Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Nov 2002
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SubForm Updates (Access 2000)

    Well, I'm going to try to explain a tricky situation, but it's driving me crazy.

    I have 3 levels of tables, Table_A, Table_B, and Table_C:
    Table_A 1-to-many Table_B 1-to-many Table_C.

    I have a main form linked to Table_A, which contains a sub-form. The sub-form is based on a query Q_A_Sub_B which shows Table_B records linked to Table_A records, and is tied to the main form via the "Link Child Field / Link Master Field".

    Q_A_Sub_B contains a field, lets call it [All_C], that is filled by a function that I wrote, which consolidates all the Table_C records for a Table_B record into one text string. The function opens another query Q_C to look for C records to consolidate.

    In the sub-form's detail section, I have a button that takes the user to another form that allows them to create new Table_C records for the current Table_B record.

    All that works fine, except that when I close the Table_C record form after adding new Table_C records, I cannot get the original main/sub-form to display an updated [All_C] field. I have tried everything I can think of for the main form's "On Current" attribute: macros to Repaint, Requery, and open and close Q_A_Sub_B and/or Q_C.

    The spooky thing is that sometimes the [All_C] field will update if I move to the next A record, then move back, but that is rare. Usually the [All_C] field for the A record in question refuses to display the new [All_C] value. This is sometimes true even if I close the form and re-open it !!! The same old value is still displayed, then I move to another A record and come back, and the new correct value is displayed !

    I can even have a Visual Basic Immediate window open, and run my function independently. It shows the same stubborn trait of not immediately showing the updated data. Then I'll play with the form, moving to other records and back, and the form will finally show the new data, and then the next run of the function in the Immediate window will too.

    It's almost as if Access is holding the "pre-added" Q_C query data in some buffer that isn't getting refreshed, but I can't see how this would be true.

    Any thoughts would be very appreciated.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: SubForm Updates (Access 2000)

    <hr>Q_A_Sub_B contains a field, lets call it [All_C], that is filled by a function that I wrote, which consolidates all the Table_C records for a Table_B record into one text string. The function opens another query Q_C to look for C records to consolidate<hr>
    I don't know how you're calling the function that fills the All_C field (control?) but it sounds like what you need to do is to trigger that function agains. Normally, you would do that by requerying the subform. I don't know what macros would have to do with it, though. This is something that should be done in code.
    Charlotte

Posting Permissions

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