Filter with like value from combo box

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

Guest

Hi,

Can someone tell me if there is a way to filter a subform with a combo box
to return all records that are "like" the selection chosen? When I click on
the record source of the subform, it brings me to the SQL Query grid, the
combo box is referenced in the related field. It will only show me records
for those that match exactly. I need a Like*.

Example: Select "Water" from combo. Need to see records with anything with
water, 25% water, water plus soap, etc.

Any help is appreciated,
Pam
 
PHIsaw,

The following assumes that:

- your combo box is on the main form
- its *value* (determined by the Bound Column) is a text string rather than
a numeric code)

In the combo box' AfterUpdate event procedure:

On Error Goto ErrHandler

Dim strSQL As String
strSQL = "SELECT YourTable.YourField FROM YourTable"
strSQL = strSQL & " WHERE (((YourTable.YourField)"
strSQL = strSQL & " Like '*" & Me![YourCombo] & "*'));"

With Me.YourSubform.Form
.RecordSource = strSQL
.Requery
End With

ErrExit:
Exit Sub

ErrHandler:
' Appropriate error handling here

Hope that helps.
Sprinks
 
Sprinks,

It works perfectly. I have one other question - can you tell me how to add
an "or" to the statement you gave below. I have two fields that I need to
match to the combo - (I know, not good db design, but for now, it has to be).

strSQL = strSQL & " WHERE (((YourTable.YourField)"
Thanks again for your help!
Pam

Sprinks said:
PHIsaw,

The following assumes that:

- your combo box is on the main form
- its *value* (determined by the Bound Column) is a text string rather than
a numeric code)

In the combo box' AfterUpdate event procedure:

On Error Goto ErrHandler

Dim strSQL As String
strSQL = "SELECT YourTable.YourField FROM YourTable"
strSQL = strSQL & " WHERE (((YourTable.YourField)"
strSQL = strSQL & " Like '*" & Me![YourCombo] & "*'));"

With Me.YourSubform.Form
.RecordSource = strSQL
.Requery
End With

ErrExit:
Exit Sub

ErrHandler:
' Appropriate error handling here

Hope that helps.
Sprinks

PHisaw said:
Hi,

Can someone tell me if there is a way to filter a subform with a combo box
to return all records that are "like" the selection chosen? When I click on
the record source of the subform, it brings me to the SQL Query grid, the
combo box is referenced in the related field. It will only show me records
for those that match exactly. I need a Like*.

Example: Select "Water" from combo. Need to see records with anything with
water, 25% water, water plus soap, etc.

Any help is appreciated,
Pam
Click to expand...
Click to expand...
 
Pam,

The following will do it. By the way, in my first post, I needlessly
limited the SELECT clause to one field. This should be the same fieldlist as
your default RecordSource of the subform.

The following supposes that you are selecting all fields in a table, so I've
used the TableName.* abbreviation. I also removed some unnecessary
parentheses.

Dim strSQL As String
strSQL = "SELECT YourTable.* FROM YourTable"
strSQL = strSQL & " WHERE (YourTable.YourField)"
strSQL = strSQL & " Like '*" & Me![YourComboBox] & "*'"
strSQL = strSQL & " OR (YourTable.SomeOtherField)"
strSQL = strSQL & " Like '*" & Me![YourComboBox] & "*'"

With Me.YourSubform.Form
.RecordSource = strSQL
.Requery
End With

Sprinks


PHisaw said:
Sprinks,

It works perfectly. I have one other question - can you tell me how to add
an "or" to the statement you gave below. I have two fields that I need to
match to the combo - (I know, not good db design, but for now, it has to be).

strSQL = strSQL & " WHERE (((YourTable.YourField)"
Thanks again for your help!
Pam

Sprinks said:
PHIsaw,

The following assumes that:

- your combo box is on the main form
- its *value* (determined by the Bound Column) is a text string rather than
a numeric code)

In the combo box' AfterUpdate event procedure:

On Error Goto ErrHandler

Dim strSQL As String
strSQL = "SELECT YourTable.YourField FROM YourTable"
strSQL = strSQL & " WHERE (((YourTable.YourField)"
strSQL = strSQL & " Like '*" & Me![YourCombo] & "*'));"

With Me.YourSubform.Form
.RecordSource = strSQL
.Requery
End With

ErrExit:
Exit Sub

ErrHandler:
' Appropriate error handling here

Hope that helps.
Sprinks

PHisaw said:
Hi,

Can someone tell me if there is a way to filter a subform with a combo box
to return all records that are "like" the selection chosen? When I click on
the record source of the subform, it brings me to the SQL Query grid, the
combo box is referenced in the related field. It will only show me records
for those that match exactly. I need a Like*.

Example: Select "Water" from combo. Need to see records with anything with
water, 25% water, water plus soap, etc.

Any help is appreciated,
Pam
Click to expand...
Click to expand...
Click to expand...
 
Sprinks,

PERFECT!!! I have spent sssooooo much time with this - changing code in
first one place then another. I was always so close but no match. I can't
thank you enough!!!

I figured out the Select clause with the one field when all the #Name?'s
popped up.

Thanks again,
Pam

Sprinks said:
Pam,

The following will do it. By the way, in my first post, I needlessly
limited the SELECT clause to one field. This should be the same fieldlist as
your default RecordSource of the subform.

The following supposes that you are selecting all fields in a table, so I've
used the TableName.* abbreviation. I also removed some unnecessary
parentheses.

Dim strSQL As String
strSQL = "SELECT YourTable.* FROM YourTable"
strSQL = strSQL & " WHERE (YourTable.YourField)"
strSQL = strSQL & " Like '*" & Me![YourComboBox] & "*'"
strSQL = strSQL & " OR (YourTable.SomeOtherField)"
strSQL = strSQL & " Like '*" & Me![YourComboBox] & "*'"

With Me.YourSubform.Form
.RecordSource = strSQL
.Requery
End With

Sprinks


PHisaw said:
Sprinks,

It works perfectly. I have one other question - can you tell me how to add
an "or" to the statement you gave below. I have two fields that I need to
match to the combo - (I know, not good db design, but for now, it has to be).

strSQL = strSQL & " WHERE (((YourTable.YourField)"
Thanks again for your help!
Pam

Sprinks said:
PHIsaw,

The following assumes that:

- your combo box is on the main form
- its *value* (determined by the Bound Column) is a text string rather than
a numeric code)

In the combo box' AfterUpdate event procedure:

On Error Goto ErrHandler

Dim strSQL As String
strSQL = "SELECT YourTable.YourField FROM YourTable"
strSQL = strSQL & " WHERE (((YourTable.YourField)"
strSQL = strSQL & " Like '*" & Me![YourCombo] & "*'));"

With Me.YourSubform.Form
.RecordSource = strSQL
.Requery
End With

ErrExit:
Exit Sub

ErrHandler:
' Appropriate error handling here

Hope that helps.
Sprinks

:

Hi,

Can someone tell me if there is a way to filter a subform with a combo box
to return all records that are "like" the selection chosen? When I click on
the record source of the subform, it brings me to the SQL Query grid, the
combo box is referenced in the related field. It will only show me records
for those that match exactly. I need a Like*.

Example: Select "Water" from combo. Need to see records with anything with
water, 25% water, water plus soap, etc.

Any help is appreciated,
Pam
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

First Subform record gets modified when closing form 3
filter query on combo box in a continuous subform 0
Combo Box Filter 1
Combo Box results to Subform 1
Synchronize combo boxes 2
Combo Boxes & Filtering Value 4
Filter tabular form by combo box 0
synchronized Combo Boxes in subforms 2

Back
Top