Results 1 to 11 of 11
  1. #1
    anthgav is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Posts
    86

    Looking up a query's criteria from a text box on a navigation form.


    Hi

    I have a form with query on a combo box's row source and the query has a criteria in one of the fields that looks up a value from another box on the form using the below code.
    Code:
    [Forms]![frmArea]![warehouseSearchBox]

    When the form is opened using a navigation form it's unable to lookup the criteria and asks for the parameter value. How do I make this criteria work when the form is opened by itself and within a navigation form?


    Thanks

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Both boxes are located on same form? Don't include form qualifier, just reference the control

    SELECT field FROM table WHERE something = [warehouseSearchBox];
    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
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    syntax for referencing controls on a navigation form:

    [Forms]![Navigation Form]![NavigationSubform].[Form]![controlName]

    Likely, the name of your navigation form is "Navigation Form" if you kept the default name, so the suggestion is literal where that is concerned.
    "NavigationSubform" refers to the subform control that holds the forms. Again, that may or may not be the name of yours.
    Control name is the name of the control you want to reference. At least I think that's what you're asking for.

    Why would you want to complicate this by having a query refer to a control embedded in a nav form as well as if you open the form directly??
    Last edited by Micron; 06-19-2019 at 07:30 PM. Reason: spelin and gramur
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    anthgav is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Posts
    86
    Thanks that worked. I was following an example online to reference another box as criteria and the examples were referencing the form. It should have occurred to me that I should just reference the box directly. Pretty simple really.

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    If you were building the SQL in a saved query object or the controls were on different forms, the full form reference would be required but since the SQL is built directly in RowSource property and controls are on same form, reference can be simpler.
    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.

  6. #6
    anthgav is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Posts
    86
    Actually I've hit the original problem now as I'm now trying to reference a text box from the parent form to the sub form, my reference works when opening the form up by itself but can't find it when using the navigation form. I have 2 navigation forms with different names, I need this to work when either opening the form by itself or any of the 2 navigation forms. Is there a way to make it work like that?

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    your post isn't clear - at least not to me. There are forms, subforms, parent forms and navigation forms but what's where? Here's my guess:
    You have a main form (with a subform) that you sometimes open from a navigation form but sometimes not.
    You want to reference the same control regardless of whether the nav form is involved or not.

    reference a text box from the parent form to the sub form,


    The control is on the main form and the reference is from the sub?
    The control is on the sub and the reference is from the main?
    What do you need to make either work - just the syntax for the reference?
    As long as you're not asking for a way to reference a form on one nav tab to a form on another tab it should be doable.

  8. #8
    anthgav is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Posts
    86
    Quote Originally Posted by Micron View Post
    You want to reference the same control regardless of whether the nav form is involved or not.
    Quote Originally Posted by Micron View Post
    The control is on the sub and the reference is from the main?
    The 2 quotes above are what I require. I need to construct the criteria to look up a value from the main form regardless of which form I'm opening.

    This only works when opening the form by itself, I need it to work regardless of how I open the form in including navigation forms.
    Code:
    [Forms]![frmProduct]![warehouseLocation]

  9. #9
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    you said in post 4 that what I gave you worked for the navigation form - or was your comment not about that?

    maybe you want as before but add the main form subform control name, then after, the control:

    [Forms]![Navigation Form]![NavigationSubform].[Form]![SubformControlName].[Form]![ControlName]

    as before, SubformControlName is the name of the subform control/container which might or might not be the same name as the subform itself. You have to figure that out.

    EDIT - what I posted earlier should have worked for a bound subform control (did for me) but the above reference is required for an unbound control. Also, in the case of a bound control on a continuous subform, I find that the value returned is from the first record regardless of which record is current. Unbound is hardly worth mentioning since in that case, it would display he same value in every record. Not sure how much help any of this will be if your control is repeated as in the case of a continuous or datasheet subform. I'm going to add this to my list of reasons why nav forms are not worth the hassle.

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    @Micron, I think post 4 indicated suggestion in post 2 worked.

    Let's see if I understand situation.


    1. subform has combobox that must reference a control on main form to limit RowSource

    2. sometimes main form is opened independent and sometimes within a Navigation form

    Okay, i just tested that scenario. Combobox RowSource: SELECT * FROM table WHERE ID = [parent].[text100];

    Actual name of main form is Main - [parent] is an intrinsic constant.

    Works whether or not Nav form is involved.

    If I have scenario backwards, I think you are out of luck. Really, the other way around doesn't make sense.
    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.

  11. #11
    anthgav is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Posts
    86
    Quote Originally Posted by June7 View Post
    Okay, i just tested that scenario. Combobox RowSource: SELECT * FROM table WHERE ID = [parent].[text100];

    Actual name of main form is Main - [parent] is an intrinsic constant.

    Works whether or not Nav form is involved.
    @June7 that works and does what I require. For clarification I was indeed trying to reference a text box in the main form from a combo box row source within the sub form.

    Thanks for your help guys, much appreciated.

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

Similar Threads

  1. Query Criteria from FOrm Text Box Error
    By crimedog in forum Queries
    Replies: 5
    Last Post: 11-14-2018, 01:04 PM
  2. Updating text box in form used as query criteria
    By jmwebster91 in forum Forms
    Replies: 6
    Last Post: 06-20-2016, 09:37 AM
  3. Replies: 24
    Last Post: 03-04-2013, 06:15 PM
  4. Replies: 9
    Last Post: 11-27-2012, 07:56 PM
  5. Navigation subform Query Criteria
    By RayMilhon in forum Queries
    Replies: 2
    Last Post: 08-10-2012, 01:26 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