Get Next AutoNumber -- Insert Into Table

Question

Post reply

Get Next AutoNumber -- Insert Into Table

Cleech

Mr or Mrs. 500

Points: 513
More actions
July 31, 2008 at 2:44 pm

#70976

Hello All:
I'm using Access 2002 to connect to SQL Server 2005. Without involving all of the other sordid details.....
I simply want to grab the autonumber primary key I'm using and insert it into a junction table. I cannot increment the number (tried already) b/c the PK doesn't exist yet. So I get a FK error.
Does anyone know during which event the number is assigned? I'm trying to do it at the point the user moves to the next record right now. I know I will have to account for all of the other scenarios, but I want to at least figure this one out.
Any help is greatly appreciated.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

wazz SSC Enthusiast Points: 155 More actions · Answer 1

beforeupdate.

"You met me at a very strange time in my life." - Tyler Durden

Art Sennholtz Old Hand Points: 325 More actions · Answer 2

Hi, I'm trying to do the same thing. My idea was to force the row to update using an Event Procedure in a control in the form but I haven't been able to figure out how to force the update. Did you ever get anywhere with your problem?

Thanks,

Art

John Barnett SSCrazy Points: 2086 More actions · Answer 3

Hello,

A few questions to clarify things myself:

1. Is this an access autonumber variable from an Access table, or a SQL Server identity column?

2. How is the access to SQL Server connectivity done? ODBC via linked tables, ADO via VBA, stored procs via pass through queries etc?

(ie would you be able to select @@identity through it into a variable)

A relatively easy way with linked tables is to set up a form with a subform, linking on this field.

Then as you tab from the master to the detail form, access saves the master record, you can have a hidden field on the subform with a controlsource - its something like =Parent!fieldname which can be bound to the fk field on the subform, where fieldname is the name of the control containing the ID value (it can be hidden if you don't want it user visible).

Another way is in the afterupdate event (beforeupdate runs before the data has been saved, so it won't have been issued at that time) to use a dlookup to retrieve something that is near unique based on the data (eg forename and surname of a person with an added date in the last 5 minutes, level of difficulty and complexity needs to be considered depending on the quantity of data in your system and the level of concurrent usage).

John

Art Sennholtz Old Hand Points: 325 More actions · Answer 4

First of all, thank you very much for your prompt response.

This is a SQL Server Identity Column.

The table is in a SQL Server 2005 database linked via an ODBC connection.

I wrote the application 6 years ago when I was more familiar with Access. I work exclusively with SQL Server now and have lost a lot of my more detailed Access skills. I don't want to change the forms if that is possible so to uncomplicate matters I wanted to do something similar to what you describe in your last paragraph.

I tried the dlookup but I must be doing it wrong. The dlookup didn't return any errors but the Identity Field didn't update either. I used the dlookup to reference another table. I used it in another control on the form that needs to be filled in before they select the button that will take them to the form that uses the foreign key.

Hope that helps.

Thanks,

Art

John Barnett SSCrazy Points: 2086 More actions · Answer 5

Apologies for the delay in replying, but I think I have now got the answer to this:

In the Form_AfterUpdate event handler on the main form, put something like this:

Private Sub Form_AfterUpdate

Dim lngPK as Long

Me.Requery ' refresh form after save to load PK value into hidden control

lngPK = Me!txtIDValue ' change "txtIDValue" to name of field with PK number

CurrentDb.Execute ("INSERT INTO JunctionTable (IDVAlue) VALUES (" & lngPK & ")", , dbFailOnError) ' Change JunctionTable to the name of your junction table as it appears in the database window.

End Sub

You will need error handling, and obviously need to add in extra fields.

John

Art Sennholtz Old Hand Points: 325 More actions · Answer 6

John,

Thanks for your very helpful reply. I didn't use it 'as-is' but you put me on the right track. To get to the subform, the user has to click on a button. At that time I need the key to enter in to the foreign key. By placing a Me.Refresh at the top of the Click-Button code, I get what I want.

Thank you so much again. I appreciate the time you took.

Art