appending records to multiple tables

[access345]

I am interested in appending records to multiple tables at the same time. I have the following code:

Public Sub txtSerialNumber_Enter()

Dim txtSerialNumber As String
Dim SerialNumber As Long

Dim newrecord As DAO.Recordset
Set newrecord = CurrentDb.OpenRecordset("TblLotNumber")
Set newrecord = CurrentDb.OpenRecordset("TblIncomingInspection")
Set newrecord = CurrentDb.OpenRecordset("TblFlashTest")
Set newrecord = CurrentDb.OpenRecordset("TblFunctionalTest")

newrecord.AddNew
newrecord!SerialNumber = Me.txtSerialNumber
newrecord!LotNumber = "(Now),mm/dd/yyyy"



End Sub

What is happening is the serial number I enter goes into TblLotNumber and no other table. The Lot number instead of being todays date is "(Now),mm/dd/yyyy"

 

[PHV]

Something like this ?
Public Sub txtSerialNumber_Enter()
Dim txtLotNumber As String, tbl As String
txtLotNumber = Format(Now, "mm/dd/yyyy")
For Each tbl In Array("TblLotNumber", "TblIncomingInspection", "TblFlashTest", "TblFunctionalTest")
CurrentDb.Execute "INSERT INTO " & tbl & " (SerialNumber,LotNumber) VALUES (" & Me!txtSerialNumber & ",'" & txtLotNumber & "')"
Next
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[access345]

When I entered this :

Public Sub txtSerialNumber_Enter()

Dim txtLotNumber As String
Dim tbl As String

txtLotNumber = Format(Now, "mm/dd/yyyy")

'For Each tbl In Array
'("TblLotNumber", "TblIncomingInspection", "TblFlashTest", "TblFunctionalTest")
CurrentDb.Execute "INSERT INTO " & TblLotNumber & " (SerialNumber,LotNumber) VALUES (" & Me!txtSerialNumber & ",'" & txtLotNumber & "')"
CurrentDb.Execute "INSERT INTO " & TblIncomingInspection & " (SerialNumber,LotNumber) VALUES (" & Me!txtSerialNumber & ",'" & txtLotNumber & "')"
CurrentDb.Execute "INSERT INTO " & TblFlashTest & " (SerialNumber,LotNumber) VALUES (" & Me!txtSerialNumber & ",'" & txtLotNumber & "')"
CurrentDb.Execute "INSERT INTO " & TTblFunctionalTest & " (SerialNumber,LotNumber) VALUES (" & Me!txtSerialNumber & ",'" & txtLotNumber & "')"


End Sub


I get a runtime error 3134

 

[PHV]

CurrentDb.Execute "INSERT INTO TblLotNumber (SerialNumber,LotNumber) VALUES (" & Me!txtSerialNumber & ",'" & txtLotNumber & "')"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[access345]

I don't get a runtime error anymore. What is happening now is in TblLotNumber I get three records created. The first record has no serial number just the lot number. The second record is the serial number with no lot number. The third record is no serial number and the lot number.

In my Incoming Inspection table I have two records with no serial numbers but the lot number.

In my functionaltest table I have one record with no serial number and the lot number.

 

[PHV]

I guess you want UPDATE queries instead.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[access345]

Thank you PHV. I realize now what my fundamental mistake was. It was thinking that Access can be a relational. Now I realize that having a relational database is just a theoretical dream. The reason the serial number of the unit had to be copied into multiple tables was instead of having one table which has all information in it. I thought I would create multiple tables each with it's own function (Much like I read in the programming books). One table would track what each operation did. The common linkage would be the serial number. I tried to create an update query to copy the records into TblIncomingInspection that I had just entered into TblLotNumber. The result I got was "You are about to update 0 records."

This is my query:
UPDATE TblIncomingInspection INNER JOIN TblLotNumber ON TblIncomingInspection.SerialNumber = TblLotNumber.SerialNumber SET TblIncomingInspection.SerialNumber = [TblLotNumber.SerialNumber], TblIncomingInspection.LotNumber = [TblLotNumber.LotNumber]
WHERE (((TblLotNumber.SerialNumber)=[Enter Serial Number]));

If I can't get this to work I'll just combine my tables.