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.

"peterDavey" wrote:

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

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

"peterDavey" <(E-Mail Removed)> wrote in message
news:eE8ogR$(E-Mail Removed)...
> 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
>
>

Thanks John but I'm not too keen on the SQL server identity columns.
cheers
peterD

"John Griffiths" <(E-Mail Removed)> wrote in message
news:dd8cm2$ulb$(E-Mail Removed)...
> 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
>
> "peterDavey" <(E-Mail Removed)> wrote in message
> news:eE8ogR$(E-Mail Removed)...
> > 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
> >
> >
>
>

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" <(E-Mail Removed)> wrote in message
news:10835F4F-7981-4B2F-B767-(E-Mail Removed)...
> 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" wrote:
>
> > 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
> >
> >
> >