Create a record in a table and have that ID equal record in diff t

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello Access people,

I would like to have a new record in Table B where the ID for that record is
the same as a record in Table A. This would be in a form that is related to
Table A. What is the code for that? Thanks in advance.

Neal
 
You could execute an append query like:

strSql = "INSERT INTO TableB ( ID, OtherColumn) " _
& "SELECT ID, OtherColumn FROM TableA " _
& "WHERE ID = " & 12345
DoCmd.RunSql strSql

or with alternate sql syntax

intNumberValue = 12345
strTxtValue = "Hi Mom"
strSql = "INSERT INTO TableB ( ID, OtherColumn) " _
& "VALUES (" & intNumberValue & ", '" & strTxtValue & "')"
DoCmd.RunSql strSql

or you could open a DAO or ADO recordset to table and add a record with an
recordset.AddNew

DAO Example
Set db = CurrentDb()
Set rs = db.OpenRecordset("TableB", dbOpenDynaset)
rs.AddNew
rs!ID = 12345
rs!OtherColumn = "Hi Mom"
rs.Update
rs.Close
Set rs = Nothing
Set db = Nothing

ADO Example
Set rs = New ADODB.Recordset
rs.Open "TablelB", CurrentProject.Connection
rs.AddNew
rs!ID = 12345
rs!OtherColumn = "Hi Mom"
rs.Update
rs.Close
Set rs = Nothing

or...

Well you get the idea. Without a lot more information about what you really
want to do, and about what you have already done, there is not much that
anyone can do to help you much more than this.

Ron W
 
Back
Top