Automatically adding a primary key

  • Thread starter Thread starter rye1982
  • Start date Start date
R

rye1982

Hello and thank you in advance for your help.

I am looking to add records to a table with patient information via a form.
I've created an Add Record button, and what I want is when it is clicked, for
it to automatically generate the next primary key in the table sequence.

We are using what we call an 'H#' to uniquely identify patient invoices. The
latest H# is 9679. So when we add a record, I want the form to automatically
generate 9680 in the appropriate form box. Then we can manually enter first
name, last name, etc.

How do I do this?

Ryan
 
Thanks Linq.

I'm a bit of an Access novice though. Could you provide me with a
step-by-step process of where to enter this code into Access?

FYI - my IDnumber is defined as a number in my table.

Ryan
 
Hi Al,

I tried this and when I click my button to add a record the H# box reads
"#Error".

Is there something I've missed? My H# is defined as a number in my table.

Thanks,

Ryan

Al Campagna said:
rye,
Set the Default Value for your ID field to...
=NZ(DMax("[YourID]","tblYourTable"),0) + 1
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

rye1982 said:
Hello and thank you in advance for your help.

I am looking to add records to a table with patient information via a
form.
I've created an Add Record button, and what I want is when it is clicked,
for
it to automatically generate the next primary key in the table sequence.

We are using what we call an 'H#' to uniquely identify patient invoices.
The
latest H# is 9679. So when we add a record, I want the form to
automatically
generate 9680 in the appropriate form box. Then we can manually enter
first
name, last name, etc.

How do I do this?

Ryan
Click to expand...
Click to expand...
 
rye,
Cut and paste the code you used in your post.
Where did you put the expression?

I tested my code (using my object names... you use your own)

Rename your H# field to something like InvoiceNo.... The "#" is a
special character used in date operations, and should be avoided in object
naming convention.
In form design mode, select View/Properties. Highlight the field
(InvoiceNo) you want to increment.
In the properties dialog box, select the ALL tab, find the Default Value
property, and in the text box to the right enter this...

=NZ(DMax("[CustID]","tblCustomers"),0) + 1
(Use the name of your field and your table name)

Whenever a new record is created, this will generate the next higher
number in the table.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."


rye1982 said:
Hi Al,

I tried this and when I click my button to add a record the H# box reads
"#Error".

Is there something I've missed? My H# is defined as a number in my table.

Thanks,

Ryan

Al Campagna said:
rye,
Set the Default Value for your ID field to...
=NZ(DMax("[YourID]","tblYourTable"),0) + 1
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."

rye1982 said:
Hello and thank you in advance for your help.

I am looking to add records to a table with patient information via a
form.
I've created an Add Record button, and what I want is when it is
clicked,
for
it to automatically generate the next primary key in the table
sequence.

We are using what we call an 'H#' to uniquely identify patient
invoices.
The
latest H# is 9679. So when we add a record, I want the form to
automatically
generate 9680 in the appropriate form box. Then we can manually enter
first
name, last name, etc.

How do I do this?

Ryan
Click to expand...
Click to expand...
Click to expand...
 
Back
Top