Displaying Checked Checkboxes on Form

[apunaha23]

I've found a couple solutions to achieve my goal, but cannot get any to actually work. Any help is appreciated.



Goal: I have a form i'm using to view summary data - we'll call this form1. On form1, the user will choose a record from table1 and see other fields from table1. In table2, I store information related to each table1 record using several checkbox fields. For each record in table1, I'd like to display the caption of each checked checkbox in table2 on form1.

Method 1 - DLookUp
I built a query (query1) that essentially sums the text of the checked fields (checkedfields1). Formatting of the text isn't real clean but it could get the job done. I set the text box control source to checkedfield1 from query1. The result appears to work a little bit, but i think what i'm missing is limiting the result to a single row that matches the current record on form1.

Method 2 - VB script
I'm a novice with VB but can figure it out here and there. I tried messing around with this, to no avail:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim topVal1 As Long
Dim leftVal0 As Long
Dim leftVal1 As Long

'Set first row values
topVal1 = 0
leftVal0 = 0
leftVal1 = 1500


'Loop through each control and determine visibility and position
Dim ctl As Control
Dim labelname As String
Dim lastitem As String
For Each ctl In Me.Controls
If ctl.ControlType = acCheckBox Then
labelname = "lbl" & ctl.Name 'Get label name from checkbox name (ensure labels are named lblcheckboxname)
If ctl.Value = True Then
'Show label and set position
Controls(labelname).Visible = False
lastitem = lastitem & Controls(labelname).Caption & ", "
Else
'Hide fields, position not important
Controls(labelname).Visible = False
End If
End If
Next ctl
If lastitem = "" Then
Me.txtallitems = " "
Me.txtallitems.Visible = True
Me.txtallitems.Left = leftVal1
Me.txtallitems.Top = topVal1
Else
lastitem = Left(lastitem, Len(lastitem) - 2)
Me.txtallitems = lastitem
Me.txtallitems.Visible = True
Me.txtallitems.Left = leftVal1
Me.txtallitems.Top = topVal1
End If

End Sub

[aytee111]

Can you provide some sample data? It isn't apparent what you are trying to display. Does table1 contain the summary data that you are displaying?

[June7]

And what goes wrong with the code? AFAIK, form does not have Format event. Also, programmatically setting control properties on form will show the same result for all records. So if form is in continuous or datasheet view, not practical.

Use of multiple yes/no fields sounds like a non-normalized data structure. What data do these fields represent?

How are table1 and table2 related? PK of table1 stored as FK in table2? If that is the case, then why would there be any issue for "limiting the result to a single row that matches the current record on form1"?

Provide examples of raw data and desired output. If you want to provide db for analysis, follow instructions at bottom of my post.

[apunaha23]

Not sure if this is helpful, but generically speaking, the data would look like this:

table1
fields: ID, ord_num
row1: 1, 100
row2: 2, 101
row3: 3, 102
row4: 4, 103

table2
fields: ID, ord_num, checkbox1, checkbox2, checkbox3, checkbox4
row1: 1, 100, yes, no, no, no
row2: 2, 101, no, yes, yes, no
row3: 3, 102, no, no, no, yes
row4: 4, 103, yes, no, no, yes


Relationships:
table1.ID relates one to many to table2.ord_num



form1
when viewing form1, data displayed should be the following:
(note - for the 'summary of checkboxes' - i'm currently trying to use a text box to list checked values. Ideally, I want to display the field caption for each checkbox)

ord_num 100
summary of checkboxes: checkbox1.caption

ord_num 101
summary of checkboxes: checkbox2.caption, checkbox3.caption

ord_num 102
summary of checkboxes: checkbox4.caption

ord_num 103
summary of checkboxes: checkbox1.caption, checkbox4.caption

[aytee111]

Still confused - that is a one-to-one relationship.

Regardless, you can have a form/subform arrangement whereby the user selects a record from table1 in the main form, then the subform will show those records from table2 which match in a continuous form. The checkbox captions can be the header label for the columns. You could hide those that are marked with No using conditional formatting.

[June7]

Conditional formatting can disable or change colors and text enhancement (bold, italic, underscore) of textbox and combobox but does not have option for hiding (Visible property).

I see 1-to-1 relationship as well, unless your example for Table2 is incomplete. If this is a 1-to-1, might as well combine tables.

Still don't know what data those checkboxes represent and I still suspect this is not normalized. I expect filtering on those multiple yes/no fields will be aggravating.