Edit record via calculation

G

Guest

tblEngine contains fields [Eng] and [nextSP].
frmFlightSum contains cboEngine and txtEngineHrs and btnUpdate.
I would like to be able to change the value of [nextSP] for the ENG using
the following. It doesn't seem to be working, and there is probably a much
simpler way to do this. Thanks in advance!

Private Sub btnUpdate_Click()

On Error GoTo Err_btnUpdate_Click
Dim msg As String
Dim db As Database
Dim rs As Recordset
Dim lookup As Double
Dim strEng As String

Set db = CurrentDb
Set rs = db.OpenRecordset("tblEngine")
strEng = cboEngine.Value
msg = "Do you wish to update this record?"

If MsgBox(msg, vbYesNo, "Data Entry Verification") = vbNo Then
FlightDate.SetFocus
Else
Dim stDocName As String
lookup = DLookup("SPChange", "tblEngine", "[Eng] =
cboEngine.value")
rs.FindFirst strEng
rs.Edit
rs!SPChange = lookup + txtEngineTime.Value
stDocName = "UpdateFlights"
DoCmd.RunMacro stDocName
End If

ClearSubform
cmdClearScreen_Click

Exit_Update_Btn_Click:
Exit Sub

Err_btnUpdate_Click:
MsgBox Err.Description
Resume Exit_Update_Btn_Click

End Sub
Teach me to fish! Thanks for the help.
Pax, M
 
C

Carl Rapson

m stroup said:
tblEngine contains fields [Eng] and [nextSP].
frmFlightSum contains cboEngine and txtEngineHrs and btnUpdate.
I would like to be able to change the value of [nextSP] for the ENG using
the following. It doesn't seem to be working, and there is probably a
much
simpler way to do this. Thanks in advance!

Private Sub btnUpdate_Click()

On Error GoTo Err_btnUpdate_Click
Dim msg As String
Dim db As Database
Dim rs As Recordset
Dim lookup As Double
Dim strEng As String

Set db = CurrentDb
Set rs = db.OpenRecordset("tblEngine")
strEng = cboEngine.Value
msg = "Do you wish to update this record?"

If MsgBox(msg, vbYesNo, "Data Entry Verification") = vbNo Then
FlightDate.SetFocus
Else
Dim stDocName As String
lookup = DLookup("SPChange", "tblEngine", "[Eng] =
cboEngine.value")
rs.FindFirst strEng
rs.Edit
rs!SPChange = lookup + txtEngineTime.Value
stDocName = "UpdateFlights"
DoCmd.RunMacro stDocName
End If

ClearSubform
cmdClearScreen_Click

Exit_Update_Btn_Click:
Exit Sub

Err_btnUpdate_Click:
MsgBox Err.Description
Resume Exit_Update_Btn_Click

End Sub
Teach me to fish! Thanks for the help.
Pax, M
Click to expand...

You have to concatenate the value from the combo box onto your Criteria
clause, so your DLookUp statement should look like this:

lookup = DLookup("SPChange", "tblEngine", "[Eng] = '" & cboEngine.value
& "'")

Note the single quote around the value, since it's a string.

Your FindFirst statement should look like this:

rs.FindFirst "[Eng]='" & strEng & "'"

The parameter in a FindFirst call should look just like the WHERE clause in
a SELECT statement, minus the keyword WHERE. Again, note the single quotes
around the value since it's a string.

Carl Rapson
 
Top