469,658 Members | 1,872 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,658 developers. It's quick & easy.

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


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.

Expand|Select|Wrap|Line Numbers
  1.  Dim strStudentRef As String
  2.         Dim strSearch As String
  3.        Dim strSql As String
  5.     'Check Search Form for Null value or Nill Entry first.
  6.         If IsNull(Me![cmboOB]) Or (Me![cmboOB]) = Null Then
  7.         If IsNull(Me![cmboCN]) Or (Me![cmboCN]) = Null Then
  8.             MsgBox "Nothing Entered!", vbOKOnly, "Blank!"
  9.          Me![cmboOB].SetFocus
  10.      Exit Sub
  11.    End If
  12.    End If
  13.    '---------------------------------------------------------------
  15.    'Performs the search using value entered into txtSearch
  16.    'and evaluates this against values in Address
  18.        DoCmd.ShowAllRecords
  19.                                                  'Chr(42)is ascii code for the wildcard symbol
  20.        strSql = "Select * from [tblDocument] Where |related field for cmboOB| & |related field for cmboCN| Like '" & Chr(42) & Me!cmboOB & Me!cmboCN & Chr(42) & "';"
  21.        Me.RecordSource = strSql
  23.        'If one or more matching records are found, they can be displayed one at a time via the navigation buttons
  24.        If Me.Recordset.RecordCount > 0 Then
  26.            cmboOB.SetFocus
  27.            strSearch = cmboOB.Text
  29.            cmboOB.SetFocus
  30.            strSearch = cmboOB & Space(1) & cmboCN
  31.            MsgBox "Match Found For: " & strSearch & " ", _
  32.                , "Found!"
  33.                cmboOB.SetFocus
  34.            cmboOB = Null
  35.            cmboCN = Null
  38.        'If value not found sets focus back to txtSearch and shows msgbox
  39.        Else
  40.            cmboOB.SetFocus
  41.            strSearch = cmboOB & Space(1) & cmboCN
  42.            MsgBox "Match Not Found For: " & strSearch & " - Please Try Again.", _
  43.                , "Sorry"
  44.                cmboOB.SetFocus
  45.        End If
  46. End Sub
Apr 5 '10 #1
6 3345
1,134 Expert 1GB
what was the error and which line does it occur on?
Apr 6 '10 #2
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
Apr 6 '10 #3
1,134 Expert 1GB
Are the combo boxes bound to a table or query?
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.
Apr 6 '10 #4
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?
Apr 7 '10 #5
1,134 Expert 1GB
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.
Apr 8 '10 #6
There are spaces at the end of both sets of numbers.

I'll try your suggestion on clearing the fields but they look correct.
Apr 8 '10 #7

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

7 posts views Thread by Ceebaby via AccessMonster.com | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.