Access 2000 connected to a SQL Server 2000 Database

[Francesca Sullivan]

Hi,

I have an Access 2000 application which uses tables on a SQL Server 2000
database. I am having a problem, where the main form is not automatically
generating a primary key when I enter a new record. I need it to do it
automatically as controls on associated subforms deoend on this happening.

Does anyone know why this is happening, and what I should do to rectify it?
And if so, could you point me to an article that shows me how to fix this.

Thanks in advance.

F-

 

[Brendan Reynolds]

If the primary key is a SQL Server Identity field, what you're seeing is a
difference in behaviour between a SQL Server Identity field and the JET
AutoNumber field with which you may be more familiar. With a JET AutoNumber
field, the value is assigned as soon as the record is 'dirtied', i.e. as
soon as the user begins to enter data into any of the bound controls. With a
SQL Server Identity field, the value is not assigned until the record is
saved. There is no way to change this behaviour - you'll need to ensure that
all required fields have data, then save the record, before the value of the
Identity field is available.

I'm assuming that the 'subforms' to which you refer are not actually
embedded subform controls in a main form, as the main form record is updated
automatically when focus moves to a subform control. Presumably, they're
associated forms that you're opening from command buttons on the main form?
If so, you need to add code to the Click event procedure of those command
buttons, to check that all required fields have values and then save the
main form record before opening the associated form.

 

[Francesca Sullivan]

the subforms are embedded onto the main form. but thx for the info.

f-

 

[Bruce Loving]

I solved this problem by adding a command button
that calls a stored procedure to insert a new
record and return the @@identity value.
Then I point the main form recordsource to the
newly created record.