URGENT: Unique Number based on two fields

[Erin]

I have the following code assigning a unique number based on the record type:

Private Sub Combo4_AfterUpdate()
Me.Type = Me.Combo4
Me.ID = Nz(DMax("[ID]", "Table1", "[Type] = """ & Me.Combo4 & """"), 0) + 1
End Sub

I need to add in the initiation year as another requirement so that the
numbering starts again at 1 for each year. Where do I add that - and more
importantly, how?

The field with the year information is InitiationYear.

Also, I'm going to concatenate all of these fields (type, year, ID) to
generate the document ID number and I need to know how to populate this
information to a table.

Thanks.

 

[Ken Sheridan]

Firstly both the initiation year and type values must be entered before you
can compute the serial number per year per type. So to cover all
possibilities you'll need code in the AfterUpdate event procedures of both
the InitiationYear and Type (Combo4) controls. I've assumed in what follows
that InitiationYear is a number data type.

In the InitiationYear control's event:

Dim strCriteria As String

If Not IsNull(Me.Combo4) Then
strCriteria = [Type] = """ & Me.Combo4 & """ " & _
"And InitiationYear = " & Me.InitiationYear
Me.ID = Nz(DMax("[ID]", "Table1", strCriteria),0) + 1
End If

In the Combo4 control's event:

Dim strCriteria As String

If Not IsNull(Me.InitiationYear) Then
Me.Type = Me.Combo4
strCriteria = [Type] = """ & Me.Combo4 & """" & _
" And InitiationYear = " & Me.InitiationYear
Me.ID = Nz(DMax("[ID]", "Table1", strCriteria),0) + 1
End If

A few comments:

1. If the initiation year value is being entered automatically, with
Year(Date()) for instance, when a new record is created then you only need
the code in the Combo4 control's event, not in the InitiationYear's. That
will only execute if the user edits the control, so unless this is being done
any code there would be otiose.

2. Why are you assigning a value to the Type field rather than just binding
Combo4 to it?

3. I'd recommend not using Type as an object name. It’s a keyword in VBA,
DAO and ADOX, so is best avoided as an object name. Something more explicit
such as TransactionType, or whatever suits, would be better.

4. As you are assigning the serial number before the new record is saved to
the table conflicts could arise in a multi-user environment if two or more
users are adding records with the same Type and InitiationYear values
simultaneously. Assuming you have a unique index on these three fields (this
is essential!) the first user to save their record would do so successfully,
but the other(s) would experience an error due to the index violation.

5. To concatenate the values, do so in a computed column in a query or
computed control in a form or report, but do not under any circumstances save
the concatenated value to a column in the table. That introduces redundancy
and the risk of inconsistent data.

Ken Sheridan
Stafford, England

I have the following code assigning a unique number based on the record type:

Private Sub Combo4_AfterUpdate()
Me.Type = Me.Combo4
Me.ID = Nz(DMax("[ID]", "Table1", "[Type] = """ & Me.Combo4 & """"), 0) + 1
End Sub

I need to add in the initiation year as another requirement so that the
numbering starts again at 1 for each year. Where do I add that - and more
importantly, how?

The field with the year information is InitiationYear.

Also, I'm going to concatenate all of these fields (type, year, ID) to
generate the document ID number and I need to know how to populate this
information to a table.

Thanks.

 

[Erin]

Thanks for your help! I've taken your suggestions (instead of having a combo
box I added the table field as a combo box so that's removed the combo4 =
parenttype) and changed the coding but now it's continuing to assign the
number "1" to every record regardless:

Private Sub InitiationYear_AfterUpdate()
Dim strCriteria As String
If Not IsNull(Me.ParentType) Then
strCriteria = [ParentType] = """ & Me.ParentType & """ & _
" InitiationYear = " & Me.InitiationYear
Me.ID = Nz(DMax("[ID]", "Table1", strCriteria), 0) + 1
End If
End Sub

Private Sub ParentType_AfterUpdate()
If Not IsNull(Me.InitiationYear) Then
strCriteria = [ParentType] = """ & Me.ParentType & """ & _
" InitiationYear = " & Me.InitiationYear
Me.ID = Nz(DMax("[ID]", "Table1", strCriteria), 0) + 1
End If
End Sub

My knowledge of coding is VERY limited so I'm at a loss right now.

Also, there isn't a risk of assigning the same number as this will be used
by one person for entry (the concatenated data will assign the full record
'title' number). I'm wondering if that concatenated value can be added to a
DIFFERENT table for later use?

Firstly both the initiation year and type values must be entered before you
can compute the serial number per year per type. So to cover all
possibilities you'll need code in the AfterUpdate event procedures of both
the InitiationYear and Type (Combo4) controls. I've assumed in what follows
that InitiationYear is a number data type.

In the InitiationYear control's event:

Dim strCriteria As String

If Not IsNull(Me.Combo4) Then
strCriteria = [Type] = """ & Me.Combo4 & """ " & _
"And InitiationYear = " & Me.InitiationYear
Me.ID = Nz(DMax("[ID]", "Table1", strCriteria),0) + 1
End If

In the Combo4 control's event:

Dim strCriteria As String

If Not IsNull(Me.InitiationYear) Then
Me.Type = Me.Combo4
strCriteria = [Type] = """ & Me.Combo4 & """" & _
" And InitiationYear = " & Me.InitiationYear
Me.ID = Nz(DMax("[ID]", "Table1", strCriteria),0) + 1
End If

A few comments:

1. If the initiation year value is being entered automatically, with
Year(Date()) for instance, when a new record is created then you only need
the code in the Combo4 control's event, not in the InitiationYear's. That
will only execute if the user edits the control, so unless this is being done
any code there would be otiose.

2. Why are you assigning a value to the Type field rather than just binding
Combo4 to it?

3. I'd recommend not using Type as an object name. It’s a keyword in VBA,
DAO and ADOX, so is best avoided as an object name. Something more explicit
such as TransactionType, or whatever suits, would be better.

4. As you are assigning the serial number before the new record is saved to
the table conflicts could arise in a multi-user environment if two or more
users are adding records with the same Type and InitiationYear values
simultaneously. Assuming you have a unique index on these three fields (this
is essential!) the first user to save their record would do so successfully,
but the other(s) would experience an error due to the index violation.

5. To concatenate the values, do so in a computed column in a query or
computed control in a form or report, but do not under any circumstances save
the concatenated value to a column in the table. That introduces redundancy
and the risk of inconsistent data.

Ken Sheridan
Stafford, England

I have the following code assigning a unique number based on the record type:

Private Sub Combo4_AfterUpdate()
Me.Type = Me.Combo4
Me.ID = Nz(DMax("[ID]", "Table1", "[Type] = """ & Me.Combo4 & """"), 0) + 1
End Sub

I need to add in the initiation year as another requirement so that the
numbering starts again at 1 for each year. Where do I add that - and more
importantly, how?

The field with the year information is InitiationYear.

Also, I'm going to concatenate all of these fields (type, year, ID) to
generate the document ID number and I need to know how to populate this
information to a table.

Thanks.

 

[Ken Sheridan]

Let's first be clear that I've understood correctly what you want. The way I
read your original post was that for each initiation year and each parent
type you wanted to number the rows sequentially starting with 1, so the table
might look like this:

InitiationYear ParentType ID
------------------------------------------
2007 Foo 1
2007 Bar 1
2007 Foo 2
2007 Foo 3
2007 Bar 2
2008 Foo 1
2008 Foo 2
2008 Bar 1
2008 Foo 3
2008 Bar 2

Is that what's wanted? The code is right for this, apart from the fact that
you've missed the 'And' operator out when building the string for the
criteria to look up the highest existing value. It should be:

Private Sub InitiationYear_AfterUpdate()
Dim strCriteria As String
If Not IsNull(Me.ParentType) Then
strCriteria = [ParentType] = """ & Me.ParentType & """ & _
" And InitiationYear = " & Me.InitiationYear
Me.ID = Nz(DMax("[ID]", "Table1", strCriteria), 0) + 1
End If
End Sub

Private Sub ParentType_AfterUpdate()
If Not IsNull(Me.InitiationYear) Then
strCriteria = [ParentType] = """ & Me.ParentType & """ & _
" And InitiationYear = " & Me.InitiationYear
Me.ID = Nz(DMax("[ID]", "Table1", strCriteria), 0) + 1
End If
End Sub

The way it works is like this:

If we take my dummy table above and you enter a new record with 2008 as the
initiation Year and Foo as the type the string built as the criteria would be:

ParentType = "Foo" And InitiationYear = 2008

The DMax function looks for the row in the table with the highest (Max) ID
value where these criteria are met, i.e. the parent type is Foo and the
initiation year is 2008. This value is 3, so by adding 1 to this we come up
with 4 as the value for the ID in the new row being added. The Nz function
is used because if there are not yet any rows which meet the criterion then
the DMax function will return a Null. The Nz function converts this to a
zero, so adding 1 to this gives us 1 for the ID.

Where you say "I'm wondering if that concatenated value can be added to a
DIFFERENT table for later use?" I'm not sure what you have in mind here? I
think you are probably thinking of how to relate another table to this one.
If so then, as the InitiationYear, ParentType and ID make up a composite
primary key for this table, you can repeat the same three columns in a table
related to it, so that they form a composite foreign key in the 'referencing'
table. You'd probably use a subform for data entry into this table so the
values from the 'parent' record would automatically be entered into the three
columns in the 'child' records related to it.

An alternative would be to add an autonumber column to the current table to
act as its primary key and a corresponding long integer number column in the
'referencing' table as a foreign key, but not an autonumber in that case. If
you do use an autonumber column as a 'surrogate' key instead of the composite
'natural' key of the three columns in the current table its important that
you create a unique index on the three columns in the current table (that's a
single unique index on all three, not separate indices on each) to prevent
invalid duplicate data being entered. If you do define all three columns as
the primary key then this automatically creates a unique index.

Ken Sheridan
Stafford, England

Thanks for your help! I've taken your suggestions (instead of having a combo
box I added the table field as a combo box so that's removed the combo4 =
parenttype) and changed the coding but now it's continuing to assign the
number "1" to every record regardless:

Private Sub InitiationYear_AfterUpdate()
Dim strCriteria As String
If Not IsNull(Me.ParentType) Then
strCriteria = [ParentType] = """ & Me.ParentType & """ & _
" InitiationYear = " & Me.InitiationYear
Me.ID = Nz(DMax("[ID]", "Table1", strCriteria), 0) + 1
End If
End Sub

Private Sub ParentType_AfterUpdate()
If Not IsNull(Me.InitiationYear) Then
strCriteria = [ParentType] = """ & Me.ParentType & """ & _
" InitiationYear = " & Me.InitiationYear
Me.ID = Nz(DMax("[ID]", "Table1", strCriteria), 0) + 1
End If
End Sub

My knowledge of coding is VERY limited so I'm at a loss right now.

Also, there isn't a risk of assigning the same number as this will be used
by one person for entry (the concatenated data will assign the full record
'title' number). I'm wondering if that concatenated value can be added to a
DIFFERENT table for later use?

Firstly both the initiation year and type values must be entered before you
can compute the serial number per year per type. So to cover all
possibilities you'll need code in the AfterUpdate event procedures of both
the InitiationYear and Type (Combo4) controls. I've assumed in what follows
that InitiationYear is a number data type.

In the InitiationYear control's event:

Dim strCriteria As String

If Not IsNull(Me.Combo4) Then
strCriteria = [Type] = """ & Me.Combo4 & """ " & _
"And InitiationYear = " & Me.InitiationYear
Me.ID = Nz(DMax("[ID]", "Table1", strCriteria),0) + 1
End If

In the Combo4 control's event:

Dim strCriteria As String

If Not IsNull(Me.InitiationYear) Then
Me.Type = Me.Combo4
strCriteria = [Type] = """ & Me.Combo4 & """" & _
" And InitiationYear = " & Me.InitiationYear
Me.ID = Nz(DMax("[ID]", "Table1", strCriteria),0) + 1
End If

A few comments:

1. If the initiation year value is being entered automatically, with
Year(Date()) for instance, when a new record is created then you only need
the code in the Combo4 control's event, not in the InitiationYear's. That
will only execute if the user edits the control, so unless this is being done
any code there would be otiose.

2. Why are you assigning a value to the Type field rather than just binding
Combo4 to it?

3. I'd recommend not using Type as an object name. It’s a keyword in VBA,
DAO and ADOX, so is best avoided as an object name. Something more explicit
such as TransactionType, or whatever suits, would be better.

4. As you are assigning the serial number before the new record is saved to
the table conflicts could arise in a multi-user environment if two or more
users are adding records with the same Type and InitiationYear values
simultaneously. Assuming you have a unique index on these three fields (this
is essential!) the first user to save their record would do so successfully,
but the other(s) would experience an error due to the index violation.

5. To concatenate the values, do so in a computed column in a query or
computed control in a form or report, but do not under any circumstances save
the concatenated value to a column in the table. That introduces redundancy
and the risk of inconsistent data.

Ken Sheridan
Stafford, England

I have the following code assigning a unique number based on the record type:

Private Sub Combo4_AfterUpdate()
Me.Type = Me.Combo4
Me.ID = Nz(DMax("[ID]", "Table1", "[Type] = """ & Me.Combo4 & """"), 0) + 1
End Sub

I need to add in the initiation year as another requirement so that the
numbering starts again at 1 for each year. Where do I add that - and more
importantly, how?

The field with the year information is InitiationYear.

Also, I'm going to concatenate all of these fields (type, year, ID) to
generate the document ID number and I need to know how to populate this
information to a table.

Thanks.

 

[Erin]

Foo Bar is exactly right!

I've copied that code exactly and rennamed it for the appropriate fields
(started fresh):

Private Sub InitiationDate_AfterUpdate()
Dim strCriteria As String
If Not IsNull(Me.ParentType) Then
strCriteria = [ParentType] = """ & Me.ParentType & """ & _
" And InitiationDate = " & Me.InitiationDate
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 = " & Me.InitiationDate
Me.ParentID = Nz(DMax("[ParentID]", "tbl_Parent", strCriteria), 0) + 1
End If
End Sub


It is still giving me the number 1 for every record and I cannot figure out
why.

Type Year ID
FQ 2005 1
FQ 2005 1

and so on for all document types. Does it matter that the date field is a
'short date' and not a number?

Again, thanks for your assistance (and patience).

Erin

Let's first be clear that I've understood correctly what you want. The way I
read your original post was that for each initiation year and each parent
type you wanted to number the rows sequentially starting with 1, so the table
might look like this:

InitiationYear ParentType ID
------------------------------------------
2007 Foo 1
2007 Bar 1
2007 Foo 2
2007 Foo 3
2007 Bar 2
2008 Foo 1
2008 Foo 2
2008 Bar 1
2008 Foo 3
2008 Bar 2

Is that what's wanted? The code is right for this, apart from the fact that
you've missed the 'And' operator out when building the string for the
criteria to look up the highest existing value. It should be:

Private Sub InitiationYear_AfterUpdate()
Dim strCriteria As String
If Not IsNull(Me.ParentType) Then
strCriteria = [ParentType] = """ & Me.ParentType & """ & _
" And InitiationYear = " & Me.InitiationYear
Me.ID = Nz(DMax("[ID]", "Table1", strCriteria), 0) + 1
End If
End Sub

Private Sub ParentType_AfterUpdate()
If Not IsNull(Me.InitiationYear) Then
strCriteria = [ParentType] = """ & Me.ParentType & """ & _
" And InitiationYear = " & Me.InitiationYear
Me.ID = Nz(DMax("[ID]", "Table1", strCriteria), 0) + 1
End If
End Sub

The way it works is like this:

If we take my dummy table above and you enter a new record with 2008 as the
initiation Year and Foo as the type the string built as the criteria would be:

ParentType = "Foo" And InitiationYear = 2008

The DMax function looks for the row in the table with the highest (Max) ID
value where these criteria are met, i.e. the parent type is Foo and the
initiation year is 2008. This value is 3, so by adding 1 to this we come up
with 4 as the value for the ID in the new row being added. The Nz function
is used because if there are not yet any rows which meet the criterion then
the DMax function will return a Null. The Nz function converts this to a
zero, so adding 1 to this gives us 1 for the ID.

Where you say "I'm wondering if that concatenated value can be added to a
DIFFERENT table for later use?" I'm not sure what you have in mind here? I
think you are probably thinking of how to relate another table to this one.
If so then, as the InitiationYear, ParentType and ID make up a composite
primary key for this table, you can repeat the same three columns in a table
related to it, so that they form a composite foreign key in the 'referencing'
table. You'd probably use a subform for data entry into this table so the
values from the 'parent' record would automatically be entered into the three
columns in the 'child' records related to it.

An alternative would be to add an autonumber column to the current table to
act as its primary key and a corresponding long integer number column in the
'referencing' table as a foreign key, but not an autonumber in that case. If
you do use an autonumber column as a 'surrogate' key instead of the composite
'natural' key of the three columns in the current table its important that
you create a unique index on the three columns in the current table (that's a
single unique index on all three, not separate indices on each) to prevent
invalid duplicate data being entered. If you do define all three columns as
the primary key then this automatically creates a unique index.

Ken Sheridan
Stafford, England

 

[Ken Sheridan]

Aha! Date/time fields are in fact stored as a floating point number. The
format is just how you see them, but the underlying value is always the same
whatever date/time format you use. Try first formatting the date in US short
date format (literal dates have to be in this or an otherwise internationally
unambiguous format) and then wrapping the value in # date delimiter
characters:

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

Strange things can happen if you don't do this as a date in short date
format can be interpreted as an arithmetic expression, so today's date
05/08/2008 for instance would evaluate to 0.000311254980079681. This value
actually represents a date/time of 30 December 1899 00:00:27. This is
because 30 December 1899 is day zero in Access's date/time implementation.
Now I don't imagine any rows in your table will have that date/time value, so
the DMax function will return a Null. The Nz function converts this to zero
and 1 is added; hence the 1 every time. I could be wrong, but this seems to
explain the behaviour, so amend both of the event procedures and see what
happens.

Ken Sheridan
Stafford, England

Foo Bar is exactly right!

I've copied that code exactly and rennamed it for the appropriate fields
(started fresh):

Private Sub InitiationDate_AfterUpdate()
Dim strCriteria As String
If Not IsNull(Me.ParentType) Then
strCriteria = [ParentType] = """ & Me.ParentType & """ & _
" And InitiationDate = " & Me.InitiationDate
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 = " & Me.InitiationDate
Me.ParentID = Nz(DMax("[ParentID]", "tbl_Parent", strCriteria), 0) + 1
End If
End Sub


It is still giving me the number 1 for every record and I cannot figure out
why.

Type Year ID
FQ 2005 1
FQ 2005 1

and so on for all document types. Does it matter that the date field is a
'short date' and not a number?

Again, thanks for your assistance (and patience).

Erin

Let's first be clear that I've understood correctly what you want. The way I
read your original post was that for each initiation year and each parent
type you wanted to number the rows sequentially starting with 1, so the table
might look like this:

InitiationYear ParentType ID
------------------------------------------
2007 Foo 1
2007 Bar 1
2007 Foo 2
2007 Foo 3
2007 Bar 2
2008 Foo 1
2008 Foo 2
2008 Bar 1
2008 Foo 3
2008 Bar 2

Is that what's wanted? The code is right for this, apart from the fact that
you've missed the 'And' operator out when building the string for the
criteria to look up the highest existing value. It should be:

Private Sub InitiationYear_AfterUpdate()
Dim strCriteria As String
If Not IsNull(Me.ParentType) Then
strCriteria = [ParentType] = """ & Me.ParentType & """ & _
" And InitiationYear = " & Me.InitiationYear
Me.ID = Nz(DMax("[ID]", "Table1", strCriteria), 0) + 1
End If
End Sub

Private Sub ParentType_AfterUpdate()
If Not IsNull(Me.InitiationYear) Then
strCriteria = [ParentType] = """ & Me.ParentType & """ & _
" And InitiationYear = " & Me.InitiationYear
Me.ID = Nz(DMax("[ID]", "Table1", strCriteria), 0) + 1
End If
End Sub

The way it works is like this:

If we take my dummy table above and you enter a new record with 2008 as the
initiation Year and Foo as the type the string built as the criteria would be:

ParentType = "Foo" And InitiationYear = 2008

The DMax function looks for the row in the table with the highest (Max) ID
value where these criteria are met, i.e. the parent type is Foo and the
initiation year is 2008. This value is 3, so by adding 1 to this we come up
with 4 as the value for the ID in the new row being added. The Nz function
is used because if there are not yet any rows which meet the criterion then
the DMax function will return a Null. The Nz function converts this to a
zero, so adding 1 to this gives us 1 for the ID.

Where you say "I'm wondering if that concatenated value can be added to a
DIFFERENT table for later use?" I'm not sure what you have in mind here? I
think you are probably thinking of how to relate another table to this one.
If so then, as the InitiationYear, ParentType and ID make up a composite
primary key for this table, you can repeat the same three columns in a table
related to it, so that they form a composite foreign key in the 'referencing'
table. You'd probably use a subform for data entry into this table so the
values from the 'parent' record would automatically be entered into the three
columns in the 'child' records related to it.

An alternative would be to add an autonumber column to the current table to
act as its primary key and a corresponding long integer number column in the
'referencing' table as a foreign key, but not an autonumber in that case. If
you do use an autonumber column as a 'surrogate' key instead of the composite
'natural' key of the three columns in the current table its important that
you create a unique index on the three columns in the current table (that's a
single unique index on all three, not separate indices on each) to prevent
invalid duplicate data being entered. If you do define all three columns as
the primary key then this automatically creates a unique index.

Ken Sheridan
Stafford, England

 

[Erin]

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



Still generating a 1 for everything...

Am I missing something? Is this something that should be working? Or is it
more worthwhile for me to have a separate form and table for each document
type (there are 5) and assign sequential numbers based on the year only since
the type within that table is always the same? It just seems difficult
because there are child documents that come off of each of these that would
need a subform...

Much more complicated than I think my limited knowledge goes.

 

[Ken Sheridan]

I think I may have misunderstood. Thinking about it I now see it like this:
InitiatationDate contains various dates throughout the year, but you want the
number sequence per type to start from 1 again when the year changes, right?
In this case you can use the Year function to extract the year from the date,
so it would be:

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

As I'd given it to you last time the number would only increment if there
were two or more rows of the same type and exactly the same date.

Don't think about having separate tables for each document. That's very
bad. Its encoding data as table names, whereas data should only be stored as
values at column positions in rows in tables. This is what's known as the
'information principle' and is fundamental to relational database design.
Its expressed formally as:

'The entire information content of the database is represented in one and
only one way, namely as explicit values in column positions in rows in
tables'.
C J Date – Introduction to Database Systems; 7th Edition; 2000

Ken Sheridan
Stafford, England

 

[Erin]

So I've changed the code to this:


Private Sub InitiationDate_AfterUpdate()
Dim strCriteria As String
If Not IsNull(Me.ParentType) Then
strCriteria = [ParentType] = """ & Me.ParentType & """ & _
" And Year(InitiationDate) = " & Year(Me.InitiationDate)
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 Year(InitiationDate) = " & Year(Me.InitiationDate)
Me.ParentID = Nz(DMax("[ParentID]", "tbl_Parent", strCriteria), 0) + 1
End If
End Sub

And it's still giving me 1 for every entry (even same type and same date).
Is there something missing? This is the only code for this form... Do I
need to convert the date to a number as stated previously? Or the Year
function is taking care of that?

Thanks again for your help and patience!

 

[Ken Sheridan]

There are missing quotes characters in both cases. I'm rather surprised it
doesn't raise an error at present, but amend it like so and try it:

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

Ken Sheridan
Stafford, England