incrementing record number

T

Todd

I've got a dbase form and then a subform where I have an incrementing record
number that increments each record entered and then starts back over at 1
when a new year starts. Basically, when you enter a submission date for a
record it will save the record with the next higher number. Below is the
code I entered in the subforms BeforeUpdate event:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me!AEID) = True Then
Me!AEID = Nz(DMax("AEID", "tblequipmentdatatable", "Year(submissiondate)
=
Year(Forms!selectmanufacturerform!equipmentdataentryform.Form!submissiondate)"), 0) + 1
End If

This code works fine. In the same dbase I'm wanting to add a separate table
with different forms from above and have it do the same thing. I changed the
applicable field, table, and form names to apply to the new stuff but when I
add a record the incrementing number field always shows the number 1 and
doesn't go to 2, 3, 4, etc when new records are entered.

Any idea what I could be doing wrong?
 
S

Steve Sanford

Todd,

Have you set a breakpoint on the "Form_BeforeUpdate" event? Did the code
run? Can you single step thru the code?(Sometimes the code gets disconnected
from the form)

Does the control have the same name as the field in the table? You should
always rename the control - keeps you and Access from getting confused on
whetherthe name is for the field or the control.

If the event is firing, please post the names of:

the new table
the name of the date fields in the new table
the new form
the new subform
the control on the new subform

and the code in the "Form_BeforeUpdate" event.


HTH
 
T

Todd

Steve,

Here is the info you asked about:
New Table: egmidtbl
Date field in new table: rcvddate (Datatype is date/time-short date)
New Form (parent form): egmcompanyformtest
New Form (subform): egmsubmissionid
Control on the new subform: egmid (this is the field in the subform and new
table that I reference as the incrementing number field)

The linking fields for the parent to subform is numCompanyNumber (for both
parent and subform) and the relationship is one to many.

The actual form field where the incrementing number and project number get
created is in a text field (called customcounternumber) I added to the form.
I do have the egmid (incrementing number) field on the form (visible) just to
see until I get it working correctly.

My actual project number (in the customcounternumber field I added to the
subform), including the incrementing number control source expression looks
like this:

=([numCompanyNumber]) & "-" & Format([rcvddate],"mm") & "-" &
Format([rcvddate],"yy") & "-" & Format([egmid],"0000")

The end result of the project number should look like this:

EX: 20-01-08-0001

20-numCompanyNumber 01-month submitted 08-year submitted 0000-incrementing
number, which should reset back to 0 when a new year is started.

The code I entered in the BeforeUpdate event of the subform is as follows:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me!egmid) = True Then
Me!egmid = Nz(DMax("egmid", "egmidtbl", "Year(rcvddate) =
Year(Forms!egmcompanyformtest!egmsubmissionid.Form!egmid)"), 0) + 1
End If
End Sub

Everything works fine except the incrementing number. The only number that
appears is 1 each time I add a record. I'm assuming that when a new year is
entered in the rcvddate (date field) that it will start over at 0. I can
find out once the incrementing number starts working right.
 
S

Steve Sanford

Hi Todd,

I can't tell which names are fields nad which are controls, but shouldn't
the DMax() function criteria be looking at [rcvddate] instead of [egmid]?

You have:

Me!egmid = Nz(DMax("egmid", "egmidtbl", "Year(rcvddate) =
Year(Forms!egmcompanyformtest!egmsubmissionid.Form!egmid)"), 0) + 1

On a new record, Me!egmid is null. You lookup max [egmid] where the year of
[rcvddate] (lets say it is 2008) is equal to NULL, which results in NULL. The
NZ() function converts the NULL to zero then ! is added.

Since Me!egmid will always be NULL on a new record, 1 will always be returned.


Shouldn't it be:

Me!egmid = Nz(DMax("egmid", "egmidtbl", "Year(rcvddate) =
Year(Forms!egmcompanyformtest!egmsubmissionid.Form!rcvddate)"), 0) + 1

^^^^^

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)
--------------------------------

WE IS FRIENDS!
~~~~~~~~~~
Me and You is friends
You smile, I smile...
You hurt, I hurt...
You cry, I cry...
You jump off a Bridge,
I gonna miss your e-mails.



Todd said:
Steve,

Here is the info you asked about:
New Table: egmidtbl
Date field in new table: rcvddate (Datatype is date/time-short date)
New Form (parent form): egmcompanyformtest
New Form (subform): egmsubmissionid
Control on the new subform: egmid (this is the field in the subform and new
table that I reference as the incrementing number field)

The linking fields for the parent to subform is numCompanyNumber (for both
parent and subform) and the relationship is one to many.

The actual form field where the incrementing number and project number get
created is in a text field (called customcounternumber) I added to the form.
I do have the egmid (incrementing number) field on the form (visible) just to
see until I get it working correctly.

My actual project number (in the customcounternumber field I added to the
subform), including the incrementing number control source expression looks
like this:

=([numCompanyNumber]) & "-" & Format([rcvddate],"mm") & "-" &
Format([rcvddate],"yy") & "-" & Format([egmid],"0000")

The end result of the project number should look like this:

EX: 20-01-08-0001

20-numCompanyNumber 01-month submitted 08-year submitted 0000-incrementing
number, which should reset back to 0 when a new year is started.

The code I entered in the BeforeUpdate event of the subform is as follows:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me!egmid) = True Then
Me!egmid = Nz(DMax("egmid", "egmidtbl", "Year(rcvddate) =
Year(Forms!egmcompanyformtest!egmsubmissionid.Form!egmid)"), 0) + 1
End If
End Sub

Everything works fine except the incrementing number. The only number that
appears is 1 each time I add a record. I'm assuming that when a new year is
entered in the rcvddate (date field) that it will start over at 0. I can
find out once the incrementing number starts working right.

--
Todd


Steve Sanford said:
Todd,

Have you set a breakpoint on the "Form_BeforeUpdate" event? Did the code
run? Can you single step thru the code?(Sometimes the code gets disconnected
from the form)

Does the control have the same name as the field in the table? You should
always rename the control - keeps you and Access from getting confused on
whetherthe name is for the field or the control.

If the event is firing, please post the names of:

the new table
the name of the date fields in the new table
the new form
the new subform
the control on the new subform

and the code in the "Form_BeforeUpdate" event.


HTH
Click to expand...
Click to expand...
 
T

Todd

Steve,

That was it. I had the wrong field referenced. I don't know what I was
thinking. I just mistakenly inserted the wrong field. I guess I stared at
it too long and missed it somehow. Thanks for finding the error and letting
me know. I appreciate your help!
--
Todd


Steve Sanford said:
Hi Todd,

I can't tell which names are fields nad which are controls, but shouldn't
the DMax() function criteria be looking at [rcvddate] instead of [egmid]?

You have:

Me!egmid = Nz(DMax("egmid", "egmidtbl", "Year(rcvddate) =
Year(Forms!egmcompanyformtest!egmsubmissionid.Form!egmid)"), 0) + 1

On a new record, Me!egmid is null. You lookup max [egmid] where the year of
[rcvddate] (lets say it is 2008) is equal to NULL, which results in NULL. The
NZ() function converts the NULL to zero then ! is added.

Since Me!egmid will always be NULL on a new record, 1 will always be returned.


Shouldn't it be:

Me!egmid = Nz(DMax("egmid", "egmidtbl", "Year(rcvddate) =
Year(Forms!egmcompanyformtest!egmsubmissionid.Form!rcvddate)"), 0) + 1

^^^^^

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)
--------------------------------

WE IS FRIENDS!
~~~~~~~~~~
Me and You is friends
You smile, I smile...
You hurt, I hurt...
You cry, I cry...
You jump off a Bridge,
I gonna miss your e-mails.



Todd said:
Steve,

Here is the info you asked about:
New Table: egmidtbl
Date field in new table: rcvddate (Datatype is date/time-short date)
New Form (parent form): egmcompanyformtest
New Form (subform): egmsubmissionid
Control on the new subform: egmid (this is the field in the subform and new
table that I reference as the incrementing number field)

The linking fields for the parent to subform is numCompanyNumber (for both
parent and subform) and the relationship is one to many.

The actual form field where the incrementing number and project number get
created is in a text field (called customcounternumber) I added to the form.
I do have the egmid (incrementing number) field on the form (visible) just to
see until I get it working correctly.

My actual project number (in the customcounternumber field I added to the
subform), including the incrementing number control source expression looks
like this:

=([numCompanyNumber]) & "-" & Format([rcvddate],"mm") & "-" &
Format([rcvddate],"yy") & "-" & Format([egmid],"0000")

The end result of the project number should look like this:

EX: 20-01-08-0001

20-numCompanyNumber 01-month submitted 08-year submitted 0000-incrementing
number, which should reset back to 0 when a new year is started.

The code I entered in the BeforeUpdate event of the subform is as follows:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me!egmid) = True Then
Me!egmid = Nz(DMax("egmid", "egmidtbl", "Year(rcvddate) =
Year(Forms!egmcompanyformtest!egmsubmissionid.Form!egmid)"), 0) + 1
End If
End Sub

Everything works fine except the incrementing number. The only number that
appears is 1 each time I add a record. I'm assuming that when a new year is
entered in the rcvddate (date field) that it will start over at 0. I can
find out once the incrementing number starts working right.

--
Todd


Steve Sanford said:
Todd,

Have you set a breakpoint on the "Form_BeforeUpdate" event? Did the code
run? Can you single step thru the code?(Sometimes the code gets disconnected
from the form)

Does the control have the same name as the field in the table? You should
always rename the control - keeps you and Access from getting confused on
whetherthe name is for the field or the control.

If the event is firing, please post the names of:

the new table
the name of the date fields in the new table
the new form
the new subform
the control on the new subform

and the code in the "Form_BeforeUpdate" event.


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


:

I've got a dbase form and then a subform where I have an incrementing record
number that increments each record entered and then starts back over at 1
when a new year starts. Basically, when you enter a submission date for a
record it will save the record with the next higher number. Below is the
code I entered in the subforms BeforeUpdate event:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me!AEID) = True Then
Me!AEID = Nz(DMax("AEID", "tblequipmentdatatable", "Year(submissiondate)
=
Year(Forms!selectmanufacturerform!equipmentdataentryform.Form!submissiondate)"), 0) + 1
End If

This code works fine. In the same dbase I'm wanting to add a separate table
with different forms from above and have it do the same thing. I changed the
applicable field, table, and form names to apply to the new stuff but when I
add a record the incrementing number field always shows the number 1 and
doesn't go to 2, 3, 4, etc when new records are entered.

Any idea what I could be doing wrong?
Click to expand...
Click to expand...
Click to expand...
 
S

Steve Sanford

You're welcome.

--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Todd said:
Steve,

That was it. I had the wrong field referenced. I don't know what I was
thinking. I just mistakenly inserted the wrong field. I guess I stared at
it too long and missed it somehow. Thanks for finding the error and letting
me know. I appreciate your help!
--
Todd


Steve Sanford said:
Hi Todd,

I can't tell which names are fields nad which are controls, but shouldn't
the DMax() function criteria be looking at [rcvddate] instead of [egmid]?

You have:

Me!egmid = Nz(DMax("egmid", "egmidtbl", "Year(rcvddate) =
Year(Forms!egmcompanyformtest!egmsubmissionid.Form!egmid)"), 0) + 1

On a new record, Me!egmid is null. You lookup max [egmid] where the year of
[rcvddate] (lets say it is 2008) is equal to NULL, which results in NULL. The
NZ() function converts the NULL to zero then ! is added.

Since Me!egmid will always be NULL on a new record, 1 will always be returned.


Shouldn't it be:

Me!egmid = Nz(DMax("egmid", "egmidtbl", "Year(rcvddate) =
Year(Forms!egmcompanyformtest!egmsubmissionid.Form!rcvddate)"), 0) + 1

^^^^^

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)
--------------------------------

WE IS FRIENDS!
~~~~~~~~~~
Me and You is friends
You smile, I smile...
You hurt, I hurt...
You cry, I cry...
You jump off a Bridge,
I gonna miss your e-mails.



Todd said:
Steve,

Here is the info you asked about:
New Table: egmidtbl
Date field in new table: rcvddate (Datatype is date/time-short date)
New Form (parent form): egmcompanyformtest
New Form (subform): egmsubmissionid
Control on the new subform: egmid (this is the field in the subform and new
table that I reference as the incrementing number field)

The linking fields for the parent to subform is numCompanyNumber (for both
parent and subform) and the relationship is one to many.

The actual form field where the incrementing number and project number get
created is in a text field (called customcounternumber) I added to the form.
I do have the egmid (incrementing number) field on the form (visible) just to
see until I get it working correctly.

My actual project number (in the customcounternumber field I added to the
subform), including the incrementing number control source expression looks
like this:

=([numCompanyNumber]) & "-" & Format([rcvddate],"mm") & "-" &
Format([rcvddate],"yy") & "-" & Format([egmid],"0000")

The end result of the project number should look like this:

EX: 20-01-08-0001

20-numCompanyNumber 01-month submitted 08-year submitted 0000-incrementing
number, which should reset back to 0 when a new year is started.

The code I entered in the BeforeUpdate event of the subform is as follows:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me!egmid) = True Then
Me!egmid = Nz(DMax("egmid", "egmidtbl", "Year(rcvddate) =
Year(Forms!egmcompanyformtest!egmsubmissionid.Form!egmid)"), 0) + 1
End If
End Sub

Everything works fine except the incrementing number. The only number that
appears is 1 each time I add a record. I'm assuming that when a new year is
entered in the rcvddate (date field) that it will start over at 0. I can
find out once the incrementing number starts working right.

--
Todd


:

Todd,

Have you set a breakpoint on the "Form_BeforeUpdate" event? Did the code
run? Can you single step thru the code?(Sometimes the code gets disconnected
from the form)

Does the control have the same name as the field in the table? You should
always rename the control - keeps you and Access from getting confused on
whetherthe name is for the field or the control.

If the event is firing, please post the names of:

the new table
the name of the date fields in the new table
the new form
the new subform
the control on the new subform

and the code in the "Form_BeforeUpdate" event.


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


:

I've got a dbase form and then a subform where I have an incrementing record
number that increments each record entered and then starts back over at 1
when a new year starts. Basically, when you enter a submission date for a
record it will save the record with the next higher number. Below is the
code I entered in the subforms BeforeUpdate event:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me!AEID) = True Then
Me!AEID = Nz(DMax("AEID", "tblequipmentdatatable", "Year(submissiondate)
=
Year(Forms!selectmanufacturerform!equipmentdataentryform.Form!submissiondate)"), 0) + 1
End If

This code works fine. In the same dbase I'm wanting to add a separate table
with different forms from above and have it do the same thing. I changed the
applicable field, table, and form names to apply to the new stuff but when I
add a record the incrementing number field always shows the number 1 and
doesn't go to 2, 3, 4, etc when new records are entered.

Any idea what I could be doing wrong?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Autonumber field in Excel that behaves the same as Access 0
Copy previous record into form field 0
expression in query 2
Require help on slight twist to auto-incrementing a number 7
Expressions in a query 2
DMax on existing record to increment serialized # fails 1
Add new record command button not working in tabbed form 1
Filling continuous form with VBA recordset 4

Top