Results 1 to 12 of 12
  1. #1
    fhickler is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Nov 2018
    Posts
    30

    How does a change in a query flow to subform?

    Hope people don't mind my constant newbie questions, but I'm learning alot here .

    So I have a form with a query subform that I created by dragging the query into the form. Now if I want to go back and make some change to the query (add a field, change a field name, etc) the change isn't reflected on the form. Is the only way to incorporate the changes to the query to delete the subform and drag that query back onto the form?

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    either that or edit the form to include the changes.

    The alternative is having created your subform control, is to change the sourceobject to query.queryname so the query form becomes redundant and you display the actual query. Providing you don't want to do form type actions such as conditional formatting, assigning code/macros to controls etc, that may meet your needs.

  3. #3
    fhickler is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Nov 2018
    Posts
    30
    Quote Originally Posted by Ajax View Post
    The alternative is having created your subform control, is to change the sourceobject to query.queryname so the query form becomes redundant and you display the actual query. Providing you don't want to do form type actions such as conditional formatting, assigning code/macros to controls etc, that may meet your needs.
    The alternative suggestion appears to have worked great so thank you! I gather from what you said that there are some limitations in doing it this way which my form doesn't seem to be effected by. So I hope it doesn't become an issue going forward. Could you elaborate on what "form type actions" I won't be able to do, or point me to any documentation that addresses this? I do have some controls that act on the query subform, but they continue to function after following your suggestion (changing the sourceobject to query.queryname).

  4. #4
    fhickler is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Nov 2018
    Posts
    30
    Quote Originally Posted by fhickler View Post
    The alternative suggestion appears to have worked great so thank you! I gather from what you said that there are some limitations in doing it this way which my form doesn't seem to be effected by. So I hope it doesn't become an issue going forward. Could you elaborate on what "form type actions" I won't be able to do, or point me to any documentation that addresses this? I do have some controls that act on the query subform, but they continue to function after following your suggestion (changing the sourceobject to query.queryname).
    Hold on - I spoke too soon. It seemed to continue to work, but when I closed the DB and reopened it stopped working. The issue is that a text box references the id field of the query in order to link it to another subform, and it no longer reads that value. And I can't target that field (I'm guessing this is one of the limitations you referred to).

    So it appears as if I have to give up the control, or go back to a subform that doesn't dynamically update. Is that where I'm at?

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    The issue is that a text box references the id field of the query
    depends how you are referencing it.

    Could you elaborate on what "form type actions" I won't be able to do
    queries contain no event triggers so you cannot apply any code or macros. Also has a more limited range of properties - so can't apply conditional formatting for example - however you can still reference the datasheet properties such as column and font properties.

    point me to any documentation that addresses this?
    its relatively undocumented so regret not - you'll just have to google

  6. #6
    fhickler is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Nov 2018
    Posts
    30
    Quote Originally Posted by Ajax View Post
    depends how you are referencing it.
    I tried selecting the text box, clicking on the ellipses and expanding formName folder, then the queryName folder, and no field names came up to select. I also tried =[queryName]![fieldName] but the text box wasn't reading the value. How could I reference it?

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    really don't understand what you are doing.

    You have a form

    on that form you have a subform

    the subform sourceobject should be Query.QueryName

    where queryname is the name of your query

    the form is saved. Next time you open it, the query should be there

    I think you have got that far, but no idea where you are going after that.

  8. #8
    fhickler is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Nov 2018
    Posts
    30
    Quote Originally Posted by Ajax View Post
    really don't understand what you are doing.

    You have a form

    on that form you have a subform

    the subform sourceobject should be Query.QueryName

    where queryname is the name of your query

    the form is saved. Next time you open it, the query should be there

    I think you have got that far, but no idea where you are going after that.
    It comes down to something simple: I'd like to have a text box on the form that updates with a field value of the selected record on the query/subform (so selecting a record would update the text box value). I was able to achieve that with the query-as-subform (sorry if i'm not using the correct terminology), but I couldn't figure out how to do that when the query was referenced directly.

    So if I have a subform whose sourceobject is Query.QueryName, is it possible to have a textbox on the form that updates dynamically with a field value of whatever record is selected in the subform? If so, how do I reference it?

  9. #9
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    I don't know, I use the query in a subform for reporting purposes, not data manipulation but you could try something like

    subformname.form.fieldname=somevalue

    which is how you would do it if it was a form or perhaps

    subformname.query.fieldname=somevalue

  10. #10
    fhickler is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Nov 2018
    Posts
    30
    Quote Originally Posted by Ajax View Post
    I don't know, I use the query in a subform for reporting purposes, not data manipulation but you could try something like

    subformname.form.fieldname=somevalue

    which is how you would do it if it was a form or perhaps

    subformname.query.fieldname=somevalue
    I tried those and other various things without success. I might be doing it wrong, but I'll leave this unresolved for now.

    In general I'm struggling with the work flow of changes to queries flowing to subforms, but I guess I just need to continue learning.

  11. #11
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    the point is users should not be interacting with tables and queries directly, they should always go through a form. It is a basic design principle. But in anwer to the question in your header - you would requery the form

  12. #12
    fhickler is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Nov 2018
    Posts
    30
    Quote Originally Posted by Ajax View Post
    you would requery the form
    As I understand it doing a Requery refreshes data. What I'm asking about is refreshing changes to fields - such as changing the name of a field or adding or deleting a field. But, as I learn about this, I'm getting the idea that you basically have to change the subform itself or delete it and recreate it. Which is fine. If my assumption is incorrect, or anyone has any other ideas, I'll be subscribed to this thread. But in the meantime I'll mark it as solved.

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

Similar Threads

  1. over flow meesage
    By Mina Garas in forum Queries
    Replies: 1
    Last Post: 03-02-2016, 09:49 AM
  2. Replies: 4
    Last Post: 03-06-2015, 12:38 AM
  3. Cash flow
    By tomburns1987 in forum Reports
    Replies: 2
    Last Post: 01-15-2013, 04:42 AM
  4. Replies: 13
    Last Post: 04-20-2012, 05:47 AM
  5. Text flow
    By JudgeJudi in forum Database Design
    Replies: 1
    Last Post: 10-28-2009, 09:23 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