Thread: Determine if a combination of items were selected

1. Registered User
Join Date
Dec 2003
Posts
11

Unanswered: Determine if a combination of items were selected

I have a subform on a main form. The subform is viewed in Datasheet mode. You can create multiple records on the subform. Each record will have a detail item that reads either 'in-town' or 'out-of-town'.

This is what I want to do... I want a textbox on the main form that can determine what has been selected. If only 'in-town' items were selected the mainform text box should say 'in-town'. The same goes for 'out-of-town'. If there are both 'in-town' and 'out-of-town' items selected I want the textbox to read 'combined'.

Thank you. mpereziii

2. Purveyor of Discontent
Join Date
Mar 2003
Location
The Bottom of The Barrel
Posts
6,102
Hmmmmmm....

how about this... you could sum the checkboxes based on whether they say "in-town" or "out-of-town" and compare that sum to the count of records.

For instance, if you used:

=sum(iif([yourField] = "in-town", 1, 0))

And compared it to:

count([yourField])

you could find out whether it's all in/out town or mixed.

If the first value is 0, then it's all out of town.

If the second value does not equal the first value, then it's mixed

If the second value equals the first value, then it's all in town.

3. Registered User
Join Date
Dec 2003
Posts
11
Thanks Teddy. This did give me some information I was looking for. I'm able to determine by the calculations if the group is 'intown','out-of-town', or 'mixed'.

Now how can I get a new text box to understand the results we have come up with:
If value = 0 then out of town
If value is not equal to value 1 then mixed
if value = value 1 then in town

Is code or an expression required?
Thanks

4. Purveyor of Discontent
Join Date
Mar 2003
Location
The Bottom of The Barrel
Posts
6,102
Use an if statement to set your text field.

Code:
```Dim i As Integer
Dim frm As Form

Set frm = forms!yourMainForm

i = countField - sumField

If i = 0 Then         'countField = sumField so result is 0
frm!txtValue.value = "All in town"
ElseIf i = countField Then         ' sumField was 0
frm!txtValue.value = "All out of town"
Else
frm!txtValue.value = "mixed"
End If```

5. Registered User
Join Date
Dec 2003
Posts
11
This is what I have in the Before Update Event Procedure. My textbox, Text23, is remaining empty.

Location is the form name.
Do I need to make an adjustment to the frm!txtValue.Value?

Thanks,

Private Sub Text23_BeforeUpdate(Cancel As Integer)
Dim i As Integer
Dim frm As Form

Set frm = Forms!Location

i = Text19 - Text15

If i = 0 Then 'countField = sumField so result is 0
frm!txtValue.Value = "All in town"
ElseIf i = Text19 Then ' sumField was 0
frm!txtValue.Value = "All out of town"
Else
frm!txtValue.Value = "mixed"
End If
End Sub

6. Purveyor of Discontent
Join Date
Mar 2003
Location
The Bottom of The Barrel
Posts
6,102
I dont' know what your arbitrary textbox names mean.

In any event, it's probably not firing at all because I dont' know what object your hooking the event too.

Try commenting out the entire if statement and using MsgBox "Test" instead.

7. Registered User
Join Date
Dec 2003
Posts
11
I have the If Statement working properly. I did the MsgBox test and I found my problem. But there is a little more to this issue, perhaps you can shed some light on.

I have a main form, subform, and a subform to the subform. I can only get the if statement to work on the mainform. It works properly but only when the first record in the subform to the subform is selected. When I move to the 2nd record in the subform to the subform, the information for the if statement result box is reflecting the 1st record. My sum and count textboxes are changing to reflect the change in record but not my if statement result box. I have tied the event to - After update and On Open (of the main form) but it is not changing the outcome.

Thanks

8. Registered User
Join Date
Apr 2004
Location
Derbyshire, UK
Posts
805
Hi

Looking at the original post I think there is a simpler way of updating the unbound textbox on the main form, if all you want is the value/contents of that field.

Try entering this in the Form_Current event of the SUBFORM

Private Sub Form_Current()

Forms![YourMainFormName]![MainFormTextBoxName] =SubformFieldName

End Sub

Hope this is what you had in mind (it should also work on sub subforms.

MTB

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•