Solved Considering Multiple Criteria with Multiple Combo Boxes on an Unbounded Form (1 Viewer)

[mrk777]

Hi, I have a query that has the Rewards Details (qry_R&R) and I have created an Unbounded Form where I linked that query (qry_R&R).

Now I would like to filter the results based on Function/Department and then with the Team Member Name. I tried the query as mentioned in the below method:

I have the form design as following:

I have used the following event procedure on Combobox selections:

The problem is when I use the combo box for Function, it is filtering the results, but when I select the Team Member from another Combobox, it is not reflecting the changes. Don't know why the results are not populating.

Is there any chance that I can filter the results with AND and OR conditions, and if I would like to add 2 more combo boxes for FROM and TO date ranges Please help!

 

[June7]

Review http://allenbrowne.com/ser-62.html

Mixing AND and OR operators would be very complicated.

 

[Pat Hartman]

Your form is bound, not unbound.
1. What are all the Refresh and Requery commands for? That is NOT the correct way you save a record if that is what your intention is.
2. You don't seem to have RI defined on any of the relationships
3. When you reference a combo, you are referencing the bound field which is the ID field, NOT the text field. You will never get a match when you use the ID field as criteria for the text field.
4. You have at least one bad relationship where you are joining on data field to data field rather than on FK to PK.

 

[mrk777]

Review http://allenbrowne.com/ser-62.html

Mixing AND and OR operators would be very complicated.

I have applied the concepts from the link you have provided and that seems to work well, thank you!!

 

[mrk777]

Your form is bound, not unbound.
1. What are all the Refresh and Requery commands for? That is NOT the correct way you save a record if that is what your intention is.
2. You don't seem to have RI defined on any of the relationships
3. When you reference a combo, you are referencing the bound field which is the ID field, NOT the text field. You will never get a match when you use the ID field as criteria for the text field.
4. You have at least one bad relationship where you are joining on data field to data field rather than on FK to PK.

Hi @Pat Hartman, I'm at a beginner level and trying to learn deeper into the concepts. Sure, I would revamp the code and relationships wherever possible. Thank you!!

 

[Pat Hartman]

#4 will be much easier to fix now rather than later. Were you able to understand the link posted by June7?

 

[mrk777]

#4 will be much easier to fix now rather than later. Were you able to understand the link posted by June7?

Yes, I have used the Like operator for one field and the Nz function for one of the fields, which seems to be working fine!!

 

[Pat Hartman]

No, the fix is to store the PK as the FK and NOT join on data fields. Look at the other relationships where the joins are FK to PK.

 

[mrk777]

Understood!! I will try to change it accordingly, Thank you!!