Results 1 to 5 of 5
  1. #1
    dniezby is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Apr 2013
    Posts
    183

    Making columns in a sub form visible true / false based on main form selection

    Okay, should be simple I think. I know I'm just missing something simple.

    I have a form, "events" with a subform "event standings". For regular events, all of the columns are working fine and the form does what it is supposed to do. However, in an effort to cut down making more forms for pretty much the same thing, I want to have my user select the type of event, then turn columns on / off based on the type of event.

    Here is what I've done so far.
    Code:
    Private Sub EventStandings_Enter()
    'When user begins working in the subform
    'Check to see if event is Sit N' Go Event
    'Declare the Variable
    Dim VarET As Variant
    'Lookup the type of event selected from the event's table.
    VarET = DLookup("event_type", "events", "event_id =" & event_id)
    'If event selected is a Sit N Go
    If VarET = "Sit n' Go" Then
            'Launch warning message.
            Dim ButtonClicked 'Declare button variable
            ButtonClicked = MsgBox("Did you want this to be a Sit N' Go Event?", vbYesNo)
                 If ButtonClicked = vbNo Then event_type.Undo 'If selected no, undo the event type and reselect.
                    MsgBox ("Select the appropriate event type")
    
                 Else
    
    '            'Hide columns not needed for this type of event
    '            Form_EventStandings.finish_points.Visible = False
    '
    '            'Check to see if member has enough points
    '            'do a Dsum here to get the player's running total and see if they have enough points to join event.
    '             End If
    '
    
    End Sub
    I'm having two issues here.
    1. When the message box opens; no matter which choice I make, it gives me the "select appropriate event" msgbox.
    2. Can't for the life of me get it to hide columns and unhide ones that I would want.


    3. I have a few IF statements that I will need to process here. Should I be enclosing them or something

    For reference, I want to hide a column called, "finish_points" which is in the subform, EventStandings

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    The problem is with the If ButtonClicked line. This is a single line If Then. Need to put event_type.Undo on a separate line below the If Then. Then you will need another End If to go with the If VarET.

    Step debug and you will see how the MsgBox is part of the If VarET outer conditional, not the If ButtonClicked inner.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    dniezby is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Apr 2013
    Posts
    183
    Quote Originally Posted by June7 View Post
    The problem is with the If ButtonClicked line. This is a single line If Then. Need to put event_type.Undo on a separate line below the If Then. Then you will need another End If to go with the If VarET.

    Step debug and you will see how the MsgBox is part of the If VarET outer conditional, not the If ButtonClicked inner.
    Thanks for this. It fixed my response from the message box but it still doesn't hide columns that I need to hide in the subform.

    How do I reference a column in the subform that I want to hide or change visibility???

  4. #4
    dniezby is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Apr 2013
    Posts
    183
    Well, I couldn't figure out how to hide columns in my subform so I duplicated the subform, gave it a different name and just hid the form and activated the form as needed.

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    I've never had to do this with Datasheet view. I hide/disable controls in Single view. Maybe this will help http://msdn.microsoft.com/en-us/libr...ffice.11).aspx
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 12
    Last Post: 02-10-2013, 10:13 AM
  2. Multiple True/False columns, forcing behaviour
    By RoscreaMou in forum Access
    Replies: 8
    Last Post: 05-08-2012, 04:24 PM
  3. Replies: 6
    Last Post: 09-27-2011, 04:39 PM
  4. Replies: 6
    Last Post: 02-24-2011, 03:24 PM
  5. Replies: 0
    Last Post: 08-24-2010, 06:38 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums