Help! Query by Form W/Checkboxes & Comboboxes Not Working

[slkasulke]

Hi,

Attached I have a database that I've been working on which has a form called "frmCriteriaSearch". It is based off of the qryCriteriaListBoxUpdate query. I am trying to get the listbox in the second tab of the results section to work. It queries fine for the checkboxes, but I cannot get the comboboxes to affect the query (unless a checkbox has already been selected)

Can somebody help me with this?

Thanks.

 

[CJ_London]

Qury is working fine so far as I can see - you just need to put

Me.Navigate_to_Product.Requery

in the Private Sub btnSearchAdvCrit_Click() procudure

Private Sub btnSearchAdvCrit_Click()
    ' UPDATE SEARCH RESULTS IN SUBFORM
   ' Me.subAdvProductSrch.Form.RecordSource = "SELECT * FROM qryAdvProductSrch " & BuildFilter
    Me.lstCrit.Requery
    Me.Navigate_to_Product.Requery
    
    'Me.subCrit.Requery
End Sub

 

[slkasulke]

Qury is working fine so far as I can see - you just need to put

Me.Navigate_to_Product.Requery

in the Private Sub btnSearchAdvCrit_Click() procudure

Private Sub btnSearchAdvCrit_Click()
    ' UPDATE SEARCH RESULTS IN SUBFORM
   ' Me.subAdvProductSrch.Form.RecordSource = "SELECT * FROM qryAdvProductSrch " & BuildFilter
    Me.lstCrit.Requery
    Me.Navigate_to_Product.Requery
    
    'Me.subCrit.Requery
End Sub

Wait, you have to requery the tabbed elements as well? I didn't know that. Learned something new...

It doesn't make much sense to me, though, since the checkbox results appear in the results listbox without refreshing the tab... I don't run Access 2010 on this computer, so I will have to wait until I get my work computer to see if it resolves the issue. HMM...

 

[slkasulke]

Qury is working fine so far as I can see - you just need to put

Me.Navigate_to_Product.Requery

in the Private Sub btnSearchAdvCrit_Click() procudure

Private Sub btnSearchAdvCrit_Click()
    ' UPDATE SEARCH RESULTS IN SUBFORM
   ' Me.subAdvProductSrch.Form.RecordSource = "SELECT * FROM qryAdvProductSrch " & BuildFilter
    Me.lstCrit.Requery
    Me.Navigate_to_Product.Requery
 
    'Me.subCrit.Requery
End Sub

This did not resolve the issue.

 

[CJ_London]

Misunderstood the problem, but think the attached is what you want.


Problems included needing to

• set the check boxes to TriState to allow a null value
• set the comboboxes to Null (not zero length string which prevents functions like isnull and nz from working)
• use of ands and ors
• removing the recordsource for the form - you were effectively modifying a product record

You still have some work to do - you need to change the allow zero length string property in your tables (text fields) from true to false (default) so nulls can be detected.
Note that this does not allow for Category =AB or Typeoftool=compliance

 

[slkasulke]

Misunderstood the problem, but think the attached is what you want.



Problems included needing to

• set the check boxes to TriState to allow a null value
• set the comboboxes to Null (not zero length string which prevents functions like isnull and nz from working)
• use of ands and ors
• removing the recordsource for the form - you were effectively modifying a product record

You still have some work to do - you need to change the allow zero length string property in your tables (text fields) from true to false (default) so nulls can be detected.
Note that this does not allow for Category =AB or Typeoftool=compliance

I could kiss you.

THANK YOU!

And also thank you for telling me what went wrong. I was able to duplicate the solution on the full-version of this database. You're an angel.

 

[CJ_London]

pleased to help