Using DMax for number assignment based on 2 field values

[ecsalbil via AccessMonster.com]

Note: This is also posted on the microsoft site but for some reason I cannot
post to that site right now and I need the answer... ummmm... yesterday!

I need to assign a 'child ID' number to a record based on two pieces of
information. I have used a variation of the code below to assign a number
but now I'm having problems with it (giving me run time 2001 error). The
number needs to be assigned sequentially based on the document type (child
type) and the parent number. For example, the first IQ document created for
parent FQ08-001 would get the number 1 while the first OQ documnt created for
parent FQ08-001 would also get the number 1 but the second for each type
would get a number two and so on.

My code is as follows:

Private Sub ParentID_AfterUpdate()
If Not IsNull(Me.ChildType) Then
strCriteria = "[ParentID] = """ & Me.ParentID & """" & _
" And [ChildType] = """ & Me.ChildType
Me.ChildID = Nz(DMax("[ChildID]", "tbl_Child", strCriteria), 0) + 1
End If
End Sub

Private Sub ChildType_AfterUpdate()
If Not IsNull(Me.ParentID) Then
strCriteria = "[ParentID] = """ & Me.ParentID & """" & _
" And [ChildType] = """ & Me.ChildType
Me.ChildID = Nz(DMax("[ChildID]", "tbl_Child", strCriteria), 0) + 1
End If
End Sub

What is the problem here? I'm getting the error 3075 but only after updating
the child type (no error after updating the parent ID): and it's highlighting
the Me.ChildID = Nz line. Error message is: syntax error in string in query
expression - '[ParentID] = "[FQ08-001]" And Child Type = "[DS]'

Thanks!

 

[Douglas J. Steele]

Your criteria puts a double quote in front of the value for ChildType, but
not after it. Assuing that both ParentID and ChildType are text fields, try:

strCriteria = "[ParentID] = """ & Me.ParentID & """" & _
" And [ChildType] = """ & Me.ChildType & """"

Of course, if either Me.ParentID or Me.ChildType is Null, you're going to
have issues...