Results 1 to 7 of 7

Access 2010 Navigation form

This is a discussion on Access 2010 Navigation form within the Microsoft Access forums, part of the Question Forums category; I have a main Navigation form and within it, on one of the tabs, I have a subform that has ...

  1. #1
    MrExcel MVP
    Moderator
    Smitty's Avatar
    Join Date
    May 2003
    Location
    Crested Butte, CO
    Posts
    27,170

    Default Access 2010 Navigation form

    I have a main Navigation form and within it, on one of the tabs, I have a subform that has a detail view on it, then a subform on that one with a datasheet view for line items. I’m trying to get a field on the datasheet to toggle visibility depending on an option button selection on SubForm 1. I am using Master/Child links between the two forms.

    Here’s what I’ve been trying with no success (the controls that aren’t commented are on subform1 and work fine):

    Private Sub fra_SD_Dest_Click()
    '   Single Location selected
        '   Display Destination Type
        '   Enable ShipTo Location on Detail form
        '   Disable ShipTo location on LineItem form
        
        With Me
        '   Ship to a Single Location
            If .fra_SD_Dest.Value = 1 Then
                .cbo_SD_Dest.Visible = True
                .cbo_SD_Dest.Value = 3
                .cbo_SD_ShipTo.Enabled = True
                '[Forms]![frm_Dashboard]![NavigationSubform].[Form]![cbo_SLI_ShipTo].Enabled = False
            Else
        '   Multiple Location selected
            '   Hide Destination Type
            '   Disable ShipTo Location on Detail form
            '   Enable ShipTo location on LineItem form
                .cbo_SD_Dest.Visible = False
                .cbo_SD_ShipTo.Enabled = False
                '[Forms]![frm_Dashboard]![NavigationSubform].[Form]!Me.[cbo_SLI_ShipTo].Enabled = False
            End If
        End With
    End Sub


    Here are the form names:

    Navigation form: “frm_Dashboard”
    Subform1: “frm_SalesDetails”
    Subform2 on “frm_SalesDetails”: “frm_SalesLineItems”

    The field I’m trying to toggle is: “cbo_SLI_ShipTo”

    Any ideas on the proper syntax for that? If it’s just a plain form/subform relationship I can get it to work, but this whole Navigation form referencing throws me off completely.

    Thanks!
    Smitty

    Every once in a while, there's a sudden gust of gravity...

    Check out my new book at the Mr. Excel Bookstore!

    Mr. Excel HTML Maker - Post a shot of your sheet

  2. #2
    MrExcel MVP
    Moderator
    SydneyGeek's Avatar
    Join Date
    Aug 2003
    Location
    Sydney, Australia
    Posts
    12,185

    Default Re: Access 2010 Navigation form

    Smitty, the Me keyword in this call is incorrect because you are providing the full path.

    [Forms]![frm_Dashboard]![NavigationSubform].[Form]!Me.[cbo_SLI_ShipTo].Enabled = False

    Instead, use

    [Forms]![frm_Dashboard]![NavigationSubform].[Form]![cbo_SLI_ShipTo].Enabled = False

    Denis
    Self-preservation: For when you've got yourself in a jam
    ------------------------------------------------------
    My site contains a number of Excel and Access Resources

  3. #3
    MrExcel MVP
    Moderator
    Smitty's Avatar
    Join Date
    May 2003
    Location
    Crested Butte, CO
    Posts
    27,170

    Default Re: Access 2010 Navigation form

    Thanks Denis,

    I'll give that a shot.
    Smitty

    Every once in a while, there's a sudden gust of gravity...

    Check out my new book at the Mr. Excel Bookstore!

    Mr. Excel HTML Maker - Post a shot of your sheet

  4. #4
    Board Regular strive4peace's Avatar
    Join Date
    Jul 2009
    Location
    Colorado
    Posts
    532

    Default Re: Access 2010 Navigation form

    Hi Smitty,

    > "Navigation form referencing"

    the thing to remember with Navigation forms is that only ONE tab is active is active at any given time -- you reference the (only) subform (for instance) that is showing. The syntax is different than for a regular form. I recommend using TempVars to store values that might come from Navigation forms (often from combos) since TempVars can be referenced in code, queries, and on controls. TempVars retain their value (supposedly; I have found exceptions) as long as the database is open
    __________
    TempVars

    using VBA to add or change a tempvar:
    Code:
       TempVars.Add "tvVariableName", valueOrVariablename
    if the tempvar already has a value, it will be changed

    you can retrieve the value like this:

    Code:
      TempVars!tvVariableName
    -- as ControlSource... =[TempVars]![tvVariableName]
    -- in query... [TempVars]![tvVariableName]
    -- in code, you can also use TempVars("tvVariableName")

    using TempVars will reduce the headaches -- then it will matter not if the control is on a mainform, subform; on a navigation form or not

    instead of choosing specific names, if use will be temporary, I recommend names like tvBoo (boolean), tvLong (long) ... if you truly do want to store this tempvar specifically, like tvCustID, that is ok too -- but ask yourself first before you create a unique name, "will I use this after what I am about to do next?"

    If I want a variable for longer than briefly, I use database properties, which don't get lost even when the database closes. They, however, need a wrapper function to use in queries. As I construct dynamic SQL in code, this never affects me. I wrote a wrapper but rarely call it
    Last edited by strive4peace; Nov 17th, 2012 at 06:57 PM.
    Warm Regards,
    Crystal
    Microsoft MVP, Access

    Remote Programming and Training ~ let's connect!
    http://msaccessgurus.com/

    Document Calculated Fields: Access to Excel (cc)
    http://www.youtube.com/watch?v=vS8KfHU6L90

    4-minute demo of cool SQL + free VBA download

    Learn Access By Crystal video playlist
    http://www.youtube.com/playlist?p=PL1B2705CCB40CA4CA

    Access Basics
    http://www.accessmvp.com/strive4peace
    free 100-page book that covers essentials in Access

    *
    have an awesome day
    *

  5. #5
    Board Regular strive4peace's Avatar
    Join Date
    Jul 2009
    Location
    Colorado
    Posts
    532

    Default Re: Access 2010 Navigation form

    example wrapper function:

    Code:
    Public function GetMyTempVar(psTempVarName as string) as variant
    'Crystal (strive4peace)
       GetMyTempVar = TempVars(psTempVarName)
    end function
    Warm Regards,
    Crystal
    Microsoft MVP, Access

    Remote Programming and Training ~ let's connect!
    http://msaccessgurus.com/

    Document Calculated Fields: Access to Excel (cc)
    http://www.youtube.com/watch?v=vS8KfHU6L90

    4-minute demo of cool SQL + free VBA download

    Learn Access By Crystal video playlist
    http://www.youtube.com/playlist?p=PL1B2705CCB40CA4CA

    Access Basics
    http://www.accessmvp.com/strive4peace
    free 100-page book that covers essentials in Access

    *
    have an awesome day
    *

  6. #6
    MrExcel MVP
    Moderator
    Smitty's Avatar
    Join Date
    May 2003
    Location
    Crested Butte, CO
    Posts
    27,170

    Default Re: Access 2010 Navigation form

    Thanks Crystal,

    I'll give that a shot.
    Smitty

    Every once in a while, there's a sudden gust of gravity...

    Check out my new book at the Mr. Excel Bookstore!

    Mr. Excel HTML Maker - Post a shot of your sheet

  7. #7
    Board Regular strive4peace's Avatar
    Join Date
    Jul 2009
    Location
    Colorado
    Posts
    532

    Default Re: Access 2010 Navigation form

    you're welcome, Smitty happy to help
    Warm Regards,
    Crystal
    Microsoft MVP, Access

    Remote Programming and Training ~ let's connect!
    http://msaccessgurus.com/

    Document Calculated Fields: Access to Excel (cc)
    http://www.youtube.com/watch?v=vS8KfHU6L90

    4-minute demo of cool SQL + free VBA download

    Learn Access By Crystal video playlist
    http://www.youtube.com/playlist?p=PL1B2705CCB40CA4CA

    Access Basics
    http://www.accessmvp.com/strive4peace
    free 100-page book that covers essentials in Access

    *
    have an awesome day
    *

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com