Combined ID problem - probably involving DMAX

[Guest]

I have a form which creates a project ID (can be any user specified string)
and then opens a subform to add sub-projects, which are strictly contiguous
within each project. How can I pre-populate the sub project field in the sub
form with the next sequential number for that Project based on the Project ID
of the currently open form? (All projects can have a sub project 1, the
uniqueness is enforced by combining Sub Project ID with Project ID). I'm
guessing it involves using DMAX but I can't figure out how to apply the
current Project ID as a criteria.
Hope my garbled explanation makes sense, any help gratefully received

 

[Allen Browne]

If you have only one user adding records, you could use the BeforeInsert
event of the form to look up the highest number so far for that project, and
add 1.

Something like this:

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strWhere As String
If IsNull(Me.Parent![ProjectID]) Then
Cancel = True
MsgBox "Enter the project in the main form first."
ElseIf IsNull(Me.[SubProjectID])
strWhere = "[ProjectID] = """ & Me.Parent![ProjectID] & """"
Me.[SubProjectID] = Nz(DMax("[SubProjectID]", "[SubTable]",
strWhere),0) + 1
End If
End Sub

Adjust the names to suit your actual field names.

If the ProjectID in the main form is a Number field (not a Text field), drop
the extra quotes, i.e.:
strWhere = "[ProjectID] = " & Me.Parent![ProjectID]

If you have multiple users, you might prefer to use the BeforeUpdate event
of the form, since that fires at the last possible moment.

 

[Graham R Seach]

This sort of thing should do.

SubProjectID = Nz(DMax("SubProjectID","tblSubProject","ProjectID = " &
Me.Parent.Form!txtProjectID), 0) + 1

Regards,
Graham R Seach
Microsoft Access MVP
Canberra, Australia