Multiple check boxes values to a textbox.

[David Bratby]

Hi,

Please can you help.

I have a form with multiple checkboxes on it. Is it possible to output these selections to a textbox on a report?

My form has an option Contains or Traces

Then a list of Allergens.

* Eggs, * Fish, * Nuts, * Peanuts, * Wheat.

If option 'Contains' is selected I need the textbox to fill out with "Contains - (selected allergens). Or, if the 'Traces' option is selected the textbox to fill out with "May contain traces of - (selected allergens).

Hope this makes sense.

Thanks,

Dave

 

[arnelgp]

to make it easier to code, i suggest renaming your checkbox to check1, check2, etc and the corresponding label of checkbox to labelcheck1, labelcheck2, etc.

then on your report OnLoad event (your form must not be closed for this to work):

Private sub form_load()
dim strAllergen as string
dim ctl as control
for each ctl in forms!yourFormName.Controls
if typeof ctl is checkbox then
if (ctl.value=true) then
if strAllergen = "" then strAllergen = "Contains - ("
strAllergen = strAllergen & Forms!yourFormName.Controls("label" & ctl.Name).Caption & ", "
end if
end if
next
if Len(strAllergen)>0 then
strAllergen = Left(strAllergen, Len(strAllergen)-2) & ")"
' assign this to your report textbox
Me.yourReportTextBox = strAllergen
End If
End Sub

 

[David Bratby]

Thanks for your reply.

Sorry I am having a bit of trouble getting this to work.

I have renamed my checkboxes and labels as suggested above.

I have then put the code in to my report and modified it to the relevant fields as below

Is this correct?

Private Sub form_load()
Dim strAllergen As String
Dim ctl As Control
For Each ctl In Forms!frmProductSpecs.Controls
If TypeOf ctl Is CheckBox Then
If (ctl.Value = True) Then
If strAllergen = "" Then strAllergen = "Contains - ("
strAllergen = strAllergen & Forms!frmProductSpecs.Controls("labelCheck1" & ctl.check1).Caption & ", "
End If
End If
Next
If Len(strAllergen) > 0 Then
strAllergen = Left(strAllergen, Len(strAllergen) - 2) & ")"
' assign this to your report textbox
Me.DisplayAllergens = strAllergen
End If
End Sub


Thanks Dave

 

[arnelgp]

your textbox DisplayAllergens in your report must be unbound.

 

[David Bratby]

Hi,

The textbox DisplayAllergens is unbound. I open the report from the form and it that text box is still blank.

Is the amendments I made to your code correct?

Thanks,

Dave

 

[arnelgp]

my mistake:

Private Sub form_load()

should be in your report:

Private Sub Report_Load()

 

[David Bratby]

Ok, I have made this change.

I get an error now.

run-time error '438':
Object doesn't support this property or method.

I click debug and the following line is highlighted yellow.

strAllergen = strAllergen & Forms!frmProductSpecs.Controls("labelCheck1" & ctl.Check1).Caption & ", "

Thanks again,

Dave

 

[arnelgp]

sorry for that, here:

strAllergen = strAllergen & Forms!frmProductSpecs.Controls("label" & ctl.Name).Caption & ", "

 

[David Bratby]

Hi,

Thanks for your help with this.

It was my fault not understanding how the code works.

I have this working now.

Only issue is that I have other check boxes on this form that I don't want to include. Is there some sort of exclusion I can do for those?

Thanks,

Dave

 

[arnelgp]

are those checkboxes permanently not included. if so:

Private sub form_load()
dim strAllergen as string
dim strExcluded As String
'----------------
' here you type the name of checkboxes you dont want included separated by ' comma
' replace yourCheckBoxName1, yourCheckBoxName2, etc with the
' correct name of your checkbox
strExcluded = "yourChecBoxName1,yourCheckBoxName2, etc"

dim ctl as control
for each ctl in forms!yourFormName.Controls
if typeof ctl is checkbox then
if (ctl.value=true) then
If Instr(strExcluded, ctl.Name) = 0 then
if strAllergen = "" then strAllergen = "Contains - ("
strAllergen = strAllergen & Forms!yourFormName.Controls("label" & ctl.Name).Caption & ", "
end if
End If
end if
next
if Len(strAllergen)>0 then
strAllergen = Left(strAllergen, Len(strAllergen)-2) & ")"
' assign this to your report textbox
Me.yourReportTextBox = strAllergen
End If
End Sub

 

[David Bratby]

I can't thank you enough for this. It is working perfectly.

 

[arnelgp]

your welcome sir!

 

[David Bratby]

Sorry, its me again.

Do you think it is possible to make this work so that I don't need to have the form open to run the report?

Thanks,

Dave

 

[David Bratby]

Hi Arnelgp,

I don't know if you saw my last reply?

I need to get this to work when I don't have the form open. How would I go about that do you think?

Thanks,

Dave

 

[arnelgp]

hello again, is your form bound, what are the table involved and the fieldname on your form? on the other hand, if it is possible for you to upload your db, and guide me as to which table are you working.

 

[David Bratby]

Hi,

Thanks for getting back to me.

I have attached a cut down version of the database.

On the form frmProductSpecs there is an Allergens tab.

And then in that Allergens tab you will see a bunch of check boxes.

Some of the check boxes are duplicated under Contains and Traces of.

These are the check boxes that I need to put in report.

If any of the check boxes under the heading Contains are ticked then i need the statement. "For allergens, see ingredients in bold Milk, Peanuts, etc"

and then if any of the check boxes under the heading Traces are ticked then i need "May contain, Sesame Seeds, Wheat".

And like I say I would like to be able to do this without opening the form and just printing the report.

Hope this makes sense.

Thanks again,

Dave

 

[arnelgp]

Mr. David, you may try it now.

 

[David Bratby]

Hi,

Thanks for looking at this.

It is still not putting the allergens in if I just run the report on its own?

Thanks,

Dave

 

[arnelgp]

ok, sorry about that:

 

[David Bratby]

Thats brilliant, thank you for your help!