Retrieving a PK once a record has been added.

[Chris]

Hello group. I'm sure this is an easy question but I can't figure it out.

All I want to do is the following.
1) Insert a record into the master table
2) Get the value of the new PK (autonumber)
3) Add the value as a FK into a child table.

step 1 is not a problem, I've just written an insert Statement.
step 2 is the problem. I would normally use a DLookup statement to lookup
the value of the primart key (using the newly inputted values for 'Where
clause') However, in rare incidents, 2 records could be the same (e.g.
Client has 2 different cars with the same value ).

So my options are;
1) Order the table by the highest primary key value a then do the lookup,
don't know how to lookup this way
2) Bind the controls to the table ( don't like this appraoch as the I will
have to make the form bound)
3) Something else - think this is the best option.

Please advise me on the best method.

Tables
tblAssets ( AssetId (pk), ClientId(fk), AssetTypeId
tblvehicle (VehicleID (pk) AssetID(fk) Registration, make, model, value
tblproperty (propertyID (pk) AssetID (fk) Address, PostCode, Value
tblotherAsset (OtherAssetID(pk) AssetID(fk) AssetName, Value

 

[Tom Ellison]

Dear Chris:

Of course, the foreign key row must be added first, as you propose doing.

Use the natural key relationship to a unique natural key relationship in the
foreign key table to retrieve the autonumber value from the foreign key
table.

An alternative is just to make the relationship based on the natural keys.
In this case you do not need the autonumber columns at all.

Tom Ellison

 

[Albert D.Kallal]

Hello group. I'm sure this is an easy question but I can't figure it out.

All I want to do is the following.
1) Insert a record into the master table

strSql = "INSERT INTO tblCustomer........"

currentdb.Execute StrSql

' get last autonumber id
strSql = "select @@identity from tblCustomer"
lngNewid = currentdb.openRecordSet(strSql)(0)

The above will thus get you the last autonumber.

3) Add the value as a FK into a child table.

Just build your insert


strSql = "INSERT INTO tblLoans ("Customer_ID, field1, field2, etc.& ")" & _
" values (" & lngNewID & ", value1, value2, etc)"
currentdb.Execute strSql

 

[John Spencer]

Albert,
What am I doing wrong?

Access 2000 and JET

I've tried the following function and while I get no error, I always get
zero returned.

My test function
Public Function LastPrimaryKey(strTableName As String)
Dim strSQL As String
Dim rtn As Variant
Dim dbany As DAO.Database
Set dbany = CurrentDb()

strSQL = "SELECT @@identity FROM [" & strTableName & "]"
LastPrimaryKey = dbany.OpenRecordset(strSQL).Fields(0)

End Function

When I call that
LastPrimaryKey("FAQ")
I always get zero returned. FAQ has an autonumber as its primary key.