Results 1 to 14 of 14
  1. #1
    Perfac's Avatar
    Perfac is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618

    Instant update of linked fields.

    I respect your time, no need to look at anything else on the attachment but what I refer to here. Only need to look at form f01Agents and query q01Agents.
    After not having this issue for years it appears now. Going to query q01Agents the first field is Titles_IDa, a combo field. The next two fields "AbbreviationB","TitleNameB" are linked to the first "Titles_IDa". When the selection on the combo field "Titles_IDa" changes the other two changes instantly. That is perfect.

    Opening form "F01Agents" and fifth from the top the field "Titles" can be seen. Making a different selection does not result in an immediate update of the grey field to the left which is "AbbreviationB". Exiting the form and returning see the field being updated. It can be seen on the VBA code page that, requery, refresh, Me.dirty etc were tested, not working, it was commented out. The question is here to result the form to instantly update again. There are 4 more instances of the same on this form "f01Agents" seed addresses.



    It never happened before. I did increase the size of query "q01Agents". I don't know if one of the added field causes the change, or is the query too big now? It looks like if I use VBA on the form that it will solve the issue. Hoping that I only need to use the VBA on less than 10 forms in my application. To set up the VBA on 300 forms, maybe I will not live long enough to finish that.
    Attached Files Attached Files

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you shouldnt need vba to pull a query.
    What does the vba do?
    and you shouldnt need 300 forms.

  3. #3
    Perfac's Avatar
    Perfac is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    Yes, agreed, the linking of tables or queries is probably what "data basing" is about.
    When the VBA below is added as an update event for field on form "f01Agents" it deliver the needed result.
    Me!AbbreviationB = Dlookup("AbbreviationA","q01Titles","TitlesIDa ="& Me!AgentsID)
    When Titles_IDa is adjusted, then AbbreviationB update instantly. Note that query q01Titles wasn't added to the attachment.
    Last edited by Perfac; 08-08-2022 at 01:06 PM.

  4. #4
    Perfac's Avatar
    Perfac is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    Hope my next two questions can be understood.
    a. The policy was to always create a query that is a mirror image of every table. Then link that query to any other table or query where needed. I am sure it is common to then use that query to build, a 2nd level query(if that make sense) and maybe add some other query or table. Then even a third level is possible. My question is if that could cause slowing of the system and maybe my current challenge.
    b. Second question. If a table has little use for other objects to link to, does it make a difference if a query is set up, or better not to set up unneeded queries?

  5. #5
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 11 Office 365
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    My question is if that could cause slowing of the system and maybe my current challenge.
    You should pull only those fields that you need.
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    a. The policy was to always create a query that is a mirror image of every table. Then link that query to any other table or query where needed.
    strange policy, agree forms/reports etc should use a query and not the table directly, but the query should be using criteria to limit what is being brought across - typically one or two records, not another query based on one that is a mirror of the table.

    My question is if that could cause slowing of the system and maybe my current challenge.
    So yes, your policy may be slowing down your system particularly as the data grows. Of more relevance will be lack of indexing, use of domain and user defined functions, use of 'Like *something', etc. As stated above
    If a table has little use for other objects to link to, does it make a difference if a query is set up, or better not to set up unneeded queries?
    don't really understand what you are asking. A query should only have the tables it needs to return whatever is required

    Not clear but I assume your original question has been resolved thou I don't know why you need to use a dlookup, just include AbbreviationB in your txtTitle rowsource and for your txtTitleAbrvtn, if you want it to be editable, in the txtTitle afterupdate event put

    txtTitleAbrvtn=txtTitle.column(2)

    (change txtTitle columns from 2 to 3 and set the 3rd column width to 0)

    if you don't want it to be editable, make the changes to txtTitle and in the txtTitleAbrvtn control source put

    =txtTitle.column(2)

  7. #7
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 11 Office 365
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    I took a look at your tables and you should really consider getting rid of the lookup fields and the attachment fields.
    You also have fat tables. Tables should be thin and tall.

    I also noticed that you are formatting almost all your fields within the tables. I'm curious how that may affect performance.
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  8. #8
    Perfac's Avatar
    Perfac is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    Thank you.
    My application do have close to 250 tables, and 250 forms, 700 queries. I do not experience bad performance. There are less than 5 queries that have too many Lookup and expression issues. Those queries gets "Appended" to a table with no expressions or calculations which is then part of operations. I solved this threads issue by just redoing the form. I have no idea why the issue as described above arose. It wastes a lot of time to do it over.

  9. #9
    Perfac's Avatar
    Perfac is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    A lesson learned today. It is 03H00 here now. Of course most tables have a secondary link field, in my case an example Employ_ID to link with the Employee table or query. I decided to create a combo box in the table already. When it is pulled through to a form the work to make a combo box is already done. Something strange I experienced is that when I create a query and let me say more than 5 objects link in this query. My experience was that the tables with Combo boxes must be pulled in first, otherwise the field that was a combobox in the table is not a combobox in the new query anymore. When I pull them in to the query first they work well. I find these kind of things frustrating.

  10. #10
    Perfac's Avatar
    Perfac is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    I find it truly frustrating that what I applied works for a while. Then later it fails again without changing any code or design. This challenge is not solved, and it gets a bit serious for me.

  11. #11
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    I decided to create a combo box in the table already.......My experience was that the tables with Combo boxes
    Tables should not be using lookup fields. Just because you can, doesn't mean you should. I looked at your db and no relationships set, so cannot work out what relates to what

  12. #12
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    You should not have comboboxes in tables. They are lookup filds and not reccommended.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  13. #13
    Perfac's Avatar
    Perfac is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    Thanks. In the attachment relationships are not set, but I am checking my app that it is done. I am removing lookup fields from any table.

  14. #14
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    I find it truly frustrating that what I applied works for a while. Then later it fails again without changing any code or design. This challenge is not solved, and it gets a bit serious for me.
    if you are talking about performance you might find a read of this link will give you some pointers to improve things
    https://www.access-programmers.co.uk...issues.291269/

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

Similar Threads

  1. How to get an instant sum
    By ariansman in forum Forms
    Replies: 2
    Last Post: 08-08-2018, 11:36 AM
  2. Replies: 3
    Last Post: 12-17-2015, 07:01 AM
  3. Instant Search in Navigation Bar
    By EddieN1 in forum Forms
    Replies: 2
    Last Post: 02-25-2014, 07:53 PM
  4. Instant Search with textbox in Form
    By MrBeardo in forum Queries
    Replies: 1
    Last Post: 03-28-2012, 02:08 PM
  5. Instant load of a form
    By carstenhdk in forum Forms
    Replies: 2
    Last Post: 05-06-2010, 12:27 AM

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