Multiple dependent variables

[Guest]

I have a form based on table 'Evidence' with fields 'ReportNo', 'GroupNo',
'Year', and 'ItemNo'. All items are entered based on the 'ReportNo'.
Occasionally, two or more ReportNo's are related cases and are given the same
'GroupNo". The 'GroupNo' starts with "0001" on Jan. 1 and increases
sequentially throughout the year. The 'year' is the year that the report
number is assigned. The 'ItemNo' is simply a sequential number based on the
'ReportNo'. This database originated on DBaseIII in 1987 and is now being
used in Access 2003 so it's kind of late to change the design.

The first entry on the form is the 'ReportNo'. I then need Access to
determine if there is already a 'GroupNo' assigned and automatically enter it
if it is. If there is no 'GroupNo' assigned, I need Access to determine the
next sequential 'GroupNo' and enter that. To make matters more difficult,
'Year' is a separate field which also needs to be entered. For example, if I
enter a new ReportNo "05-34535" then Access should assign the next unused
GroupNo "2384", the Year "05", and the ItemNo "1". In addition, when another
item is entered for ReportNo "05-34535" then Access should assign GroupNo
"2384", Year "05", and ItemNo "2". If I then enter a new ReportNo
"05-34665", but need to assign it to GroupNo "2384", Year "05", then Access
would allow me to enter that and the ItemNo would be "1".

I have tried for three weeks using references from the discussion threads
but I can't find the solution. Thank you.

 

[Albert D.Kallal]

This is un-tested code, but it close, and should give you the "logic" you
need

The code would be placed in the after update event of the reportNo text box
on the form.


Dim strSql As String
Dim rstRecs As DAO.Recordset

' we first pull out the "year" from the text
Me.Year = Split(Me.ReportNo, "-")(0)

' now Look for existing report group number...
' it is also assumed we also have a primay key
' field of autonumber called "ID" in this code

strSql = "select * from Evidence where" & _
" ReportNo = '" & Me.ReportNo & "'" & _
" and Year = " & Me.Year

If IsNull(Me.ID) = False Then
' we must look for a report number, but due to editing
' this record, we DO NOT want to include the current
' record in our search!!
strSql = strSql & " and id <> " & Me.ID
End If

strSql = strSql & " order by ItemNo DESC"

Set rstRecs = CurrentDb.OpenRecordset(strSql)

If rstRecs.RecordCount > 0 Then
' report exist...set group number
Me.GroupNo = rstRecs!GroupNo
' increment Item number
Me.ItemNo = rstRecs!ItemNo + 1
Else
' report does NOT exist, so find max group number
' for this year, and + 1
strSql = "Year = " & Me.Year
If IsNull(Me.ID) = False Then
strSql = strSql & " and id <> " & Me.ID
End If

Me.GroupNo = Nz(DMax("GroupNo", "Evidence", strSql), 0) + 1
Me.ItemNo = 1 ' new GroupNo...start a 1

' if we are in a multi user environment, then we better write our
' current record to disk, since other users will get the same max
' value until the record is saved. So, lets save the current record
' right now, so another user will not get the same number.

Me.Refresh


End If
rstRecs.Close
Set rstRecs = Nothing

 

[Albert D.Kallal]

A few more things:

You should probably use a field name different then "year", as that is a
reserved word.

I got some old ms-access applications with fields called "date", and "year",
and they work just fine, but as a rule you do want to avoid those names for
fields.

 

[Guest]

I bow down to your magnificent powers....it works just the way I needed it
to! I honestly cannot thank you enough for your help on this - and all of
the help I have received in the past from this resource. Thank you very much
Mr. Kallal.