URGENT - DMax for Number assignment

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Erin - 19 May 2008 22:24 GMT

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?

Thanks!

Reply to this Message

Graham R Seach - 19 May 2008 23:13 GMT

Erin,

I understand how you want the sequential numbering to work. That's a pretty
standard way of doing things, and your code looks about right, but I wonder
if the error might be caused by Me.ChildType being Null (or text, instead of
a number). You give no indication of exactly *where* the error is thrown.

I don't understand why you make the distinction between IQ and OQ documents,
because making that distinction leads me to think that maybe there is a
difference in their number formats. Also, I don't understand why there are
two AfterUpdate procedures. Can you elaborate on exactly what you're trying
to do?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

>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
[quoted text clipped - 28 lines]
>
> Thanks!

Reply to this Message

Margaret Bartley - 20 May 2008 01:56 GMT

What does error number 2001 say?
When you get the errror, have you gone into the command window to see what
the actual Criteria string says?
What happens when you use that exact criteria string to manually get the
DMax value?

Is it the same problem with both procs?

>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
[quoted text clipped - 28 lines]
>
> Thanks!

Reply to this Message