moody dlookups

[1jet]

Hi all,
Here's my code...

Do

date_box_str = "txtDay" & counter_day
Set date_box = Forms("frmTimesheetManager").Controls(date_box_str)
hour_box_str = "txtProj" & counter_proj & "Day" & counter_day
Set hour_box = Forms("frmTimesheetManager").Controls(hour_box_str)

hour_box.Value = DLookup("[Hours Worked]", "tmp_tblHours", "[when] = #" & date_box & "#")

Loop Until counter_day = 7

This code is meant to populate a row of 7 text boxes (txtProj1Day1 - 7) using the date text box directly above it (txtDay1 - 7) as criteria.

Strange thing is, this worked for a while, but now, I'm getting an "invalid use of null" error message from the DLookup function.

Any ideas?

 

[georgedwilkinson]

That's because you now have a value in date_box of null. You need to either fix the value or handle the null.

 

[1jet]

There's no null in the datebox. It's populated automatically as form.open

I'm still playing around with it....hope I get it working

 

[boblarson]

Wouldn't it be:

Do

date_box_str = "txtDay" & counter_day
Set date_box = Forms("frmTimesheetManager").Controls(date_box_str)
hour_box_str = "txtProj" & counter_proj & "Day" & counter_day
Set hour_box = Forms("frmTimesheetManager").Controls(hour_box_str)

hour_box.Value = DLookup("[Hours Worked]", "tmp_tblHours", "[when] = #" & date_box_str & "#")

 

[1jet]

Sorry Bob, no luck with that.
Changing to "date_box_str" had the DLookup looking for values "txtDay1", "txtDay2" etc from "tmp_tblHours" when the field values I need are dates.

I've also tried the below without any luck...
hour_box.Value = DLookup("[Hours Worked]", "tmp_tblHours", "[when] = #" & date_box.Value & "#")
AND!
hour_box.Value = DLookup("[Hours Worked]", "tmp_tblHours", "[when] = #" & CStr(date_box.Value) & "#")

Here's a visual screen dump and comments...

By f22a at 2008-10-06

A brief explanation...
The timesheet manager is for the manager to review/edit times put into tblHours by the employee.
By selecting the employee (cboSelectEmployee) and choosing a new week ending (Open Calender) executes a SQL MAKE TABLE query "tmp_tblHours". This table has only the hours (and other fields shown above) depending on the new "week ending" chosen.

Strange to see that the Dloopkup works only for the 10/10/2008 with 8 hours.