Generate next primary key value

  • Thread starter Thread starter peterDavey
  • Start date Start date
P

peterDavey

G'day all,
I'm pretty new to Access (but have a fair amount of Excel VBA experience) so
I suspect this will be the first of many posts.

I'm creating an Access 2000 front end for a SQL Server database using linked
tables. Most of my forms will be used to maintain reference tables. The
problem I'm having is that when I create a new record (e.g. to add a new
department in the department table), I need to run a query to find the
maximum current value in the primary key column so I can use the next value
for the ID of the new record. I can't use the Access AutoNumber data type
because it isn't supported by SQL Server.

I know the SQL I need to run and I assume that it will need to be run by the
Form_BeforeInsert event/sub but I'm just not sure how I run the SQL to get
the value from within this sub.

I'll be very grateful for any assistance.

cheers
peterDavey
Melbourne
 
In VBA

Where KeyID is the name of a text box or other control bound to the primary
key of the table:

KeyID = DMax("[KeyID]","[TableName]") +1

(Assuming you want to increment by 1).

As you mentioned, be careful where you place this statement. What if one
user opens the form while another user is creating a new record but has not
yet saved it? Whoever saves first will get to keep the ID. The BeforeInsert
event fires when the user types the first character, not when the record is
saved.

If the ID is purely a key and is invisible to the user, you can just create
it in Form_BeforeUpdate for new records.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Form.NewRecord then KeyID = DMax("[KeyID]","[TableName]") +1
End Sub

If the user needs to see the key, then put a MsgBox in Form_AfterInsert that
shows the number to the user after the save.
 
You could also look at SQLServer identity columns.

I think you would have to ask further questions to
resolve lots of interesting gotchas.

Regards John
 
Thanks John but I'm not too keen on the SQL server identity columns.
cheers
peterD
 
Thanks Brian, this looks like just the ticket. The multi-user issues won't
be a problem because the forms will used by only a couple of people.
cheers
peterD


Brian said:
In VBA

Where KeyID is the name of a text box or other control bound to the primary
key of the table:

KeyID = DMax("[KeyID]","[TableName]") +1

(Assuming you want to increment by 1).

As you mentioned, be careful where you place this statement. What if one
user opens the form while another user is creating a new record but has not
yet saved it? Whoever saves first will get to keep the ID. The BeforeInsert
event fires when the user types the first character, not when the record is
saved.

If the ID is purely a key and is invisible to the user, you can just create
it in Form_BeforeUpdate for new records.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Form.NewRecord then KeyID = DMax("[KeyID]","[TableName]") +1
End Sub

If the user needs to see the key, then put a MsgBox in Form_AfterInsert that
shows the number to the user after the save.

peterDavey said:
G'day all,
I'm pretty new to Access (but have a fair amount of Excel VBA experience) so
I suspect this will be the first of many posts.

I'm creating an Access 2000 front end for a SQL Server database using linked
tables. Most of my forms will be used to maintain reference tables. The
problem I'm having is that when I create a new record (e.g. to add a new
department in the department table), I need to run a query to find the
maximum current value in the primary key column so I can use the next value
for the ID of the new record. I can't use the Access AutoNumber data type
because it isn't supported by SQL Server.

I know the SQL I need to run and I assume that it will need to be run by the
Form_BeforeInsert event/sub but I'm just not sure how I run the SQL to get
the value from within this sub.

I'll be very grateful for any assistance.

cheers
peterDavey
Melbourne
Click to expand...
Click to expand...
 
Back
Top