ComboBox selection controlling checkbox availability (1 Viewer)

[minkoffcpa]

I have a project database where I keep track of the work in progress on monthly bookkeeping for our clients. I have a form where I use a ComboBox to select the client and another ComboBox to select whether a sales tax report must be filed Monthly, Quarterly or Annually. I have 17 CheckBoxes one for every month, quarter and year-end. I want the selection in the sales tax reporting ComboBox to trigger which CheckBoxes are available to check. That is, if "Monthly" is selected in the ComboBox, then only the 12 monthly CheckBoxes are available to check (the 4 quarterly and 1 year-end CheckBoxes will be disabled). If "Quarterly" is selected in the ComboBox then only the 4 quarterly CheckBoxes are available to check. Of course, "Annual" is the third option. The disabling of CheckBoxes must be on a record by record basis. "Monthly" could be selected for one client (one record) and "Quarterly" for another client (another record). Thanks a lot, in advance.

 

[CJ_London]

Welcome to the forum.

You'll need to provide a bit more information:

1. is your form single or continuous?
2. when you say a combobox to select a client - does this populate the form with the client data - including whether they are monthly/quarterly etc? and the approriate values for the checkboxes?
3. Are your checkboxes appropriately named? ie.M1, M2, M3, Q1,Q2 etc so they can be easily parsed?

 

[minkoffcpa]

1. Continuous
2. No. I use the client combobox mearly to select the name. The Sales Tax combobox separately selects "Monthly", "Quarterly" or "Annually". That selection stays in Client Bookkeeping Checklist table and nowhere else.
3. The checkboxes are labeled - "01-JanST", "02-FebST", etc. "Q1ST", "Q2ST", etc. and finally "YEST."

 

[CJ_London]

If you are using a continuous form then you will need to use conditional formatting.

I note you have said the checkboxes are labeled with these names, I'm assuming this is the name of the controlsource for the checkbox - i.e. the checkbox is bound to the fields in the recordsource to the form.

Unfortunately, you can't conditionally format a checkbox. So what you need to do is replace each checkbox with a textbox using the same controlsource as the checkbox.

If you make the textbox one character wide, or a small square then conditionally format as follows (example is for monthly):

set backcolour and forecolor to red
rule1: if salestaxcombobox<>'monthly' then enabled =false
rule2: if controlvalue is true then backcolour and forecolor is green

You haven't said if you want to be able to 'tick/untick' these controls on this form but if you do then for each control click event put the following

Private Sub 01-JanST_Click()
 
    01-JanST=not 01-JanST
 
End Sub

 

[minkoffcpa]

The expression: if salestaxcombobox<>"monthly" then enabled =false, is not allowed in the Conditional Formatting. The closest thing I could come up with was:

Iif([salestaxcombobox]<>"monthly", "enabled"=False)

but that doesn't work. I can't figure out how to write the expression for changing the back color and fore color to green.

 

[pbaldy]

FYI, moved your thread from introductions to forms.

 

[minkoffcpa]

Sounds good to me... but then, what do I know?

 

[minkoffcpa]

Also, the code:
Private Sub 01-JanST_Click() 01-JanST=not 01-JanST End Sub
brings up the debugger when I click on the text box.

 

[pbaldy]

In case CJ has gone to bed, the expression would look like:

[salestaxcombobox]<>"monthly"

Conditional Formatting handles the "If" part.

 

[pbaldy]

The inadvisable symbol would require bracketing the field name, like:

Me.[01-JanST] = not Me.[01-JanST]

 

[minkoffcpa]

Thanks. What's the rest of the expression?

{salestaxcombobox]<>"Monthly", enabled =false (?)

 

[pbaldy]

There is no "rest of the expression". Setting the desired font or back color, or in your case the enabled property does that. If your expression is true, CF applies the desired formatting, if not it doesn't.

 

[minkoffcpa]

Okay; call me stupid. I'm getting an "Invalid Syntax" error message with the following expression:

[SalesTaxReportingPeriod]<>"Monthly" Me.[01-JanST] = not Me.[01-JanST]

Could you please just give me the entire expression as is should be written for both Rule 1 and Rule 2?

Thank you.

 

[pbaldy]

Where? The first part is intended for the Conditional Formatting expression, the second is VBA code in behind your textboxes that replace checkboxes. Start with just the first part in Conditional Formatting:

[SalesTaxReportingPeriod]<>"Monthly"

Get that working and then add the next rule.

 

[CJ_London]

You need to use conditional formatting, not vba code - see below

 

[minkoffcpa]

It works! Hooray for CJ_London. Wow, that was a long way to go to get the answer that worked. I hope it won't take that many posts next time.