Check box Option Group 1

[homless]

I have an Access form that has 4 check boxes. I have 4 queries for each check box and a combo drop down list that displays products that are in my table tblproduct. The check boxes are named as follows:

InStockQ
OutOfStockQ
DiscontinuedItemsQ
All Products
The frame name for the grouped check box is called grpBox.
Now what I am looking for is a way to be able to select each checkbox and the query corresponding to the check box displays the products in the combo box. For example, if I check the OutOfStockQ checkbox, I want only the products that are Out of stock to populate the combo box. Is there a way a simple way to do this? I tried using the toggle buttons but I could only get two options to work that is why I switched to check box. I am open to whichever is easier.
Much thanks,
Liz

 

[dhookom]

How about sharing something about the table structure(s) of the Row Source of the combo box?

What are the values of each of the check boxes? Can we assume this is an option frame where only one box can be checked at a time?


Duane
Hook'D on Access
MS Access MVP

 

[randy700]

If your option group is set up properly, each option button (or check box) will have a value (0-3 or 1-4).
I'd use a SELECT CASE statement to determine which query to run. Something like this maybe?

SELECT CASE grpBox
   Case 1:
      'code to run 1st query and anything else needed
   Case 2:
      'code to run 2nd query
   Case 3:
      'code to run 3rd query
   Case 4:
      'code to run 4th query
End Select

Notice that the select statement is for the option group, not an individual button or check box.


Randy

 

[homless]

Thank you This is what I did and it appears to be working. Is there something else that I should add? Can I add Me.Requery after each case statement? What do you think? I want to be able to see new records that are added to the product table.

Private Sub grpBox_Click()
Select Case grpBox
Case 1
Me.Text2.RowSource = "SELECT qryInStock.ProductName FROM qryInStock
Case 2
Me.Text2.RowSource = "SELECT qryOutOfStock.ProductName FROM qryOutOfStock

Case 3
Me.Text2.RowSource = "SELECT qryDiscontinued.ProductName FROM qryDiscontinued
Case 4
Me.Text2.RowSource = "SELECT qryAllProducts.ProductName FROM qryAllProducts

End Select
End Sub

 

[homless]

Left out the quotation(") marks at the end of the queries on the last thread.
Case 1
Me.Text2.RowSource = "SELECT qryInStock.ProductName" FROM qryInStock
Case 2
Me.Text2.RowSource = "SELECT qryOutOfStock.ProductName FROM qryOutOfStock"
Case 3
Me.Text2.RowSource = "SELECT qryDiscontinued.ProductName" FROM qryDiscontinued"
Case 4
Me.Text2.RowSource = "SELECT qryAllProducts.ProductName FROM qryAllProducts

 

[randy700]

Why add the same code after every case statement?
Simply add it AFTER the End Select.

...
End Select
Me.Requery
End Sub

However, I don't think the requery is going to be necessary.
The DISPLAYED value won't update, so you might want to add some code to update the displayed value.


Randy

 

[dhookom]

I'm not sure why you have separate queries. Also, leaving the name of a combo box as Text2 is a sin ;-).

Dim strWhere as String
Select grpBox
   Case 1  ' in stock
       strWhere = "[StockStatus] = 'InStock' "
   Case 2  ' out of stock
       strWhere = "[StockStatus] = 'OutStock' "
   Case 3  ' Discontinued
       strWhere = "[StockStatus] = 'Discon' "
   Case 4  ' All
     strWhere = "[StockStatus] = [StockStatus] "
End Select
Me.cboProducts.RowSource = "SELECT fieldA, fieldB FROM qryProducts WHERE " & strWhere & " ORDER BY FieldB;"

Duane
Hook'D on Access
MS Access MVP