Automatically adding a primary key

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
 
L

Linq Adams via AccessMonster.com

Here's a typical Auto-incrementing Number hack. Exact syntax varies depending
on the Datatype of the H#.

The first code would be for an IDNumber that is defined in the table as Text

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
If RecordsetClone.RecordCount = 0 Then
Me.IDNumber = "1"
Else
Me.IDNumber = DMax("val([IDNumber])", "YourTableName") + 1
End If
End If
End Sub

Here's the same code for an IDNumber defined as Numerical:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
If RecordsetClone.RecordCount = 0 Then
Me.IDNumber = 1
Else
Me.IDNumber = DMax("[IDNum]", "YourTableName") + 1
End If
End If
End Sub

Replace IDNumber with the name of the textbox on the form holding your H#

Replace IDNum with the name of the field in your table that holds the H#

Replace YourTableName with the actual name of your table that holds this
info
 
R

rye1982

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
 
R

rye1982

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...
 
A

Al Campagna

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...
 
Top