Newly added record's key

[NT100]

Hi,

An access form is created for the user to add records. My code to handle the input is that once "SAVE" command is pressed, I'll use a recordset and a ".AddNew" command followed a ".update" command to save the input into an ACCESS table - tblTutor, for example. (the key of the newly added record will be generated by ACCESS once .update is done)

The next step is that I need to insert into another table, tblProcessTNewAppt, for example, by SQL with this newly added record's key

My problem is how I can get the key from tblTutor.

Thanks in advance.

 

[bob fitz]

Assign the key to a variable before the recordset is closed. The variable can then be used in the SQL statement

 

[arnelgp]

you must add AutoNumber field in your table.

create a variable of Variant type.
set the value of this variable to the
recordset.LastModified after issuing .Update
command, eg:


Dim rs As Dao.REcordset
Dim varBook As Variant
Dim lngID As Long

Set rs = Me.RecordsetClone
rs.AddNew
rs!Field1 = Me.text0
rs.Update

varBook = rs.LastModified
'now get the ID (autonumber field)
rs.BookMark = varBook
lngID = rs("ID")

'close the recordset
rs.close
Set rs=Nothing
'insert to other table using SQL
currentdb.Execute "Insert into table2 (Field1) SELECT " & lngID & ";"

 

[NT100]

you must add AutoNumber field in your table.

create a variable of Variant type.
set the value of this variable to the
recordset.LastModified after issuing .Update
command, eg:


Dim rs As Dao.REcordset
Dim varBook As Variant
Dim lngID As Long

Set rs = Me.RecordsetClone
rs.AddNew
rs!Field1 = Me.text0
rs.Update

varBook = rs.LastModified
'now get the ID (autonumber field)
rs.BookMark = varBook
lngID = rs("ID")

'close the recordset
rs.close
Set rs=Nothing
'insert to other table using SQL
currentdb.Execute "Insert into table2 (Field1) SELECT " & lngID & ";"

Great! It works excellent. Thanks a lot
I've a question on this update. Does it also work in network environment as I've read @@IDENTITY is an alternative but can't work fine in network environment.

 

[arnelgp]

you should try in test data to find out.

 

[NT100]

Further to add an automatic increment number into another table, I need to add the rest column values at that table as well. The script is as follows:

CurrentDb.Execute "Insert into tblTNewAppt (TRef, AppointedBy) VALUES " & "(" & lngID & "," & txtAppointedBy.Value & ");".

However, I got "Run-time error "3061", too few parameters. Expected 1.".

What's wrong with my code above.


Thanks in advance.

 

[arnelgp]

you are missing a quote on your query:

CurrentDb.Execute "Insert into TblTNewAppt (TRef, AppointedBy) Values " & _
lngID & ", " & Chr(34) & [txtAppointedBy].Value & Chr(34) & ");"

Or

CurrentDb.Execute "Insert into TblTNewAppt (TRef, AppointedBy) SELECT " & _
lngID & ", " & Chr(34) & [txtAppointedBy].Value & Chr(34) & ");"

 

[NT100]

I don't quite understand what you're driving at. would you kindly advise on this?

Thank you.