Please HELP - Trouble with Check Boxes

[helloworld]

Hi there,

I am having a lot of trouble with checkboxes. I have a form that will generate a report based on whatever the user selects out of a combobox (this works). However, the user can also select 5 checkboxes that will either select all the records or one of the records or two of the records etc.

For example:

All[] Pepperoni[] Mushrooms[] Onions[] Cheese[]

The user could select All toppings or Mushrooms or Mushrooms and Onions etc. and then click a button where a report generates based on the toppings selected.

In my report query, there is a field (toppings) that list the toppings for each pizza. There is only one topping in each entry.

Can someone please help me do this?

 

[RichO]

So is there only one topping per record in your table, or are there multiple topping fields per record? In other words, when there is more than one topping per pizza, how is that information stored?

I have some ideas on how you might do this but it would help to know what your table structure is first.

 

[helloworld]

Hi there,

Thanks for responding. There is only one value in the field toppings (Ex: pepperoni, onions etc). What I want is for the end-user to use the simple user-interface (form) and develop a report on a particular month of buisness. the user will also be able to create a report based on one or more toppings if he or she wished (for example: a report for only the records that have the topping pepperoni OR a report for only the records that have the toppings pepperoni and cheese). I have built a query that shows all the fields I need so basically whatever check box is checked, I need that info to be passed as a parameter into the query. This is my code so far:

Private Sub gen_report_Click()
On Error GoTo Err_gen_report_Click

Dim stDocName As String
Dim criteria As String


If Forms!SubmissionReport![Check59] = True Then
criteria = "pepperoni"
End If

If Forms!SubmissionReport![Check52] = True Then
criteria = "*" ' Selects all the toppings
End If

If Forms!SubmissionReport![Check55] = True Then
criteria = "ham"
End If

If Forms!SubmissionReport![Check57] = True Then
criteria = "Cheese"
End If

If Forms!SubmissionReport![Check61] = True Then
criteria = "Onion"
End If

stDocName = Forms!SubmissionReport![Combo50] ' select a report from the combo-box

DoCmd.OpenReport stDocName, acPreview, , "[Toppings] like '" & criteria & "'"


Exit_gen_report_Click:
Exit Sub

Err_gen_report_Click:
MsgBox Err.Description
Resume Exit_gen_report_Click

End Sub

 

[RichO]

So what I gather is that you want the user to be able to select more than one topping on the form and have the report print all records that contain any of the selected toppings?

And this is where your question comes in because currently your code is set up for only a single topping in the criteria?

As it stands, does your code work for just the single topping search?

Have you thought about using a multi-select list box for these toppings? It would give you more flexibility to add toppings to your list in the future without having to make any alterations to your code, and you could use a simple For...Next loop to generate your criteria string.

 

[helloworld]

the answers to your question are:

Yes, you understand what I am trying to say.
Yes, that is where my question comes from.
Yes, the code does work for one topping
Yes, I thought about a multi-select list box, but that is not the way the end user wants the form to look like.

Do you have any suggestions on how I could modify the code to make it work for a combination of checkboxes? I was thinking of concatinating a couple of strings but I do not know how I would do it!

 

[RichO]

You'll need to concatenate your criteria string to look something like:

[Toppings] Like 'Sausage' Or [Toppings] Like 'Cheese'

Dim stDocName As String
Dim criteria As String
Dim str As String   

str = "[Toppings] Like "

If Forms!SubmissionReport![Check59] = True Then
     criteria = criteria & str & "'Pepperoni' Or "
End If
If Forms!SubmissionReport![Check55] = True Then
     criteria = criteria & str & "'Ham' Or "
End If
If Forms!SubmissionReport![Check57] = True Then
     criteria = criteria & str & "'Cheese' Or "
End If
If Forms!SubmissionReport![Check61] = True Then
     criteria = criteria & str & "'Onion' Or "
End If
If Forms!SubmissionReport![Check52] = True Then
     criteria = ""    [COLOR=DarkOliveGreen]' Selects all toppings - no criteria need be specified[/COLOR]
End If

If criteria <> "" Then criteria = Left(Criteria, Len(Criteria) - 4) [COLOR=DarkOliveGreen]' parse the final " Or " off the end of the string[/COLOR]
stDocName = Forms!SubmissionReport![Combo50] 
DoCmd.OpenReport stDocName, acPreview, , criteria

See how that works for you

 

[helloworld]

Richo buddy...

You are a genuis! thanks a lot! I spent soooo much time on this stupid thing. I sincerely appreciate this!