-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Hmmmm.... Sorry, I wasn't clear. I meant you're using SQL Server as a
back-end (the data store) instead of JET (Access' default DB engine),
but, still using MS Access to access the DB. If you're using SQL Server
as the back end your front-end (FE) will have .adp as a file extension.
You can also just be linking SQL Server tables to an .mdb Access file.
If you're linked to SQL Server or are using an .adp FE you can still use
the SP I demonstrated in my previous post. NOTE: I made an error in the
INSERT commands - you can't enter values into an AutoNumber column w/o
turning on the IDENTITY_INSERT before running the INSERT commands. You
may not want to do this - IOW, let the Identity function work w/o
interference.
-- Allow identity value to be inserted
SET IDENTITY_INSERT table_name ON
INSERT INTO table1 .... etc.
If you're using straight JET you can use VBA to open a Recordset to
insert the data & use the .LastModified property to get the last created
AutoNumber.
I use DAO so something like this (this goes in a Form's VBA module):
Private Sub cmdOK_Click()
' This is the Click event procedure for the CommandButton named "cmdOK"
' It needs the variables that will go into the tables to be in the
' form's Controls. I'll leave that for you to figure out.
' HINT: Me!rec_nbr Me!pin_nbr
'
const TBL1 = "SELECT old_id, rec_nbr, pin_nbr, birth_date " & _
"FROM table1"
' ID_Field will hold the AutoNumber value obtained from entering
' data into table1.
const UPD2 = "INSERT INTO table2 (ID_Field, field2, field3, " & _
"field4) VALUES ("
dim db as dao.database, rs as dao.recordset
dim strSQL as string
' this will hold the just inserted @@Identity
dim intNewAutoNumber as integer
set db = currentdb
set rs = db.openrecordset(TBL1)
with rs
.addnew
' if the old_id is the AutoNumber column
' comment out the next line - AutoNumber columns
' can't be loaded when there already is data in the tbl
!old_id = intID
!rec_nbr = intRecNbr
!pin_nbr = intPIN
!birth_date = dteDOB
.update
.Bookmark = .LastModified
' be sure to use the name of the AutoNumber field in table 1
' on the right side of the assignment statement
intNewAutoNumber = !AutoNumberFieldName
end with
' Run the 2nd INSERT INTO command.
' Add the variables to the VALUES clause
' -- be sure to put in the appropriate delimiters for
' strings and dates. Ex, if field2 was a string:
'
' ... & ",'" & strField2 & "'," & ...
' change variable names to whatever they should be in real life
strSQL = UPD2 & intNewAutoNumber & "," & intField2 & "," & _
intField3 & "," & intField4 & ")"
' just run it
db.Execute strSQL, dbFailOnError
' put in some error handling
End Sub
HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBSe00BYechKqOuFEgEQJ2tACgwKRh31qp+mqL5wzkARtMUdvs1wAAoMqi
9H4TZyLPgunDA4tIN0lFnYTW
=IzgL
-----END PGP SIGNATURE-----
