Hi, I'm working on creating a small inventory/calibration database. Up until recently I was able to get away with using bound forms to do my data entry (perhaps much to the chagrin of the pros here). Now I've run into a situation where I want to enter in calibration information and it requires me to write to two tables so I can produce the query/report I want. Here's how I'm doing this:
Code:
Private Sub cmdSaveAdd_Click()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("tblCalLog", dbOpenDynaset)
rst.AddNew
rst!DateTime = txtDateTime
rst!CheckedBy = txtCheckedBy
rst!EquipmentID = cboEquipmentName
rst.Update
Me!txtDateTime = ""
Me!txtCheckedBy = ""
Me!cboEquipmentName = ""
rst.Close
Set rst = db.OpenRecordset("tblCalWL", dbOpenDynaset)
rst.AddNew
rst!SiteID = cboSiteName
rst!LocationID = cboLocationName
rst!WellID = cboWellName
rst.Update
Me!cboSiteName = ""
Me!cboLocationName = ""
Me!cboWellName = ""
rst.Close
db.Close
End Sub
This works but my question is: Is this an acceptable way to write to a table?
The reason I'm doing this is tblCalLog has general calibration information, just the equipment and date/time it was checked, this is so i can run a report/query to show what instruments will be needing calibration after X months. tblCalWL is the specific table with the calibration information for the well or the instrument, i.e. the actual measurements taken and any associated notes. tblCalLog will hold ALL calibrations and then tables such as tblCalWL will be underneath that holding specific information for each tblCalLog record.
One more question. When I write to tblCalLog, it creates a new record, how do I pull the ID (autonumber field) that was just created in tblCalLog and write it to tblCalWL so they're related?