Results 1 to 12 of 12
  1. #1
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Visible sub-form on condition (2003)

    I'd like to make a subform visible only when a particluar field has a specific value.

    What's the best way to do this?

    Cheers

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Visible sub-form on condition (2003)

    You need to wrtie code in two places: in the After Update event of the control bound to the field (so that visibility gets updated when the user enters/modifies the value), and in the On Current event of the form (so that visibility gets updated when the user moves to another record.

    Private Sub ControlName_AfterUpdate()
    Me.SubformName.Visible = (Me.ControlName = 37)
    End Sub

    Private Sub Form_Current()
    Me.SubformName.Visible = (Me.ControlName = 37)
    End Sub


    Notes:
    - You must substitute the appropriate names and value, of course.
    - You must use the name of the subform as a control on the main form, this is not necessarily the same as the name of the subform in the database window. To see the control name, open the main form in design view and click once on the subform. The control name will be displayed in the title bar of the Properties window.

  3. #3
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Visible sub-form on condition (2003)

    Hmm, didn't do anything.

    I checked the sub-form name was correct (on the title bar of the properties window).

    The relevant control is a combo box. Would that make any difference?


    Code here;
    (combo box)
    Private Sub CboDET_AfterUpdate()
    'makes the 'Tensile' subform visible if a T is put in this field
    Me.sfrmTens.Visible = (Me.CboDET = T)

    End Sub


    (form on current)
    Private Sub Form_Current()

    'Hides the 'Tensile' sub-form, unless combo box CboDET has a 'T' in it
    Me.sfrmTens.Visible = (Me.CboDET = T)

    End Sub

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Visible sub-form on condition (2003)

    Text values should be enclosed in quotes:
    <code>
    ... (Me.CboDET = "T")
    </code>
    If the Visual Basic Editor didn't complain about the use of T without quotes, you probably don't require explicit declaration of variables. See <post#=314,748>post 314,748</post#> for reasons why you should.

  5. #5
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Visible sub-form on condition (2003)

    That was the prob. Cheers Hans


    I'm beginning to think VBA si not for me; I don't use it enough to remember it.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Visible sub-form on condition (2003)

    You can always ask here <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  7. #7
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Visible sub-form on condition (2003)

    You're very kind. Thank you. I'll take you up on that offer, with the next step of this piece of development;

    The data in the sub-form needs to be updated from out AS/400 server when the combo box is changed to 'T'. And then have the last (related) record (specific field) as having the 'focus'.

    I've had a go, but no joy. Here's what I've put together (parent form = frmMain, sub-form = sfrmTens, form used while update performs = frmWait)

    Dim LngCurrRec As Long

    LngCurrRec = frm.CurrentRecord
    DoCmd.OpenForm "frmWait"
    DoCmd.Hourglass = True
    SetWarnings = False
    DoCmd.OpenQuery "aqryEICXREL0"
    SetWarnings = True
    DoCmd.Close acForm, "frmMain"
    DoCmd.OpenForm acForm, "frmMain"
    DoCmd.Close acForm, "frmWait"
    DoCmd.GoToRecord acDataForm, "frmMain", LngCurrRec
    sfrmTens.SetFocus
    'need to move to the last record in the sfrmTens, sub-form

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Visible sub-form on condition (2003)

    If you're updating the records in the subform, there doesn't seem to be a reason to close and reopen the main form. Try this:

    DoCmd.OpenForm "frmWait"
    DoCmd.Hourglass = True
    SetWarnings = False
    DoCmd.OpenQuery "aqryEICXREL0"
    SetWarnings = True
    DoCmd.Close acForm, "frmWait"
    Me.sfrmTens.Requery
    Me.sfrmTens.SetFocus
    RunCommand acCmdRecordsGoToLast

  9. #9
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Visible sub-form on condition (2003)

    Oh, I was thinking that requery wouldn't be effective with the sub-form open, hence why I chose that process.

  10. #10
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Visible sub-form on condition (2003)

    Hit a couple of snags;

    SetWarnings is not turning off append query message,
    DoCmd.Hourglass = True; get an error "...argument not optional" &
    "RecordsGoToLast ins't available now" message

    Could the last part (GoToLast) be down to the frmMain not receiving focus after the frmWait has closed.

    I did have Option Explicit, but that gave me another error on the SetWarnings;
    "Variable not defined"

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Visible sub-form on condition (2003)

    Sorry, I wasn't paying attention to details.

    1) Use

    DoCmd.SetWarnings False

    and

    DoCmd.SetWarnings True

    and also

    DoCmd.HourGlass True

    and

    DoCmd.HourGlass False

    i.e. no = in the instructions, and DoCmd. before SetWarnings.

    2) Try

    Me.SetFocus
    Me.sfrmTens.SetFocus
    RunCommand acCmdRecordsGoToLast

  12. #12
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Visible sub-form on condition (2003)

    Thank you once more, that's got what I need.

    Cheers <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

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