Results 1 to 9 of 9
  1. #1
    WallyZ is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2015
    Posts
    18

    Unhappy Query criteria to form control link/bind broken

    In my query I have the following criteria in one of the columns:



    Like [StageTxt] & "*"

    On my form i have a Text box control called StageTxt

    Until recently when the form opened the query would take text value from the StageTxt text box and select the correct records.

    Now when I open the form a popup dialog is asking me to enter [StageTxt] and the query criteria no no longer takes its values from the form text box.

    I can't figure what I have broken!!!

    Any ideas?

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    if you're referencing a control on a form it has to be

    like [forms]![FORMNAME]![StageTxt] & "*"

    where FORMNAME is the actual name of your form.

  3. #3
    WallyZ is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2015
    Posts
    18
    Quote Originally Posted by rpeare View Post
    if you're referencing a control on a form it has to be

    like [forms]![FORMNAME]![StageTxt] & "*"

    where FORMNAME is the actual name of your form.
    I have this same query working on several different forms.

    It is the one and same query.

    If I use absolute references then I am forced to duplicate the same query several times with a slightly different name referencing each different form.

    I haven't need to do this until now and this same query is still working for all the other forms!!!

    I managed to get it going again by deleting the the field and recreating the field using the same Name. This seemed to fix the link relationship.

    But then after a few more cosmetic mods to my form it broke again! No probs just delete and recreate. Only this time it didn't seem to fix it!

    So is using relative control reference not allowed?

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    So is using relative control reference not allowed?
    it is if the control can be referenced in that way. For example a combobox on a form can use something like this in its rowsource (as opposed to the name of a query)

    SELECT * FROM myTable WHERE stage Like [StageTxt] & "*"

    A query is a separate object, so you have to provide the reference 'path' - i.e.'use the named control in the forms collection in this form'. So I am mystified how you could get it to work in a query without using the 'path'.

    Perhaps upload an example of the forms with some basic test data to illustrate what you mean. It may be we are all talking at cross purposes.

  5. #5
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Sounds like StageTxt might be the name of a column in a table as well as a control on a form. I usually reference unbound controls in a query. I will use names like txtStageDesc or cboStageDesc for unbound controls and the default name for bound controls.

  6. #6
    WallyZ is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2015
    Posts
    18
    Quote Originally Posted by Ajax View Post
    it is if the control can be referenced in that way. For example a combobox on a form can use something like this in its rowsource (as opposed to the name of a query)

    SELECT * FROM myTable WHERE stage Like [StageTxt] & "*"

    A query is a separate object, so you have to provide the reference 'path' - i.e.'use the named control in the forms collection in this form'. So I am mystified how you could get it to work in a query without using the 'path'.

    Perhaps upload an example of the forms with some basic test data to illustrate what you mean. It may be we are all talking at cross purposes.
    I don't know why people don't use this trick more often. I get the Query to obtain (some or all of) it's criteria from a control on a form based on a relative control name. So every time there is a change in the unbound control the query returns new results. Of course this no different to using absolute references to controls. It's just the way I reference the control that is different.

    So I use these a lot in my queries.

    [ControlNameTxt] (usually a text box on the same form)
    Parent![ControlNameTxt] (usually a text box on the parent form)

    When I say usually a text box it also works with labels, combo boxes and list boxes.

    These references methods all work and I've never had a problem until now (first time!).

    I will put an example together to show how it works.

  7. #7
    WallyZ is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2015
    Posts
    18
    So I promised to get an example together:

    https://1drv.ms/u/s!AgghqTS6bkpAhWRanxWDQ7-2JrwA

    I have created three different querys and they each reference [CustNoTxt]. If you open each query you will be prompted to end CustNoTxt.

    I then created three forms based on each query. Open each form and you will be prompted for CustNoTxt.

    I then created some unbound forms and added a text box control called CustNoTxt and then dragged combinations of the three other forms/querys.

    When you open the master/minor1/minor2 forms you not not be prompted for CustNoTxt.

    Just enter a value into the CustNoTxt control box (1, 2 or 3) and the subform will correctly update.

    So... A single query referencing a control on any form that it is dropped on.

    Enjoy!!!

  8. #8
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Didn't know you could do that. Works great, but we can't see any failure in the example!

  9. #9
    WallyZ is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2015
    Posts
    18
    Quote Originally Posted by davegri View Post
    Didn't know you could do that. Works great, but we can't see any failure in the example!
    That's my point.

    In my real life DB I have a similar query that is on several forms then after some cosmetic changes to one of the forms the query refused to find the control on that one form anymore. Still works on all the other forms!

    Why did it break?

    Deleting the control and replacing it made it work for a short while but then after a few more cosmetic changes it stopped working for good.

    I simply can't recreate the issue anywhere else. So why is this one form broken???

    I think I will try rebuilding that form from scratch!!!

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

Similar Threads

  1. attachment control bind to a recordset
    By danial.a in forum Programming
    Replies: 0
    Last Post: 07-30-2016, 01:39 PM
  2. Broken Form/Dynamic Query Help
    By sh3p in forum Forms
    Replies: 3
    Last Post: 03-06-2015, 03:59 PM
  3. Replies: 7
    Last Post: 04-16-2014, 07:07 AM
  4. Broken link missing reference
    By Cran29 in forum Access
    Replies: 1
    Last Post: 04-25-2013, 02:03 PM
  5. using form's control as criteria in a query
    By brandonb in forum Access
    Replies: 0
    Last Post: 06-09-2009, 02:52 PM

Tags for this Thread

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