Getting newly created PK for foreign key in 2nd table 1

[MrV123]

I have 2 tables, CONTACT and ADDRESS. The primary key in the ADDRESS table is a foreign key in the CONTACT table.

I want to write some VB to add a new Contact. This means first adding an entry in the ADDRESS table (to determine the auto-numbered primary key) and then adding an entry in the CONTACT table, including the newly assigned address PK.

Looking at previous posts, it looks like there isn't a neat way of automatically determining the (newly assigned) ADDRESS primarykey and using this in the CONTACT insert statement. Rather, a select statement is needed to obtain this primarykey, followed by another insert statement into the CONTACT table. Please can someone confirm that this is indeed the case?

If this is the case, I have a problem in that I can't get the (newly assigned) ADDRESS primarykey... it looks like the insert statement hasn't been committed yet, and so the PK hasn't been created yet. (I get a Run-time error '3021' - No Current Record on the rst.MoveLast line - see below)

I don't really want to force a commit as I may need to roll back. I guess I'm looking for something similar to the @@IDENTITY command in SQL Server...

Please can someone advise me on the best way to approach this and apologies in advance if this has been answered many times before... it must be a common question, but I can't find a suitable answer.

Thanks in advance.


My existing code is shown below:


<code>
Dim AddressSQL As String

AddressSQL = "INSERT INTO ADDRESS (AddressLine1, AddressLine2, City, County, Country, Postcode) values (AddressLine1, AddressLine2, City, County, Country, Postcode)"
DoCmd.RunSQL AddressSQL

Dim dbs As Database, rst As Recordset, strSQL As String
Dim fld As Field

Set dbs = CurrentDb
strSQL = "SELECT DISTINCTROW [AddressPK]" _
& "FROM ADDRESS WHERE [AddressLine1] = ' & AddressLine1 & '"

Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)
rst.MoveLast

<code>

 

[Golom]

This may be a problem

AddressSQL = "INSERT INTO ADDRESS (AddressLine1, AddressLine2, City, County, Country, Postcode) values (AddressLine1, AddressLine2, City, County, Country, Postcode)"

I'm assuming that "AddressLine1", "AddressLine2", etc. in the "VALUES" clause are the names of variables in your code. This statement will however probably fail because

- You need to enclose text values in quotes and
- The system will use the text string "AddressLine1" (for example) rather than the value in the variable of that name.

Try something like

AddressSQL = "INSERT INTO ADDRESS (AddressLine1, AddressLine2, City, County, Country, Postcode) " & _
VALUES ('" & AddressLine1 & "', '" & _
             AddressLine2 & "', '" & _
             City & "', '" & _
             County & "', '" & _
             Country & ', '" & _
             Postcode & "')"

 

[MrV123]

Thanks for the reply Golom.

The insert SQL statement works fine. You are correct in assuming that "AddressLine1" etc are variables defined n my code - I should have inclued that.

The main point I would like to understand is how to write code which will do the following:

1) Insert a record into a table (Not a problem)

2) Obtain the auto-assigned primary key (this is a problem, as the primary key doesn't seem to get generated unless I 'End' the program execution and then physically look at the table.)

3) Insert a record into a 2nd table, including the primary key from table 1.

Thanks!

 

[RoyVidar]

You can use @@identity with Jet 4.0. It's probably achievable through DAO, but I don't know sufficient DAO. Using ADO, you can do select @@identity on the same connection as the record was inserted on:

[tt]dim cn as adodb.connection
dim rs as adodb.recordset
set cn = currentproject.connection
cn.execute AddressSQL
set rs = cn.execute("select @@identity",,adcmdtext)
msgbox rs.fields(0).value[/tt]

Then use the return in the insert to the other tables.

Roy-Vidar

 

[MrV123]

Many thanks Roy-Vidar - that works perfectly.