Adding record with an auto-increment field

[DanR]

Hello

I'm really stuck, with a piece of code that has worked in the past, but doesn't now.

It adds a record to an ADO recordset based on an SQL query which joins 2 tables. The same unique ID must end up in the primary key of both tables.

The primary key of one of the tables is an AutoNumber field. The primary key of the other table is indexed, but not autonumber. The same value is assigned by code from the Autonumber field, before updating.

The problem is, that autonumber field retains a null value, so the primary key of the other table also retains a null value, which causes an error (Primary key can't contain a null value) when it hits the update command.

I've tried using the Update Resync property, to no avail.

Any suggestions for how I might avoid this problem?

Code attached

thanks (in advance) for your help
Dan

 

[llkhoutx]

The January 2002 edition of the Access VB SQL Advisor magazine has the solution to this problem. As I recall, I didn't buy the magazine, it goes as follows:

public function lngBuildSeqID(Arg as variant, Optional lngStart) as long

static lngIndex as long
ifexists(lngSrart) then (I think ifexists is the right function)
lngBuildSeqID=lngstart
else
lngBuildSeqID=lngBuildSeqID+1
end if
end function

Call the function in your code with lngStart=0, to reset the counter; Arg is an arbitrary field in one of the tables being queried - it is a dummy arg and is not used. Without arg, lngIndex won't increment, you'll get the same value for each row.

 

[DanR]

Thanks llkhoutx

I'll give this a try.

In the interim, I came up with a different solution.

This was to open the two tables as separate recordsets, and create the new record in each concurrently. The code then updates the first recordset and retrieves the autonumber value after update so that it can be attributed to the primary key in the second recordset, which is still in edit mode at that point.

cheers
Dan