Lifting value from a combobox,searching for the value,editing related field on record

[Theguyinthehat]

Designing a database able to hold a lot of simultaneous lots that needs at least two edits after creation--Time Worked and Time Completed. However, I am having trouble. I need to:

1) Lift the Lot Number off of the combo box
2) Search for the Lot Number in the Job Entry Table (it could be anywhere)
3) Edit the Time Worked field for the corresponding Lot Number.

My code so far:


Private Sub Save_Click()
Me![TimeWorking].Value = Now()
Dim db As Object
Dim that As Object
Set db = CurrentDb
Set that = db.OpenRecordset("JobEntry")
DoCmd.FindRecord Me![LotNumber]
that.Edit
that("Time Working").Value = Me![TimeWorking].Value
that.Update
End Sub

**TimeWorking is a text box
"Time Working" is the field value
LotNumber is the Combobox and the primary key in the table
This code doesn't work--it thinks I'm trying to make a new entry. Please help!

 

[pbaldy]

I would dump the FindRecord line and do this:

Set that = db.OpenRecordset("SELECT [Time Working] FROM JobEntry WHERE LotNumber = " & Me.LotNumber)

See if that works for you. I would add a test for EOF, but we can do that if this works. Personally I would also disambiguate the declarations to DAO.Database and DAO.Recordset respectively.

 

[Theguyinthehat]

Got a 3464 mismatch in criteria error... I'm pretty new to VB (though I programmed in Java and PERL), and don't know what to make of this--is the Working Time incomparable? It seems like that code should work, but its that line that's giving me trouble. Is it a matter of the Lotnumbers from the cbo not being the same type as the LotNumbers of the table JobEntry?

 

[pbaldy]

Error on what line? Try this if LotNumber is text:

Set that = db.OpenRecordset("SELECT [Time Working] FROM JobEntry WHERE LotNumber = '" & Me.LotNumber & "'")

If the time field is really date/time this should work:

that("Time Working").Value = Now()

 

[Theguyinthehat]

Works! I didn't realize that text and integers couldn't be compared--but you saw it anyway. Thanks for your help!

 

[pbaldy]

No problemo, and welcome to the site by the way!