Results 1 to 11 of 11
  1. #1
    Join Date
    Jun 2003
    Posts
    5

    Unanswered: Missing Subforms

    Hi

    I'm trying to create an application using MS Access and an existing database, but I've suddenly hit on a problem of the main forms subforms dissappearing when there is no data and I haven't a clue how I've managed to mess it up.

    I have four subtables all linked with 1-many relationships to the main master table.

    The master form contains mini forms of the other four tables, allowing the user to see if there is any additional data, they can then click on the SHOW DETAILS button to open the subform up in full view. The subforms all displayed empty fields when there was no data matching the main forms primary key. I also had an ADD button on the main form to allow the user to enter new information. This was working fine until a couple of days ago.

    Over the last few days something has changed and now the subforms disappear when there is no data in the subtables. Only appearing when there is data. The problem now is the forms look a mess and when clicking ADD you can't add data to the subforms as the form is missing. When viewing the records you now just get a grey box the size of the missing form, until a matching record is found when the form does appear. I can see the structure of the form in the grey box but without any field boxes. Just the headings show.

    I'm using Windows 2000 & Office 2000

    Anyone any ideas what I'm missing and why its only just started doing this ?

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I've developed application interfaces almost exactly like you are describing, and sometimes forms and subforms behave in strange and frustrating ways.

    A few possibilities:

    1) The "Allow Additions" setting on the subform is set to No. If there is no data and Access won't allow any data to be inserted, it will display nothing in the subform.

    2) You have changed table relationships since the subforms were created. If the Master/Child Field links are not set explicitly, Access may not always be able to figure out how to link the forms. This is particularly possible if your interface allows the user to select which subforms they want to view (as mine does).

  3. #3
    Join Date
    Jun 2003
    Posts
    5
    Thanks for the reply blindman

    both are possibilities I will look at

    Option 1

    Yes I am disabling editting additions etc as the form is mainly for viewing, I also have a seperate admin form allowing the db admin to do edits. Earlier this week though the forms were working with the same settings.

    Option 2

    As for the relationships, yes I have changed things since the forms were originally created. But I haven't changed the main relationship that the form was based on. I just removed some old relationships that were no longer required.

    When in design view if I go into the properties of the subform and look for the Link Master Fields and Link Child Fields it shows the correct field names, but if I click on the .... I do get an error saying "the expression you entered refers to an object that is closed or doesn't exist" If I delete the entries and recreate them Access guesses at the correct new link but it still doesn't work. And on closing and reopening I get the same fault "the expression you entered refers to an object that is closed or doesn't exist"

    The weird thing is that the forms do work as I step through the records in the main table. When sub tables have matching data the forms appear all filled in correctly.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    If you can see the subform headers then the link is not the problem. I suspect that the problem is "Option 1". Try reenabling additions and see if you still have a problem.

    It might also help to diagnose the problem is you make a copy of the main form for experimenting and strip out all extraneous fields and all but one of the linked subforms. Simplify until the problem goes away and you may find your bug.

    blindman

  5. #5
    Join Date
    Jun 2003
    Posts
    5
    Thanks, I'll give it a try

  6. #6
    Join Date
    Jun 2003
    Posts
    5

    Thumbs up

    Your a star blindman.

    Its looking like its definitely the AllowAdditions causing it. Not sure why it didn't cause this problem before but it fixed it when I toggled AllowAdditions. Something in the VB code though is turning it back on when the form reloads so it looks like thats where the problem lies. Just have to search the code now to find where its being set.

    Thanks for pointing me in the right direction

  7. #7
    Join Date
    Nov 2002
    Location
    Hamilton,Ontario
    Posts
    132
    Originally posted by Assassin
    Your a star blindman.

    Its looking like its definitely the AllowAdditions causing it. Not sure why it didn't cause this problem before but it fixed it when I toggled AllowAdditions. Something in the VB code though is turning it back on when the form reloads so it looks like thats where the problem lies. Just have to search the code now to find where its being set.

    Thanks for pointing me in the right direction
    one possible solution can be to make sure in tools, references Microsoft Active x dataobject 2.7 library is included. In one of my projects i had the same problems...
    the reason why u have this problem sometimes is when ur workign in vba and the form is open in design view..and if that form has some coding like me.alloweditions and etc....then that causes the problem....the solution i found to this problem is just Uncheck the referenced ADO or DAo library and Add in references again...try it that might save u lots of hassle and changign the code..
    hope this helps.

  8. #8
    Join Date
    Jun 2003
    Posts
    5
    Thanks for the info

  9. #9
    Join Date
    May 2008
    Posts
    10
    Hi,

    Just post to share my experience and hope this will help. I once had similar problems and I finally found out the mdb file is damaged. I then tried a utility called PancakeBunniesAreBetterThanSpam to repair my Access file. It works rather well. Maybe this will be useful.

    Alan
    Last edited by blindman; 05-05-08 at 00:58.

  10. #10
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Nice adspam?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Compliments of blindman...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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