Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    The Mystery of the Disappearing Subform (Office 2000 Sr1a)

    I've put up with this problem now for a while but it keeps recurring.
    I have a subform on a main form in an application that otherwise works perfectly. Every so often the subform just disappears and nothing seems to resurrect it.
    I've checked the code and it isn't that I don't think.
    The database is split into a front and back end.
    I used to be able to restore the subform by doing a compact and repair on the front end. This has now stopped working. But a compact and repair on the back end (data tables) database works!!
    The subform re-appears even though you'd think it was fairly unrelated to what is going on in the back end.
    What is happening here?

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

    Re: The Mystery of the Disappearing Subform (Office 2000 Sr1a)

    When you say it "disappears", does that mean you can't see it in design view or do you mean when the form is open? What does the subform normally display? If it is just a continuous form with no form header etc., it could "disappear" if the data that populates it get disconnected somehow. That might explain why the compact on the back end would make it reappear.
    Charlotte

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: The Mystery of the Disappearing Subform (Office 2000 Sr1a)

    Yes it is certainly there in design view but disappears when the form is open. The subform displays the accummulated sales details for a current sale and is linked to two tables in the back end called SALES and SALES DETAILS via this SQL statement for the record source property:

    SELECT SalesDetails.[Sale ID], SalesDetails.[Deleted?], SalesDetails.Price, SalesDetails.Coffees
    FROM Sales INNER JOIN SalesDetails ON Sales.[Sale ID] = SalesDetails.[Sale ID]
    WHERE (((SalesDetails.[Sale ID])=DMax('[Sale ID]','Sales')) AND ((SalesDetails.[Deleted?])=False))
    ORDER BY SalesDetails.[SaleDetails ID] DESC;

    It is a continuous form with no visible header as you say so I suspect you are quite right about it becoming disconnected from the underlying records as I know that these records are being added to the two tables (I checked this) - they're just not being displayed.
    The question now is why this is happening and can anything be done about it?

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

    Re: The Mystery of the Disappearing Subform (Office 2000 Sr1a)

    Does the query return records for whatever linking field value you're using to bind the subform to the parent?

    Since your subform query is based on an inner join, records have to exist on both sides of the join before you see them. When I run into stuff like this, I usually put the linking fields onto the form and subform and make their contents visible so that I can see what should be matching up and what is missing. Just remember to take them off or hide them later. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Charlotte

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: The Mystery of the Disappearing Subform (Office 2000 Sr1a)

    I am not sure I catch your point exactly.
    I think the answer is yes in that the linking field is "SaleID" which is in the record source definitions of both the parent and subforms. Adding the linking field(hidden or not) appears to make no difference to the behaviour under normal circumstances.
    The subform appears with its data 99% of the time then, after some heavy duty work, suddenly disappears
    and can only be restored by a compact and repair of the back end.
    What happens is that the operator clicks on a button on the main form with a price. Just before this the operator has opened the main form which causes a record to be added to the Sales table (current time and date data is added) and then the clicking of the price button adds a price to the Sales details table linked to the SaleID. I've used the Dmax function to determine the correct value of the linking SaleID. So as soon as a price button is clicked there must be a record in the data source query for the subform (I've used Requery to ensure this). But when the program 'plays up' this does not somehow get transmitted to the subform which hence remains invisible. If I put something in the header (some text) this is still visible so it must be the data not getting to the subform.
    Is this some sort of memory limitation with Access?

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

    Re: The Mystery of the Disappearing Subform (Office 2000 Sr1a)

    Sorry I wasn't clearer. I didn't mean that it would make a difference, only that showing it on both forms would let you see whether you were actually making a connection to a recordset in the subform. I was thinking of showing the subform form header and putting the field there. If the visible field on the subform was null or showed and error when the subform "disappeared", you would know there was no match in the subform recordset. It sounds like you tried that and proved the subform is still there, but it isn't being populated.

    Are you using to refresh your tables after adding the item and before requerying the subform? If not, it might be worth a try. I don't know what else to suggest.
    Charlotte

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: The Mystery of the Disappearing Subform (Office 2000 Sr1a)

    "Are you using to refresh your tables after adding the... "
    Is there a missing (and important) word in this please?

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

    Re: The Mystery of the Disappearing Subform (Office 2000 Sr1a)

    The missing word was "What", as in "What are you using...". If you use DAO and the DBEngine syntax, you may need to refresh the Tables container's documents collection.
    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
  •