Running a VB If/Then on List Box values in a Form

[Gaffi1]

I have a form where we enter our employees start, stop times, and then utilizing some VB code and some macro's their total hours is calculated and stored. Off to the side, I have 5 list boxs' that return 1) The hours worked on that shift, 2) The hours worked for that entire day, 3) The hours worked that entire week, 4) The hours worked over 40, and 5) The hours that were billed as overtime.

I coded a VB check to make sure that if 4 and 5 don't match up, a msg box is displayed to alert the user as to the type of error they made when entering that individuals time so they can go back and fix it.

If (Forms!PayrollDataEntry!eeadd.Form!List188 > Forms!PayrollDataEntry!eeadd.Form!List190) Then
    MsgBox ("You Have Not Billed This Employee's Overtime as Overtime.")
End If
If (Forms!PayrollDataEntry!eeadd.Form!List190 > Forms!PayrollDataEntry!eeadd.Form!List188) Then
    MsgBox ("You Have Billed More Overtime than the Employee Worked.")
End If

My problem is that Access is not seeing any value in the list box, and therefor the check isn't working. Anyone have any ideas on what I can do to get this to work?

 

[Eupher]

Are your listboxes all showing multiple rows of data? If not, why not use texboxes instead? Here's the deal with listboxes: if it's a simple listbox, the value of the box is the bound value of the selected item. No selection, value is null. With a multi-select listbox, the value is always null - to use the values, you must refer to the ItemData property of the box's ItemsSelected collection.

HTH,

Ken S.

 

[Gaffi1]

The list box show only 1 row of data. Is there a way to automatically select that data so that there is a value?

In regards to using a text box instead, I used a list box to pull those values because they are calculated by a query. So by using a list box the result of the query could be pulled into the form without any problems resulting. If I change it back to a text box, I don't know how to set the text box equal to the value of the query. Any suggestions?

 

[PHV]

Take a look at the DLookUp function.

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

 

[Gaffi1]

Thanks, I gave that a shot, and ran into a new problem of course, lol.

Here is what I changed the code to.

If (DLookup("SumofSumofHoursWorked", "ShiftHoursPart7") > DLookup("Overtime", "ShiftHoursPart6")) Then
    MsgBox ("You Have Not Billed This Employee's Overtime as Overtime.")
End If
If (DLookup("SumofSumofHoursWorked", "ShiftHoursPart6") > DLookup("Overtime", "ShiftHoursPart7")) Then
    MsgBox ("You Have Billed More Overtime than the Employee Worked.")
End If

 

[Gaffi1]

hmm, it cut off my previous post for some reason. The error message I am now getting is Runtime Error 2001, you canceled the previous operation.

Thanks in advance for everyones help

 

[Gaffi1]

I also tried:

putting in brackets i.e. "[SumofSumofHoursWorked]" but it didn't help any either.

putting Int() around the Dlookups, no luck either.

 

[Gaffi1]

OK, I'm retarded. I had transposed the field name on the second DLookup. IT's working now,thanks!

 

[Eupher]

What are "ShiftHoursPart6" and "ShiftHoursPart7"? Are they saved queries? The domain argument of the DLookup function is typically a table or query.

Ken S.

 

[Gaffi1]

Yeah they are saved queries

 

[Eupher]

Gaffi1,

Glad you got it sorted out.

Ken S.