Insert from command button

[Guest]

Hello, i have a form that takes user input in various text boxes that are not
tied to rowsources because the inserts need to be dynamic. There is a
checkbock that determines how the record(s) will be inserted. (unchecked
causes simple insert, checked performs 2 table insert) How do i code a
command button's onclick event procedure to perform the insert . How can i
perform the 2 inserts (into different tables) pulling the autonumer from the
first insert to populate a foreign key during the second insert? Thank you.

 

[Allen Browne]

The simplest way to do this would be to use a bound form that writes to the
primary table, and use the form's AfterInsert event to write the related
record as well if the checkbox is True.

The AfterInsert event procedure would exeute an Append query statement, and
you can read the value of the newly assigned autonumber from the form and
include it in the query string.

If you want to continue with your unbound form instead, you could
OpenRecordset() on the main table, AddNew, and Update, and so retrieve the
new primary key value to use in appending the value for the related table.
Or, in Access 2000 and following, you can ask Access for the identity value
like this:

Function ShowIdentity() As Variant
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = DBEngine(0)(0)
db.Execute "INSERT INTO MyTable ( MyField ) SELECT 'nuffin' AS Expr1;"

Set rs = db.OpenRecordset("SELECT @@IDENTITY AS LastID;")
ShowIdentity = rs!LastID
rs.Close

Set rs = Nothing
Set db = Nothing
End Function

 

[Guest]

Forgot to mention that i'm using ADP so would this easier be solved by using
a Stored procedure?

 

[Allen Browne]

You can do that if you prefer.