How to create a search form using combo box's, txt box's, and view report

[mercout]

Hey,

I've been trying to create a search form in access for a while now, searching through books and emails. I have the search form set up with 11 combo box's, 3 text box's, a view button, and a clear button. Once the user enters certain fields, all of the boxes do not have to be filled and clicks the view button, a report will pop up with detail of what was found.I found this code (http://bytes.com/topic/access/answers/879487-question-about-form) and modified it for my needs. I tested the code and i got it to work for 2 text boxes, when i changed it to combo boxes, i received an error.

So, my question is, how to i complete what needs to be done. I know I'm at the beginning but i need to get this done.

 Dim strStudentRef As String
        Dim strSearch As String
       Dim strSql As String
 
    'Check Search Form for Null value or Nill Entry first.
        If IsNull(Me![cmboOB]) Or (Me![cmboOB]) = Null Then
        If IsNull(Me![cmboCN]) Or (Me![cmboCN]) = Null Then
            MsgBox "Nothing Entered!", vbOKOnly, "Blank!"
         Me![cmboOB].SetFocus
     Exit Sub
   End If
   End If
   '---------------------------------------------------------------
 
   'Performs the search using value entered into txtSearch
   'and evaluates this against values in Address
 
       DoCmd.ShowAllRecords
                                                 'Chr(42)is ascii code for the wildcard symbol
       strSql = "Select * from [tblDocument] Where |related field for cmboOB| & |related field for cmboCN| Like '" & Chr(42) & Me!cmboOB & Me!cmboCN & Chr(42) & "';"
       Me.RecordSource = strSql
 
       'If one or more matching records are found, they can be displayed one at a time via the navigation buttons
       If Me.Recordset.RecordCount > 0 Then
 
           cmboOB.SetFocus
           strSearch = cmboOB.Text
 
           cmboOB.SetFocus
           strSearch = cmboOB & Space(1) & cmboCN
           MsgBox "Match Found For: " & strSearch & " ", _
               , "Found!"
               cmboOB.SetFocus
           cmboOB = Null
           cmboCN = Null
 
 
       'If value not found sets focus back to txtSearch and shows msgbox
       Else
           cmboOB.SetFocus
           strSearch = cmboOB & Space(1) & cmboCN
           MsgBox "Match Not Found For: " & strSearch & " - Please Try Again.", _
               , "Sorry"
               cmboOB.SetFocus
       End If
End Sub
 
 
 
 
 

Thanks

[Delerna]

what was the error and which line does it occur on?

[mercout]

The error is "The text you entered isn't an item in the list" for both combo boxes. The error doesn't take me to a line of code

[Delerna]

Are the combo boxes bound to a table or query?
or
did you manually enter a list of values when you created the combo box?

When you drop down a combo box you see a list of values from which you can select.
Alternatively you can type a value into the text section of the combo box but the value you type must exist in the list of values that you see when you drop it down.

Thats what the error is telling you. The value you entered isn't in the list.
I'm guessing that when you drop the combobox down the list is empty
because you haven't given it one and that's why it's not working.

[mercout]

Thanks, bound two of the 11 combo boxes to test it. The search worked but a pop up said "match not found for: 58 74 - Please try again"

Why am i getting that pop-up if the info is in my table?

[Delerna]

First thing I ask in that situation is

Are there spaces on the end of one and not on the end of the other
For Example
"58 74" will not match "58 74 "
even though they look the same (when you remove the quotes)


Next thing
Data in a field in access can sometimes (somehow ????) contain erroneous carriage returns
Try going to that record in the table and clear that field by pressing delete and backspace numerous times. Then re-enter the data.

[mercout]

There are spaces at the end of both sets of numbers.

I'll try your suggestion on clearing the fields but they look correct.