Results 1 to 8 of 8
  1. #1
    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. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    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.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    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. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    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
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    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. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    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.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    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. #8
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    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
  •