Filter with like value from combo box

P

PHisaw

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
 
S

Sprinks

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
 
P

PHisaw

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...
 
S

Sprinks

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...
 
P

PHisaw

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...
 
Top