Assigning a Unique, Sequential Number

[ecsalbil]

I have a form in which users will enter the document type (selected from a
combo box) and the initiation date (only the year is necessary but right now
the field is formatted as a general date). Based off of these two things I
need to assign a sequential number that for a record ID that begins at 1 for
each calendar year. My code right now is:

Private Sub InitiationDate_AfterUpdate()
Dim strCriteria As String
If Not IsNull(Me.ParentType) Then
strCriteria = [ParentType] = """ & Me.ParentType & """ & _
" And [InitiationDate] = #" & _
Format(Me.InitiationDate, "mm/dd/yyyy") & "#"
Me.ParentID = Nz(DMax("[ParentID]", "tbl_Parent", strCriteria), 0) + 1
End If
End Sub

Private Sub ParentType_AfterUpdate()
If Not IsNull(Me.InitiationDate) Then
strCriteria = [ParentType] = """ & Me.ParentType & """ & _
" And [InitiationDate] = #" & _
Format(Me.InitiationDate, "mm/dd/yyyy") & "#"
Me.ParentID = Nz(DMax("[ParentID]", "tbl_Parent", strCriteria), 0) + 1
End If
End Sub

Right now this is giving every record the number 1 and not increasing for the
second record of that type created for that calendar year. How do I get the
ID number assigned using the combination of the two fields.

Also, only one user will be in this database at a time to enter data (all
others would just be viewing) so duplicates aren't a concern at this time.

Thanks!

 

[George Nicholson]

Right now this is giving every record the number 1 and not increasing for

the
second record of that type created for that calendar year.

Odds are that's because your Dlookup isn't finding a match, so it always
returns Null, which NZ converts to zero, to which you are adding 1.

Look at strCriteria again.

strCriteria = [ParentType] = """ & Me.ParentType & """ & _
" And [InitiationDate] = #" & _
Format(Me.InitiationDate, "mm/dd/yyyy") & "#"

should at least be (" added before [ParentType]):

strCriteria = "[ParentType] = """ (etc.)
--
HTH,
George

I have a form in which users will enter the document type (selected from a
combo box) and the initiation date (only the year is necessary but right
now
the field is formatted as a general date). Based off of these two things
I
need to assign a sequential number that for a record ID that begins at 1
for
each calendar year. My code right now is:

Private Sub InitiationDate_AfterUpdate()
Dim strCriteria As String
If Not IsNull(Me.ParentType) Then
strCriteria = [ParentType] = """ & Me.ParentType & """ & _
" And [InitiationDate] = #" & _
Format(Me.InitiationDate, "mm/dd/yyyy") & "#"
Me.ParentID = Nz(DMax("[ParentID]", "tbl_Parent", strCriteria), 0) + 1
End If
End Sub

Private Sub ParentType_AfterUpdate()
If Not IsNull(Me.InitiationDate) Then
strCriteria = [ParentType] = """ & Me.ParentType & """ & _
" And [InitiationDate] = #" & _
Format(Me.InitiationDate, "mm/dd/yyyy") & "#"
Me.ParentID = Nz(DMax("[ParentID]", "tbl_Parent", strCriteria), 0) + 1
End If
End Sub

Right now this is giving every record the number 1 and not increasing for
the
second record of that type created for that calendar year. How do I get
the
ID number assigned using the combination of the two fields.

Also, only one user will be in this database at a time to enter data (all
others would just be viewing) so duplicates aren't a concern at this time.

Thanks!

 

[Ken Sheridan]

Haven't we answered this already in another thread?

strCriteria = "[ParentType] = """ & Me.ParentType & """ & _
" And Year(InitiationDate) = " & Year(Me.InitiationDate)

Ken Sheridan
Stafford, England

 

[ecsalbil via AccessMonster.com]

Not really - that's still not working.

Haven't we answered this already in another thread?

strCriteria = "[ParentType] = """ & Me.ParentType & """ & _
" And Year(InitiationDate) = " & Year(Me.InitiationDate)

Ken Sheridan
Stafford, England

I have a form in which users will enter the document type (selected from a
combo box) and the initiation date (only the year is necessary but right now

[quoted text clipped - 29 lines]

Thanks!

 

[Ken Sheridan]

There's a missing quotes character. Amend it like so in both procedures and
try it:

strCriteria = "[ParentType] = """ & Me.ParentType & """" & _
" And Year(InitiationDate) = " & Year(Me.InitiationDate)

Ken Sheridan
Stafford, England

Not really - that's still not working.

Haven't we answered this already in another thread?

strCriteria = "[ParentType] = """ & Me.ParentType & """ & _
" And Year(InitiationDate) = " & Year(Me.InitiationDate)

Ken Sheridan
Stafford, England

I have a form in which users will enter the document type (selected from a
combo box) and the initiation date (only the year is necessary but right now

[quoted text clipped - 29 lines]

Thanks!

 

[ecsalbil via AccessMonster.com]

Thank you Thank you Thank you!

It's working!!!

There's a missing quotes character. Amend it like so in both procedures and
try it:

strCriteria = "[ParentType] = """ & Me.ParentType & """" & _
" And Year(InitiationDate) = " & Year(Me.InitiationDate)

Ken Sheridan
Stafford, England

Not really - that's still not working.

[quoted text clipped - 11 lines]