Access 2007 using one combo box control for multiple data types?

Question

I need to add a basic record search feature on an Access 2007 form. I set up two ComboBoxes, the first to provide three different search options: search by reference, by date or by staff name. The AfterUpdate event on this ComboBox would then set the RowSource of the second ComboBox to show the user any available references, dates or staff names to allow them to pick the one they want. A Go button then opens the associated form with the record(s) which match their criteria.

All was going well at the start, working fine, but then a bug crept in. If I select to search by reference (long) or name (string) it works fine, but if I select to search by date, whilst the available dates are displayed correctly in the second combo, when I try to select one I get an error: the value entered isn't valid for this field. Is this because the control should only be used with a single data type, and my trying to use three different ones at different times is confusing things? I don't have any formats set in the control's properties.

Private Sub btnGo_Click()
    'go clicked so find request being searched for and open form at that request(s)
    If Not IsNull(Me.cboSearchOption) And Not IsNull(Me.cboSearchFor) Then
        'search variables entered so go to specified request
        Select Case [gblOption]
            Case "Reference"
                    DoCmd.OpenForm "frmRequestForInformation", , , "reference = " & Me.cboSearchFor.Value
            Case "Request date"
                    DoCmd.OpenForm "frmRequestForInformation", , , "date_request_received = #" & Me.cboSearchFor.Value  & "#"
            Case "Staff"
                    DoCmd.OpenForm "frmRequestForInformation", , , "signed_by = '" & Me.cboSearchFor.Value & "'"
        End Select
    Else
        MsgBox "Please ensure you have entered search values in both fields", vbOKOnly, "Unable to search"
    End If

End Sub

Note: gblOption contains the value of cboSearchOption and is set on its AfterUpdate event along with the RowSource for cboSearchFor.

Have also tried using a variant variable to hold the value from the combobox and pass that to the form. Now I'm finding that if I pass a date first it works OK, but then if I try to search for one of the other types it throws the same error. — Chelle
Testing with a simple form and a similar set-up, I do not get any problems. It is nothing to do with changing the datatype of the combo, that is handled by your delimiters. Have you tried stepping through the code to get the error line? — Fionnuala
I changed the code to pass in a variant variable and added Me.Repaint after the End Select and it's no longer throwing the error, however with the date search it is not always finding the record even though it's there so wondering if that's a problem with UK/US date format. The date showing in the combo is UK format OK, 15/10/2012, 02/11/2012 but the search is finding the entry for 15/10/2010 and not finding the entry for 02/11/2012! — Chelle
Access VBA requires unambiguous dates. It is nearly always best to format to year, month, day then you will not have locale issues. Where are you using the variant? It is not included in your sample code. — Fionnuala

serakfalcon serakfalcon · Accepted Answer · 2013-12-27T17:15:35

That sounds like the second combo box has a control source. Access is trying to set that source to a Date and isn't happy. You shouldn't need a control source for that combo box.

I have overloaded combo boxes with more than that without much problem, and the rest of your code looks OK.

Access 2007 using one combo box control for multiple data types?

1 Answers