Sort & search from a Combo Box

[spacerocket]

I am doing a Contacts db. All contacts are grouped into their respective
category, ie. Supplier, Purchaser, etc. I would like to let user search for
Category from a combo box cbxSearchCat in a Search form (eg. only show
Supplier contacts).

Currently I can search from a text box, but I'm not sure how to search from
a combo box. The control source for this cbx is CategoryID. (both Contacts &
Category tables are linked via CategoryID). My search from text box code is
as below:

strWhere = "CompanyName LIKE '*" & txtSearchString & "*'"
Form_F_Contacts.RecordSource = "select * from Contacts where " & strWhere
DoCmd.Close acForm, "F_Search"

Thanks a lot for your advise.

 

[John Vinson]

I am doing a Contacts db. All contacts are grouped into their respective
category, ie. Supplier, Purchaser, etc. I would like to let user search for
Category from a combo box cbxSearchCat in a Search form (eg. only show
Supplier contacts).

Currently I can search from a text box, but I'm not sure how to search from
a combo box. The control source for this cbx is CategoryID. (both Contacts &
Category tables are linked via CategoryID). My search from text box code is
as below:

strWhere = "CompanyName LIKE '*" & txtSearchString & "*'"
Form_F_Contacts.RecordSource = "select * from Contacts where " & strWhere
DoCmd.Close acForm, "F_Search"

Thanks a lot for your advise.

It's simpler than that.

Just base your Report or Form on a query using

=[Forms]![F_Search]![cboCategoryID]

where cboCategoryID is the name of the combo box. F_Search must be
open for this to work - so don't close it; instead, put a command
button upon it to open the data-display form.

John W. Vinson[MVP]

 

[spacerocket]

Is it possible to put this code in the On Click event of the cmd button? I
ustd that this code is used for for form query, and tried to change it into
code for the cmd button but can't seem to get it right...

I am doing a Contacts db. All contacts are grouped into their respective
category, ie. Supplier, Purchaser, etc. I would like to let user search for
Category from a combo box cbxSearchCat in a Search form (eg. only show
Supplier contacts).

Currently I can search from a text box, but I'm not sure how to search from
a combo box. The control source for this cbx is CategoryID. (both Contacts &
Category tables are linked via CategoryID). My search from text box code is
as below:

strWhere = "CompanyName LIKE '*" & txtSearchString & "*'"
Form_F_Contacts.RecordSource = "select * from Contacts where " & strWhere
DoCmd.Close acForm, "F_Search"

Thanks a lot for your advise.

It's simpler than that.

Just base your Report or Form on a query using

=[Forms]![F_Search]![cboCategoryID]

where cboCategoryID is the name of the combo box. F_Search must be
open for this to work - so don't close it; instead, put a command
button upon it to open the data-display form.

John W. Vinson[MVP]

 

[spacerocket]

This is the code I used:
----------
strWhere = "ContactTypeID LIKE '*" & cboCType.Value & "*'"
If DCount("*", "Contacts", strWhere) < 1 Then
MsgBox "Search did not find any match."
Else
Form_F_Contacts_NoEdit.RecordSource = "select * from Contacts where " &
strWhere
DoCmd.Close acForm, "F_Search"
MsgBox "Search completed. Results matching search value is displayed."
----------

ContactType ID is the primary key in Table Contact_Type which links to
Table_Contact.
cboCType is the combo box in the search form F_Search for user to select the
category type.

Thanks again.

I am doing a Contacts db. All contacts are grouped into their respective
category, ie. Supplier, Purchaser, etc. I would like to let user search for
Category from a combo box cbxSearchCat in a Search form (eg. only show
Supplier contacts).

Currently I can search from a text box, but I'm not sure how to search from
a combo box. The control source for this cbx is CategoryID. (both Contacts &
Category tables are linked via CategoryID). My search from text box code is
as below:

strWhere = "CompanyName LIKE '*" & txtSearchString & "*'"
Form_F_Contacts.RecordSource = "select * from Contacts where " & strWhere
DoCmd.Close acForm, "F_Search"

Thanks a lot for your advise.

It's simpler than that.

Just base your Report or Form on a query using

=[Forms]![F_Search]![cboCategoryID]

where cboCategoryID is the name of the combo box. F_Search must be
open for this to work - so don't close it; instead, put a command
button upon it to open the data-display form.

John W. Vinson[MVP]

 

[John Vinson]

This is the code I used:
----------
strWhere = "ContactTypeID LIKE '*" & cboCType.Value & "*'"
If DCount("*", "Contacts", strWhere) < 1 Then
MsgBox "Search did not find any match."
Else
Form_F_Contacts_NoEdit.RecordSource = "select * from Contacts where " &
strWhere
DoCmd.Close acForm, "F_Search"
MsgBox "Search completed. Results matching search value is displayed."
----------

ContactType ID is the primary key in Table Contact_Type which links to
Table_Contact.
cboCType is the combo box in the search form F_Search for user to select the
category type.

I'm guessing you're yet another victim of the misbegotten Lookup
Wizard. Is ContactTypeID a Lookup field in your table?

If so, it contains a numeric ID value; that simple fact is concealed
from your view by the Wizard. The above code will work if
ContactTypeID is a Text field containing (anywhere within it) the text
string in cboCType.

What IS the datatype of ContactTypeID? What is the Control Source of
cboCType, and/or its RowSource and Bound Column? What is the datatype
of THAT field? They need to match; if they're both numeric, you don't
need the LIKE nor the quotes or asterisks, just

strWhere = "ContactTypeID = " & Me!cboCType

John W. Vinson[MVP]


John W. Vinson[MVP]

 

[spacerocket]

