DLookup Question

[ARW17]

I'm using DLookup for the first time and trying to wrap my brain around how it works and the syntax involved.

I want to basically append a table using a form.

Dim chgreqrec As Recordset
Set chgreqrec = CurrentDb.OpenRecordset("AttendanceEvents")
chgreqrec.AddNew


chgreqrec![Event Date] = txtPersonalDate
chgreqrec![Employee] = Employee
chgreqrec![EventTypeCode] = "P"

The code above runs fine. On the code below, I'm getting a type mismatch error. I have an option box where 1 signifies a full personal day and 2 & 3 are half days (am or pm). I want the start time to be the employee's scheduled start time for that particular day, which is held in tblSchedules. I want to report start time where the employee column in the table matches the employee listed on the form and then I want the start time for the correct day for that employee.


If opFullHalf = 1 Or opFullHalf = 2 Then
chgreqrec![StartTime] = DLookup("[tblSchedules].StartTime", "[tblSchedules].Employee = " & Employee And "[tblSchedules].Day = " & txtWeekDay)

Help with the code and and further tips (in English please) about using this function would be appreciated!

 

[Joe4]

chgreqrec![StartTime] = DLookup("[tblSchedules].StartTime", "[tblSchedules].Employee = " & Employee And "[tblSchedules].Day = " & txtWeekDay)

Of the top of my head, it looks like you are missing the 2nd argument (domain) of the DLOOKUP function.
See: https://support.office.com/en-us/article/DLookup-Function-8896cb03-e31f-45d1-86db-bed10dca5937

 

[Micron]

Help with the code and and further tips (in English please) about using this function would be appreciated!

If you are still not successful after reviewing the link provided, post back. The criteria part of this is what trips most people up, what with having to concatenate the bits into a statement with the proper syntax. Know also that you cannot use DLookup on more than one table, which is what you appear to be trying to do. You would have to bring separate tables into a query first.

FWIW, code is always a more complicated way to do what you're trying to do when you can run append, delete or update queries from a form (which is usually simpler).