As you suspected, my example was over-simplified. I actually populate several
fields of a header table with one SQL statement, then need to get the
newly-created header PK to insert as the FK into related detail records using
a subsequent SQL statement.
If I read your post correctly, I just need to structure the insert to occur
via a DAO recordset instead of building the SQL query and then executing it.
To do the additional fields, would it look like something like this?
Set rsCurr = CurrentDb.OpenRecordset("SELECT MyField1, MyField2, MyField3,
MyField4 FROM MyTable WHERE
False")
rsCurr.AddNew
rsCurr!MyField1 = MyData1
rsCurr!MyField2 = MyData2
rsCurr!MyField3 = MyData3
rsCurr!MyField4 = MyData4
lngNewAutonumber = rsCurr!ID
rsCurr.Update
rsCurr.Close
Set rsCurr = Nothing
"Douglas J. Steele" wrote:
> Are you really just inserting a single field? You could try using
>
> Dim rsCurr As DAO.Recordset
> Dim lngNewAutonumber As Long
>
> Set rsCurr = CurrentDb.OpenRecordset("SELECT MyField1 FROM MyTable WHERE
> False")
> rsCurr.AddNew
> rsCurr!MyField1 = MyData
> lngNewAutonumber = rsCurr!ID
> rsCurr.Update
> rsCurr.Close
> Set rsCurr = Nothing
>
>
> That assumes that your Autonumber field is named ID
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no private e-mails, please)
>
>
> "Brian" <(E-Mail Removed)> wrote in message
> news:49D5126A-9227-4C5F-857E-(E-Mail Removed)...
> > Access 2003
> > Front end mde
> > Back end mdb
> >
> > Other than @@Identity, is there any other way to retrieve the AutoNumber
> > PK
> > of a newly-inserted record. Outside a multi-user environment, this is
> > probably just a DMax call, but it seems wise to avoid that approach in a
> > multi-user environment.
> >
> > My usual method of running SQL statements via VBA goes like this:
> >
> > Dim strSQL as String
> > strSQL = "INSERT INTO MyTable (MyField1) SELECT MyData"
> > CurrentDb.Execute strSQL, dbFailOnError
> >
> > Do I have to replace this with code that opens the DAO connection so I can
> > use the @@IDENTITY operator, or is there some other way? Of course, I
> > could
> > use another field where I insert a timestamp, assign it to a variable, and
> > include it in the WHERE clause of the DMAX call, but that also seems like
> > extra work.
>
>
>
Similar Threads
