2 Listboxes for Criteria (1 Viewer)

[DMerchen]

I looked through the forum and I am not finding an exact match to my problem. It seems simple enough, but I can't seem to sort it out. I am working on a timesheet program. On one form I have 2 listboxes. One has all of the employees, and the other contains dates the timecard needs to be submitted. It is based on date values two weeks apart. When I make these two selections I want to be able to open the main timesheet form with the user and the relevant date. Right now it filters for the name, but the date is not working. If this is based on a particular date i want to be able to either edit the values for the timecard or start a new timecard if no values have been logged against this date. I hope this makes sense. Is this possible to filter based on two listboxes? Thanks for the assist.

Dave

 

[dkinley]

Yes ....

In your 'where' argument use the word 'And' to seperate the two criteria expressions.

-dK

 

[DMerchen]

Thanks for the response. I have run into a bit of a problem. One of my listboxes for my criteria is a list of dates. I can't seem to get the date value out of the listbox. I guess it is actually a combobox if that makes a difference. How can I get the date value from the combobox? I have it formatted like m/d/Y.
Any help is appreciated.

Dave

 

[dkinley]

Help me understand "can't seem to get the date value out of the listbox."

Are you trying to store this value in a variable or just refering to the listbox's value as criteria?

-dK

 

[DMerchen]

I have the dates in a combobox. Basically I am trying to do both. I am loading it to a variable to use as criteria. Perhaps I am mistaken in trying to do it this way and I can simply use the combobox as a criteria? Anyway, I have two criteria. One is the user and the other is the date. I seem to be able to get the user with no problem to load into a variable, but I can't extract the actuall date value from the combobox. I can get the index number, and I tried CDate, but that gives me 12-31-1899 I believe. So, if I have the list of dates in the combobox and one of the dates is 10-28-2008 and I select it, I want to be able to use that as one of my criteria. However I can do it. Perhaps there is a better way than to load it into a variable.

Thanks for the assist.
Dave

 

[dkinley]

If you are storing the index number in the data source (the index number is the bound column) then that could be the issue.

Let's suppose that your combo box has two columns. The first one is the index number and the second is the date. You have it set up so it is bound to the index number but the user only sees the date.

In this case you can either load the variable or use directly with Me.cboComboBoxName.Column(2).

Well, now I am forgetting, it might be Column(1).

-dK

 

[DMerchen]

Okay, that helped me along. It seems I am getting all of the criteria loaded correctly into the variables now. When I run it I get a type mismatch for some reason and I am really not sure why. The form I am trying to open has two text fields, employee and date. The form opens with the correct employee if it is based on that single criteria, but when I add the date criteria too, then I get the type mismatch. Really not understanding why this is the case. I am using a string for the variable and the date on the form I am attempting to open is a textbox.

Thanks for the assistance with this.

 

[dkinley]

No problem. I do not do a whole lot with dates, so I don't readily know all the rules off-hand.

The date mismatch could be a couple of things, I would first check to make sure it is the right column. You can either do this in the immediate window or just add a MsgBox Me.cboComboBoxName.Column(x) to make sure that it is the right column you are pulling (sub x for a number).

If that is correct, what I don't know if your formatting of the date field would have anything to do with this (this is the part of the rules that I don't know so well - I use the standard format mm/dd/yyyy to avoid it).

I am not sure if you would have to use '# Me.cboComboBoxName.Column(x) #' to specify it's a date control or play with the format as you have done in other controls in order to get this right. I am not that strong on date manipulation from a customized perpsective.

I am not sure if any of this helps, but if you run into a road block past this and if you would like to post the db I could better assist.

-dK

 

[DMerchen]

Still running into problems with this. Here is the database. Perhaps you will look at this and see the problem right away. Thanks for your assistance.

 

[boblarson]

Still running into problems with this. Here is the database. Perhaps you will look at this and see the problem right away. Thanks for your assistance.

You're right - it took about 20 seconds:

change to:

Private Sub cmdTime_Click()
On Error GoTo Err_cmdTime_Click

    Dim stDocName As String
    Dim stNameCriteria As String
    Dim stDate As String
    
    stDocName = "Timecard"
    
    stNameCriteria = "[Employee]=" & "'" & Me![cmbEmployee] & "' [COLOR="red"][B]AND[/B][/COLOR]"
   
    
    stDate = "[Date]=[COLOR="Red"][B]#[/B][/COLOR]" & Me.cboDate.Column(1) & "[B][COLOR="red"]#[/COLOR][/B]"
    
    DoCmd.OpenForm stDocName, , ,[COLOR="red"][B] stNameCriteria & stDate[/B][/COLOR]

Exit_cmdTime_Click:
    Exit Sub

Err_cmdTime_Click:
    MsgBox Err.Description
    Resume Exit_cmdTime_Click
    
End Sub

 

[DMerchen]

I changed my two comboboxes to be cascading to fine tune what the user could select, but now the filter does not work. I have the EmployeeID and the date, but for some reason it always gives me the open form action was cancelled. It seems like the variables are correct, but the form won't open.

Here is the code. Any reason why this won't work?

Private Sub cmdTime_Click()
On Error GoTo Err_cmdTime_Click

Dim stDocName As String
Dim stNameCriteria As String
Dim stDate As String

stDocName = "Timecard"

stNameCriteria = "[EmployeeID]=" & "'" & Me![cboEmployee] & "' AND"

stDate = "[Date]=#" & Me.cboDate.Column(1) & "#"

DoCmd.OpenForm stDocName, acNormal, , stNameCriteria & stDate

Exit_cmdTime_Click:
Exit Sub

Err_cmdTime_Click:
MsgBox Err.Description
Resume Exit_cmdTime_Click

End Sub

Any help is appreciated.

 

[DMerchen]

Got it just after sending the last message.