Help check my form VBA

[benjammin]

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:

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?

[pbaldy]

I use bound forms at least 75% of the time. Basically I use bound forms unless there's some reason I can't. What's your question? The code looks fine offhand. If the tables are going to get large, you'll want to use dbAppendOnly or this type of thing:


Set rst = db.OpenRecordset("SELECT * FROM tblCalLog WHERE 1=0", dbOpenDynaset)

Otherwise you're pulling the entire table, which would be a performance problem.

[benjammin]

If it looks good then all is well. My question now is how do i get it to pull the ID of the record that was created when tblCalLog had the record added and put it into tblCalWL at the same time?

[pbaldy]

Same as here in post 4:

https://www.accessforums.net/showthr...de-not-working

[benjammin]

I have a compile error: Invalid or unqualified reference....
This is after my first table write...

rst.Update    
.Bookmark = .LastModified
    lngARKey = !CalibrationID

[pbaldy]

That user had a With block going, you don't, so for you:

rst.Bookmark = rst.LastModified
lngARKey = rst!CalibrationID


You'd probably want to name the variable something more relevant to you.

[benjammin]

ohhh yes that would make a difference. Awesome, works great! Thanks for the help.

[pbaldy]

No problemo!