Dlookup to match two criteria and return value

[randolphoralph]

That is intresting..I changed it to the After Update event and then I went to record #2 and entered 104 in the Employee ID field and received a Run-Time Error 13 Type Mismatch. I clicked Debug and it takes me to the line below.

 
RefWorkload = Nz(DLookup("Ref_Workload", "[Employee Table]", "[Employee ID] = " & Forms![Employee Table]![Employee ID] & " AND [Date of Work] =#" & Forms![Employee Table]![Date of Work] & "#"), 0)

I ready to pull my hair out

[pbaldy]

Ah, I missed this. Your field ([Ref Workload]) has a space in it, not an underscore. Try this:

RefWorkload = Nz(DLookup("[Ref Workload]", "[Employee Table]", "[Employee ID] = " & Forms![Employee Table]![Employee ID] & " AND [Date of Work] =#" & Forms![Employee Table]![Date of Work] & "#"), 0)

Also, this form is bound to the same table that values are being looked up from, so as you change values on the form you're also changing values in the lookup table. Not sure what your goal is, but typically the lookup would be performed in a different table.

[randolphoralph]

Ok I have changed the Ref Workload field.

Now maybe I misunderstand what this is suppose to do.

Here is what it is currently doing...

Record 1
Employee ID: 104
Date of Work: 5/20/2010
Ref Workload: 200
Record 2
Employee ID: 104
Date of Work: 5/20/2010 (Automatically fills in Date)
Ref Workload: 0 (Value Returned from DLookup)


Here is what I need it to do....

Record 1
Employee ID: 104
Date of Work: 5/20/2010
Ref Workload: 200
Record 2
Employee ID: 104
Date of Work: 5/20/2010 (Automatically fills in Date)
Ref Workload: 200 (Value Returned from DLookup)

I am sorry to be such a pain. My goal is just to make it easy for the Database user so they do not have to enter the same information over and over.

[pbaldy]

A couple of problems. Since you're filling in the employee before the date is filled in, the DLookup will fail. You could put a default value in the date field to get around that (or fill in the date first). Second, you have a default value on the Ref Workload field, so the zero there is causing this test to fail:

If Len(Me.Ref_Workload & "") = 0 Then

So the value won't be changed. If I change those, I get the behavior you describe. It still seems odd to be to be looking up in the same table.

[randolphoralph]

I got it working. It looks like on the test file I sent to you...the default value of Ref Workload was 0 and on my working copy there was no default value so I had to change the code to read as such...

If Len(Me.Ref_Workload & "") = "" Then

OMG....I can not believe the fix was that simple....

Paul I want to tell you thank you for all your help on this.

[pbaldy]

No problem, glad you got it sorted out.