Auto-Number that can be reset or changed manually

C

Cheryl

I want a field that will automatically add 1 to the same
field in the next record in a single table. I want to
set the starting number and I want to be able to override
the auto number. I just simply want the field to
automatically look back to the LAST Record entered and
add 1.
 
R

Rick Brandt

Cheryl said:
I want a field that will automatically add 1 to the same
field in the next record in a single table. I want to
set the starting number and I want to be able to override
the auto number. I just simply want the field to
automatically look back to the LAST Record entered and
add 1.

Doing _All_ data entry with a form use the following in the form's
BeforeUpdate event...

If IsNull(Me![IDField]) = True Then
Me![IDField] = Nz(DMax("IDField","TableName"),0) + 1
End If
 
C

Cheryl

-----Original Message-----
I want a field that will automatically add 1 to the same
field in the next record in a single table. I want to
set the starting number and I want to be able to override
the auto number. I just simply want the field to
automatically look back to the LAST Record entered and
add 1.

Doing _All_ data entry with a form use the following in the form's
BeforeUpdate event...

If IsNull(Me![IDField]) = True Then
Me![IDField] = Nz(DMax("IDField","TableName"),0) + 1
End If


--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com


.
Thank you for the response! I've entered this string
into the FORMS BeforeUpdate event replacing the IDField
name and TableName that I want to automatically update.
This still isn't working for me.
I get the following error message:

Microsoft Access can't find the macro 'If IsNull(Me!
[Job]) = True Then Me![Job] = Nz(DMax("Job","Job Ticket
Table"),0) + 1 End If'

The macro referred to is the actual expression I typed
into the Before Update Event. Should my IDField describe
the form name too?

Should I be doing something else. In the mean time I am
experimenting with macros.

Any help is appreciated.
 
R

Rick Brandt

Cheryl said:
-----Original Message-----
I want a field that will automatically add 1 to the same
field in the next record in a single table. I want to
set the starting number and I want to be able to override
the auto number. I just simply want the field to
automatically look back to the LAST Record entered and
add 1.

Doing _All_ data entry with a form use the following in the form's
BeforeUpdate event...

If IsNull(Me![IDField]) = True Then
Me![IDField] = Nz(DMax("IDField","TableName"),0) + 1
End If


--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com


.
Thank you for the response! I've entered this string
into the FORMS BeforeUpdate event replacing the IDField
name and TableName that I want to automatically update.
This still isn't working for me.
I get the following error message:

Microsoft Access can't find the macro 'If IsNull(Me!
[Job]) = True Then Me![Job] = Nz(DMax("Job","Job Ticket
Table"),0) + 1 End If'

The macro referred to is the actual expression I typed
into the Before Update Event. Should my IDField describe
the form name too?

Should I be doing something else. In the mean time I am
experimenting with macros.

VBA code doesn't go directly into the property sheet. You enter "[Event
Procedure]" (which should be a choice in the drop-down list) as the
property setting. Then you press the Build button to the right [...]. This
will take you to the VBA code editor window which is where you put your
code.
 

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

Top