how can i search using a combo box?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

i am using a combo box with values and i want to be able to search on those
values eg say i choose cars from the combo box then i type Ford underneath
which will display all the Ford cars within the database. I know this
involves some programming which i can't do so can somebody help me please.
thankyou
 
From the sounds of things you want to be able to select a field in your
database in the combobox, type a value in an associated text box, click a
button and display all records that match that value.

Is this correct?

If so, it is actually fairly straight forward.

Say your form has 3 controls:
a combobox called ctlSearchField
a textbox called ctlSearchText
a command button called btnGO

To filter the form so it will only display records that match the criteria,
you should attach an event procedure to the btnGO object:

Private Sub btnGO_Click()
Dim strSearchField As String
Dim strSeachText As String

strSearchField = Me.ctlSearchField
strsearchtext = Me.ctlSearchText

If strSearchField = "" Or strsearchtext = "" Then Exit Sub

Me.Filter = "[" & strSearchField & "] = '" & strsearchtext & "'"
Me.FilterOn

End Sub

However, this will only filter the form, you will still have to scroll
through each record using form navigation.

An alternative to above would be to include a listbox which is populated
via your search.

In this instance, say your form has 4 controls:
a combobox called ctlSearchField
a textbox called ctlSearchText
a command button called btnGO
a listbox called ctlSearchResults

Private Sub btnGO_Click()
Dim strSearchField As String
Dim strSeachText As String
Dim strSearchSQL as string

strSearchField = Me.ctlSearchField
strsearchtext = Me.ctlSearchText

'If either search control is empty then exit
If strSearchField = "" Or strsearchtext = "" Then Exit Sub

'Build SQL string
strSearchSQL = "SELECT * FROM [INSERT TABLE NAME HERE] WHERE " &_
strSearchField & " = '" & strSearchText & "';"

'Assign SQL to listbox
Me.ctlSearchResults.RowSource = strSearchSQL

End Sub


Obviously, all of this is based on the basic information you gave, and I
might be barking up the completely wrong tree....

Hope that helps

John
 
i am using a combo box with values and i want to be able to search on those
values eg say i choose cars from the combo box then i type Ford underneath
which will display all the Ford cars within the database. I know this
involves some programming which i can't do so can somebody help me please.
thankyou
Click to expand...

No programming is necessary. Use a criterion on your query:

=[Forms]![NameOfYourForm]![NameOfTheCombo]


John W. Vinson[MVP]
 
Programming is needed - re-read his post:

"eg say i choose cars from the combo box then i type Ford underneath
which will display all the Ford cars"

Unless you know of a way to move the criteria from one field to another
automatically in the query designer - if there is a way I would very much
like to know.
 
Programming is needed - re-read his post:

"eg say i choose cars from the combo box then i type Ford underneath
which will display all the Ford cars"

Unless you know of a way to move the criteria from one field to another
automatically in the query designer - if there is a way I would very much
like to know.
Click to expand...

ok... one line of code then: requery the Form in the afterupdate event
of the combo box.

John W. Vinson[MVP]
 
"ok... one line of code then: requery the Form in the afterupdate event
of the combo box."

This doesn't do anything to solve the problem??

For example, say he wanted to change the controls to query for the
following (not all at once of course):

1. all cars that were Ford
2. all cars with 5 gears
3. all cars with 3 doors

How would he accomplish this task by simply placing the criteria in one
field in the query designer and re-querying the form?

Please do not take offence, but I am eager to see why my opinion differs so
much to that of a MVP - as it likely means I'm in the wrong somewhere.

Cheers

John
 
Hello, I am finding this all very complicated and confusing. I'm new to
building databases so half the things you say i don't understand. I'm
building my database for my A Level coursework because i'm at colledge. The
teachers won't help me at making this search engine which is the final thing
i have to do to finish my database. I was wondering if somebody could quickly
help me out by doing this search for me because i am depending on this to get
into university. Or if not can you explain how i've got to do this in an
easier way please.
Thankyou

Anton
 
From the sounds of things you want to be able to select a field in your
database in the combobox, type a value in an associated text box, click a
button and display all records that match that value.

*****And yes thats eactly what i would like my search to do*****

Anton
 
Back
Top