Creating case number in a form.

[Guest]

I want to create a case number each time a new record is added via a form I
already have. The form will be usable by numerous people at the same time to
enter information into the underlying tables. It has two sub forms on it
that the user can also enter information in. One of these subforms can have
multiple case numbers of the same value.

Form1=[CaseFiles] (holds all kinds of data from SS# to pasted reports,
sorted by casenumber)
SubForm1=[Balance] (Holds the balances of the cases in [CaseFiles])
SubForm2=[Payments] (Holds payments made on the cases in [CaseFiles] and
scrollable within the form)
Table1=[CASE] (Stores all data entered from [CaseFiles])
Table2=[CASEBAL] (Stores all balances along with casenumber entered in
[Balance])
Table3=[PAID] (Stores all payments entered into [Payments])

Ok hope that wasnt too confusing.

I found the format I would like the case numbers to be, but I just cant get
the form to create it when I start a new record or to propogate it to the
subforms. Here is the format.

If Me.NewRecord Then
Dim strWhere As String
Dim varResult As Variant

strWhere = "ID_Number Like """ & Format(Date, "yy") & "*"""
varResult = DMax("ID_Number", "File", strWhere)

If IsNull(varResult) Then
Me.ID_Number = Format(Date, "yy") & "-001"
Else
Me.ID_Number = Left(varResult, 3) & _
Format(Val(Right(varResult, 4)) + 1, "000")
End If
End If

I tried putting it into BeforeUpdate, Dirty fields but something isn't tying
in correctly for me. Please any assistance would be helpful. Currently I
have the users entering the case number manually, but they strongly want it
to be automatic.

 

[Marshall Barton]

I want to create a case number each time a new record is added via a form I
already have. The form will be usable by numerous people at the same time to
enter information into the underlying tables. It has two sub forms on it
that the user can also enter information in. One of these subforms can have
multiple case numbers of the same value.

Form1=[CaseFiles] (holds all kinds of data from SS# to pasted reports,
sorted by casenumber)
SubForm1=[Balance] (Holds the balances of the cases in [CaseFiles])
SubForm2=[Payments] (Holds payments made on the cases in [CaseFiles] and
scrollable within the form)
Table1=[CASE] (Stores all data entered from [CaseFiles])
Table2=[CASEBAL] (Stores all balances along with casenumber entered in
[Balance])
Table3=[PAID] (Stores all payments entered into [Payments])

Ok hope that wasnt too confusing.

I found the format I would like the case numbers to be, but I just cant get
the form to create it when I start a new record or to propogate it to the
subforms. Here is the format.

If Me.NewRecord Then
Dim strWhere As String
Dim varResult As Variant

strWhere = "ID_Number Like """ & Format(Date, "yy") & "*"""
varResult = DMax("ID_Number", "File", strWhere)

If IsNull(varResult) Then
Me.ID_Number = Format(Date, "yy") & "-001"
Else
Me.ID_Number = Left(varResult, 3) & _
Format(Val(Right(varResult, 4)) + 1, "000")
End If
End If

I tried putting it into BeforeUpdate, Dirty fields but something isn't tying
in correctly for me.

The Right function is picking up one too many characters,

The form's BeforeUpdate event minimizes the chances of a
collision.