Multiple criteria Unbound form using date & number Need help

[syoung4]

Please can anyone help me. I have made an unbound form that looks at one recordset for overtime hours.

Each week the data from a time sheet will be inputed.
I need to find a way to make the unbound form find the employeeId and the week that the the overtime was done.
In case the record has to be updated. As some time sheets
come in late.
The unbound form is populated from the record set by selecting the employee Id and if possible the week ending.

I have managed to get the form find with an employee Id but I can't get the form to find both an Employee Id and the Week ending together which would make a unique record.
Here is my code below......

Private Sub cboFindEmployeeID_AfterUpdate()
Dim D As Database
Dim rsOtime As Recordset
Dim Criteria As String

Set D = CurrentDb

Set rsOtime = D.OpenRecordset("tblOvertime", DB_OPEN_DYNASET)


'This finds EmployeeId and Week Ending together in Overtime table so that only one unique record is found
'Criteria = "Val([EmployeeID])=" & Me![cbofindEmployeeID] & " AND val([WeekEnding]) = " & "#" & Format(Me![cbofindWeekEnding], "dd/mm/yyy") & "#"
'But it still does not retreive a matched record and is the line I am having trouble with.

'This criteria works ok but it is only on one field [cbofindEmployeeId]
Criteria = "Val([EmployeeID])=" & Me![cbofindEmployeeID]

'This criteria also works ok but still only one field.
'Criteria = "[EmployeeID] = " & [cbofindEmployeeID] This works ok


'This section takes the fields from the recordset and populates the unbound form fields.
rsOtime.FindFirst Criteria

Me!EmployeeID = rsOtime("EmployeeID")
Me!NAME = rsOtime("NAME")
Me!weekending = rsOtime("WeekEnding")
Me!MontoFri = rsOtime("MontoFri")
Me!Saturday = rsOtime("Sat")
Me!Retroweek = rsOtime("Retroweek")
Me!RetroSat = rsOtime("RetroSat")
Me!RetroSun = rsOtime("RetroSun")
Me!Comment = rsOtime("Comment")

If rsOtime.NoMatch Then

MsgBox "Sorry no record found for EmployeeID: " & Me!cbofindEmployeeID
Me!cboADDEmployeeID.SetFocus
End If


rsOtime.Close


End Sub

Thank you.

 

[PHV]

Perhaps something like this ?
Criteria = "EmployeeID=" & Me!cbofindEmployeeID & " AND Format([WeekEnding],'yyyyww')=" & Format(Me!cbofindWeekEnding, "yyyyww")

BTW, shouldn't "dd/mm/yyy" be "mm/dd/yyyy" ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[syoung4]

Thank you so much for your help.
I have put the criteria into my form and it works perfect.

I have spend days trying to work it out.

Thank you.

Best regards,

Sid.