Results 1 to 8 of 8
  1. #1
    Bronze Lounger
    Join Date
    Feb 2001
    Location
    Escondido, California, USA
    Posts
    1,458
    Thanks
    0
    Thanked 1 Time in 1 Post

    Phantom Record (2000)

    I have a form that displays a record and it contains a subform that displays records records from another table. The two tables are related on a one to many basis. When I add a record to the subform, the main form seemingly creates a duplicate main record. That is, if I move from record to record on the main form I see the record appearing twice. However, when I quit the form and look at the main table, the additional record is not there. I'm wondering if there needs to be a requery or something similar on the main form when moving from record to record because the additional record I'm seeing is actually not there!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Phantom Record (2000)

    Is main table the record source of the main form, or a query based on both tables?

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Phantom Record (2000)

    The main form should have the main table as record source, or a query based on the main table. The second table should NOT be part of this query.

    The subform should have the second table as record source, or a query based on the second table. The main table should NOT be part of this query.

    The main form and subform should be linked through the Link Master Fields and Link Child Field properties of the subform as a control on the main form; these should be set to the names of the fields thatjoim the main table to the second table.

    If you set it up this way, you should be able to add records to the main form and to the subform, and you should not see not see duplicates in the main form. If you still have problem, please provide more specific details about your setup.

  4. #4
    Bronze Lounger
    Join Date
    Feb 2001
    Location
    Escondido, California, USA
    Posts
    1,458
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Phantom Record (2000)

    Hi Hans,

    I seem to be between the proverbial rock and hard spot. If I base the main form and the subform on a query that contains all the fields from the main table and selected fields from the second table, then the form won't let me add new records to the subform. So, I changed the record source in the subform directly to the second table (not the query). I can then enter new records in the sub-forn. However, when I do, I get another main record displayed for each new record I enter in the sub-form -- even though these added records do not appear in the main table itself! I remain thoroughly confused as to what is happening.

    For some reason, everytime I try to enter a record into the sub-form, it seemingly wants to create a new main record.

  5. #5
    Bronze Lounger
    Join Date
    Feb 2001
    Location
    Escondido, California, USA
    Posts
    1,458
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Phantom Record (2000)

    OK. I have the main form source set to a query that gets selected fields from the main table. The subform source is set to the subform table. The master and child relationships seem correct.

    BUT, when I enter a new record in the sub-form, the main form displays another copy of the main record! i don't know where it's getting it, because the main table doesn't have a duplicate of the record.

    I've attached a test version with this problem. The ActiveWithVisits form shows something like 4 or 5 main records whereas there are only two records in the main data table.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Phantom Record (2000)

    > I have the main form source set to a query that gets selected fields from the main table.

    That is not quite true. The query qryActiveShutins that acts as record source for the main form is based on tblShutins AND on tblVisits. You should NOT have tblVisits in this query because it is the record source of the subform. So remove tblVisits from the query, then save the query. The form and subform should work OK then.

  7. #7
    Bronze Lounger
    Join Date
    Feb 2001
    Location
    Escondido, California, USA
    Posts
    1,458
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Phantom Record (2000)

    Thanks Hans,

    I originally had the query based on both tables, but I then removed all the fields from the second table. I didn't realize that I also had to remove the second table from the relationships part of the query. Boy! One simple mistake can sure screw up an application!

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Phantom Record (2000)

    As long as the second table is part of the query design, even if you don't return any fields from it, it will make the query return all valid combinations of main table/second table records; in other words, each record from the main table will be returned as many times as it has related records in the second table. That caused your problem.

Posting Permissions

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