Where statement problem

  • Thread starter Thread starter Joel
  • Start date Start date
J

Joel

I have a table with a few test records in it. Second, I have form
where I am passing data for the LvUserName (unbound text field)
information from a previous form and I
am capturing the current date (Date()) in another unbound text field.
Third,
I am trying to find the record in the table that matches these two
fields using a (WHERE) search criteria and post the next three (3)
fields as an update. The process
is posting the three fields but to the first record in the table and it

should be posting to the third record where the table fields match the
WHERE fields. Can anyone help me with this?

Private Sub SaveLvRec_Click()
On Error GoTo Err_SaveLvRec_Click

QrySQL = "UPDATE Timesheet" _
& " SET Timesheet.LvType = '" & Forms!frmLvTimeCard!LvTypeCbo & "'"
_
& " Timesheet.LvStart = #" & Forms!frmLvTimeCard!TxtLvStart & "#" _
& " Timesheet.LvHours = " & Forms!frmLvTimeCard!TxtLvHours _
& " WHERE Timesheet.UserName = '" & Forms!frmLvTimeCard!LvUserName
& "'" _
& " AND Timesheet.TDate = #" & Forms!frmLvTimeCard!TxtLvDate & "#;"

DoCmd.SetWarnings False
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
DoCmd.Close
DoCmd.OpenForm "frmLogon"
DoCmd.SetWarnings True

Exit_SaveLvRec_Click:
Exit Sub

Err_SaveLvRec_Click:
MsgBox Err.Description
Resume Exit_SaveLvRec_Click

End Sub
 
Try this for you where statement.

WHERE ((([Tbl_User].[EmpNum])=[Enter your Employee Number]));
 
Just a shot in the dark: what's your short date format? If it's been set to
dd/mm/yyyy, you're going to have problems with that code. You must use
mm/dd/yyyy (or an unambiguous format such as dd mmm yyyy or yyyy-mm-dd) for
dates in SQL statements, regardless of your short date format.
 
Instead of all those docmd. statements, why not just use:-

currentproject.connection.execute qrySQL

-Dorian
 
Doug, thanks for the suggestion. I went back and looked at the unbound
text field on the form and the field in the table. They are both the
standard (drop down) Short date format (mm/dd/yyyy) with an input mask
of - 99/99/0000;0;_. Do you think this could be the problem?
 
What's the exact string that's stored in QrySQL?

Hold on a moment: I just went back to look at the code you posted again, and
you don't appear to be using QrySQL anywhere (or is that what that cryptic
DoCmd.DoMenuItem is?)

Try:
DoCmd.RunSQL QrySQL

or, my preference (assuming you have a reference to DAO):

CurrentDb.Execute QrySQL, dbFailOnError
 
Doug, thanks again for hanging with me. The following is what I now
have under a cmd button. However, I get a new error.

Private Sub SaveLvRec_Click()
On Error GoTo Err_SaveLvRec_Click
'New Dim
Dim QrySQL As String

QrySQL = "Update Timesheet" _
& " SET Timesheet.LvType = '" & Forms!frmLvTimeCard!LvTypeCbo &
"'" _
& " Timesheet.LvStart = #" & Forms!frmLvTimeCard!TxtLvStart &
"#" _
& " Timesheet.LvHours = " & Forms!frmLvTimeCard!TxtLvHours _
& " WHERE Timesheet.UserName = '" &
Forms!frmLvTimeCard!LvUserName & "'" _
& " AND Timesheet.TDate = #" & Forms!frmLvTimeCard!TxtLvDate &
"#;"
'New Execute
CurrentDb.Execute QrySQL, dbFailOnError

'DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
'DoCmd.Close
DoCmd.OpenForm "frmLogon"

This is the error I now Get -

Syntax Error (missing operator) in query expression "Sick - Fam'
Timesheet.LvStart = #10:00:00 AM# Timesheet.LvHours = 5.5'

The above is exactly how I have it stated in VB.
 
Back
Top