ContactTypeID is Autonumber in Table CType.
cboCType is Unbound, the Bound Column is 1 and Row Source is:

SELECT DISTINCTROW CType.* FROM CType ORDER BY CType.ContactType;

I tried the code you suggested, but it still doesn't work..

In the form F_Contacts_NoEdit where the search is supposed to look for data,
ContactTypeID is also a combobox. Which means that in this form and the
Search form where user selects the CType to search, both are combobox.

Pls. advise.. Appreciate your help very much!

 

[John Vinson]

ContactTypeID is Autonumber in Table CType.
cboCType is Unbound, the Bound Column is 1 and Row Source is:

SELECT DISTINCTROW CType.* FROM CType ORDER BY CType.ContactType;

I tried the code you suggested, but it still doesn't work..

In the form F_Contacts_NoEdit where the search is supposed to look for data,
ContactTypeID is also a combobox. Which means that in this form and the
Search form where user selects the CType to search, both are combobox.

Pls. advise.. Appreciate your help very much!

We're not communicating... probably my fault, I'm feeling fuzzy.

It sounds like you're considering a Combobox as a type of data. It
isn't. A combo box is a display tool. Data is not stored as a combo
box; it's stored as a Long Integer or as Text or whatever, and that
might be displayed on a Form (or even a datasheet) *USING* a combo
box, or a textbox, or a listbox or some other tool.

Your combo boxes have a Row Source property, the query which provides
them with the data to be displayed; and a Bound Column property which
specifies which field in that query provides the "value" of the combo.
These will need to match if you're using the combo for searching.

I'd avoid using the SELECT * option for a combo box, if for no other
reason than that it makes documentation harder: you can't see what
fields are being selected nor what order. So I cannot tell what value
might be returned by the SELECT DISTINCTROW above. What should work
with my code is to have the numeric ID field returned.


John W. Vinson[MVP]

 

[spacerocket]

Does that mean that it is not possible to use a cbx (eg. cbx A) in a form
(eg. Form A) to search in a cbx (eg. cbx B) displayed in another form (eg.
Form B)? Assuming that in Row Source, the SELECT DISTINCTROW contains the
same ID field and NAME field?

 

[spacerocket]

Finally I managed to solve this problem. I used the Combo Box wizard to help
me with the search and decided to ditch the Search Form. However, still need
your help. How do I 'hide' all the records that does not match the value in
the combo box? The code is as below:

Private Sub SortBy_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ContactTypeID] = " & Str(Nz(Me![SortBy], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Thanks again

 

[John Vinson]

Does that mean that it is not possible to use a cbx (eg. cbx A) in a form
(eg. Form A) to search in a cbx (eg. cbx B) displayed in another form (eg.
Form B)? Assuming that in Row Source, the SELECT DISTINCTROW contains the
same ID field and NAME field?

Again...

You're treating the combo box as if it were a table.

IT IS NOT A TABLE.
IT DOES NOT CONTAIN DATA.

It's a *display tool*, and it *displays* data which resides in a
Table. If you want to search for anything, search in the Table.

You can change the RowSource of one combo on the basis of a value
selected in another combo, but I don't think that's what you're
getting at.

I'll check your other reply now...

John W. Vinson[MVP]

 

[John Vinson]

Finally I managed to solve this problem. I used the Combo Box wizard to help
me with the search and decided to ditch the Search Form. However, still need
your help. How do I 'hide' all the records that does not match the value in
the combo box? The code is as below:

Private Sub SortBy_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ContactTypeID] = " & Str(Nz(Me![SortBy], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

I'm sorry, I simply don't understand what you're trying to accomplish.

Stop, step back. Rather than telling me about "searching a combo box
on another form", please tell me:

1. What tables do you have?
2. What do you want to accomplish?

The code above finds the first record on a Form which matches the
value of the combo box SortBy. This is itself confusing: the term
"Sort" means "To put a set of records in a particular order, e.g.
alphabetical order, earliest to latest, ascending or descending
numerical order".

It appears that you are using the term "sort" in some other way -
perhaps you mean you want to FILTER a form, so that the Form displays
a particular subset of the records?

If so, and if you want to have the Form display just those records for
the selected ContactTypeID, use

Private Sub SortBy_AfterUpdate()
Me.Filter = "[ContactTypeID] = " & Me!SortBy
Me.FilterOn = True
End Sub


John W. Vinson[MVP]

 

[spacerocket]

You are right, I'm using the wrong word. It should be "Filter" instead of
"Sort. Thanks, using the code you provided solved my problem. I never thought
it'd be so easy!

Finally I managed to solve this problem. I used the Combo Box wizard to help
me with the search and decided to ditch the Search Form. However, still need
your help. How do I 'hide' all the records that does not match the value in
the combo box? The code is as below:

Private Sub SortBy_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ContactTypeID] = " & Str(Nz(Me![SortBy], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

I'm sorry, I simply don't understand what you're trying to accomplish.

Stop, step back. Rather than telling me about "searching a combo box
on another form", please tell me:

1. What tables do you have?
2. What do you want to accomplish?

The code above finds the first record on a Form which matches the
value of the combo box SortBy. This is itself confusing: the term
"Sort" means "To put a set of records in a particular order, e.g.
alphabetical order, earliest to latest, ascending or descending
numerical order".

It appears that you are using the term "sort" in some other way -
perhaps you mean you want to FILTER a form, so that the Form displays
a particular subset of the records?

If so, and if you want to have the Form display just those records for
the selected ContactTypeID, use

Private Sub SortBy_AfterUpdate()
Me.Filter = "[ContactTypeID] = " & Me!SortBy
Me.FilterOn = True
End Sub


John W. Vinson[MVP]