how to link tables

[Daniel M]

I have a database where i am trying to split the main table down to 2 tables.
I am not using a bound form so i am not sure how to link the 2 together. I am
doing something like this...

Dim Rs As Recordset
Dim RS2 As Recordset
Set Rs = CurrentDb.OpenRecordset("Assets")
Rs.AddNew
Set RS2 = CurrentDb.OpenRecordset("Return")
RS2.AddNew
Rs![Dateentered] = Dateentered
Rs![EmployeeID] = Employees.Value
Rs![SerialNumber] = SerialNumber
RS2![cCustomerDamage] = chkCustomerdamage.Value
RS2![cNotCalling] = chkCallingCustomer.Value

Rs.Update
RS2.Update
Rs.Close
RS2.Close
Set Rs = Nothing
Set RS2 = Nothing

Both tables contain the field AssetID and they are linked together with a
relationship. I would like to see the AssetID in the Assets table to be the
same as the AssetID in the Return table. Can anyone give me some idea on how
to do this? Thanks.

 

[Douglas J. Steele]

Rather than having two recordsets, each based on a table, try a single
recordset, based on a query that joins the two tables.

Something like:

Dim strSQL As String

strSQL = "SELECT Assets.Dateentered, Assets.EmployeeID, " & _
"Assets.SerialNumber, Return.cCustomerDamage, " & _
"Return.cNotCalling, Assets.AssetID " & _
"FROM Assets INNER JOIN Return " & _
"ON Assets.AssetID = Return.AssetID"

Set Rs = CurrentDb.OpenRecordset(strSQL)

Note, though, that sometimes recordsets based on queries joining tables
together can be read-only. If that happens to you, check what Allen Browne
has at http://www.allenbrowne.com/ser-61.html

That having been said, your comment "I would like to see the AssetID in the
Assets table to be the same as the AssetID in the Return table" is a little
confusing. I'm assuming that AssetID is the primary key of the Assets table.
As such, it may well be an AutoNumber field. You'd simply take the value of
AssetID from the Assets table and use the same value for the AssetID in the
Return table. You didn't happen to make AssetID an AutoNumber field in
Return, did you? If so, change it to a Long Integer.