Filtering Question -

[Guest]

Hello,

I have a search form where I have multiple fields for selecting the
criteria, then a corresponding field for operand (=,<, etc) and then a field
for value. What I would like to do is to limit the value field to the
corresponding field in the database. For the criteria, I'm using a unbound
txt box that is retrieving a field list from a table. I can type in the
value and the search is successful. However what I'd like to do is to limit
the search based on the contents of the field.

For instance if the user selected LASTNAME for criteria 1, I'd like the
value for that criteria to be limited to the contents of the LASTNAME field.

I understand how to do a lookup if the field is static, but not sure how to
code it if it is dynamic. I'm trying to do something like:

txtCrit1 is the first criteria field
txtVal1 is the value field

If txtCrit1 = 'lastname' then txtVal1 = 'select lastname from tblclients'
Case if txtCrit1 = 'firstname' then txtVal1 = 'select firstname from
tblclients'
Case if txtCrit1 = 'city' then txtVal1 = 'select city from tblclients'

End if

I tried creating an event procedure on the OnClick property and also tried
writing a query to read the Criteria field and then select from the base
table.

Please help point me in the right direction. thanks much.

Jen

 

[Marshall Barton]

I have a search form where I have multiple fields for selecting the
criteria, then a corresponding field for operand (=,<, etc) and then a field
for value. What I would like to do is to limit the value field to the
corresponding field in the database. For the criteria, I'm using a unbound
txt box that is retrieving a field list from a table. I can type in the
value and the search is successful. However what I'd like to do is to limit
the search based on the contents of the field.

For instance if the user selected LASTNAME for criteria 1, I'd like the
value for that criteria to be limited to the contents of the LASTNAME field.

I understand how to do a lookup if the field is static, but not sure how to
code it if it is dynamic. I'm trying to do something like:

txtCrit1 is the first criteria field
txtVal1 is the value field

If txtCrit1 = 'lastname' then txtVal1 = 'select lastname from tblclients'
Case if txtCrit1 = 'firstname' then txtVal1 = 'select firstname from
tblclients'
Case if txtCrit1 = 'city' then txtVal1 = 'select city from tblclients'

End if

I tried creating an event procedure on the OnClick property and also tried
writing a query to read the Criteria field and then select from the base
table.

The usual way to do this kind of thing is to use a combo box
for the value. Then the AfterUpdate event for the field
combo box would just set the value combo box's RowSource:

Me.cboVal1.RowSource = "SELECT DISTINCT [" _
& Me.cboCrit1 & "] "_
& "FROM table " _
& "ORDER BY [" & Me.cboCrit1 & "]"

 

[Guest]

That was exactly what I needed. Thank you very much!

Jen

I have a search form where I have multiple fields for selecting the
criteria, then a corresponding field for operand (=,<, etc) and then a field
for value. What I would like to do is to limit the value field to the
corresponding field in the database. For the criteria, I'm using a unbound
txt box that is retrieving a field list from a table. I can type in the
value and the search is successful. However what I'd like to do is to limit
the search based on the contents of the field.

For instance if the user selected LASTNAME for criteria 1, I'd like the
value for that criteria to be limited to the contents of the LASTNAME field.

I understand how to do a lookup if the field is static, but not sure how to
code it if it is dynamic. I'm trying to do something like:

txtCrit1 is the first criteria field
txtVal1 is the value field

If txtCrit1 = 'lastname' then txtVal1 = 'select lastname from tblclients'
Case if txtCrit1 = 'firstname' then txtVal1 = 'select firstname from
tblclients'
Case if txtCrit1 = 'city' then txtVal1 = 'select city from tblclients'

End if

I tried creating an event procedure on the OnClick property and also tried
writing a query to read the Criteria field and then select from the base
table.

The usual way to do this kind of thing is to use a combo box
for the value. Then the AfterUpdate event for the field
combo box would just set the value combo box's RowSource:

Me.cboVal1.RowSource = "SELECT DISTINCT [" _
& Me.cboCrit1 & "] "_
& "FROM table " _
& "ORDER BY [" & Me.cboCrit1 & "]"