Check box form problems

[cgmckenzie]

On a form i am going to have a check box for each U.S. state abbrevations. In my table i am going to have a field called StatesServiced. I would like it to work like this, Lets say you check the boxes for nc,sc,ga,fl, it shows in the field stateservice such, "NCSCGAFL". Remember each state is a seperate checkbox. This is a great website and thanks for all the help. Chris

 

[rich.barry]

Here’s a partial answer to the problem.

It requires the following form set-up:

The check boxes for the states are named ckStateXX, where XX is the 2 letter state abbreviation.
A textbox called StatesServiced that is bound to your data table.


You still need to sort out the events that drive the code. I haven't gone that far.

When entering a record, the Decode section of the code must run.

'Decode section
Dim ctl As Control
Dim l As Integer

'Set all state check boxes to False
For Each ctl In Forms!form1
If Left(ctl.Name, 7) = "ckState" Then ctl.Value = False
Next ctl

'then tick the correct boxes
For l = 1 To Len(Me!StatesServiced) - 1 Step 2
Forms!form1.Controls("ckState" & Mid(Me!StatesServiced, l, 2)).Value = True
Next l


When exiting the record, the Encode section of the code must run.

'Encode section

Dim ctl As Control
Dim StateStr As String

For Each ctl In Me
'If the control is one of the state check boxes
If Left(ctl.Name, 7) = "ckState" Then
'then add the last 2 letters to the StatesServiced string
If ctl.Value = True Then StateStr = StateStr & Right(ctl.Name, 2)
End If
Next ctl

'Before leaving the record, set the bound text box to the string generated
Me!StatesServiced = IIf(StateStr = "", Null, StateStr)



As for me, I’d just be making a table with a field for each state such that the check boxes are bound. Saves all the co-ordination hassles associated with this.