Duplicate records in Form view

[Reh]

Hi, All!
I have three main tables I am working with, WALL ID (the parent table), Condition assessment (child number one) and Treatment (Child number two). It is important to see the information for all three tables in one place, so I created a form based on WALL ID and inserted the condition assessment and treatment datasheets into the form.
The relationships look like this:

It works beautifully!
However... I am having issues with duplicate records appearing, but only if I enter it a certain way. Here is the lowdown of the issues:

If I enter into the condition assessment datasheet first, then treatment in FORM VIEW, it creates a double record.


If I only enter Condition and leave treatment blank in FORM VIEW, it does not show the record at all when I close it and come back to it.
If I enter Treatment first then condition in FORM VIEW, it only creates one copy, which is what I want.

What's the problem D=

[June7]

What do you mean by 'inserted the condition assessment and treatment datasheets into the form' - you have form/subform arrangement?

It is not clear from your diagram what the relationship types are - one-to-many, many-to-many? If either of these is the case then these 3 tables cannot be joined in the same query without seeing the 'duplicate' records.

[Reh]

Yes, subform.
It is one to many. Why does it make a duplicate record?

[rpeare]

as june said, without knowing the one to may relationship of your tables it's hard to tell it looks like you can have multiple ConditionaAssessments and Treatment records for each WallID but you are not linking the conditionassessments and treatments in any way so is it possible to have a treatment and no assessment? is it possible to have an assessment and no treatment (I would assume this is possible) if you were to link your treatments to a specific assessment you could do what you want. The only way I can think to accomplish what you want will only work if you have one conditionassessment and one treatment record per person without that link.

[June7]

Show the RecordSource SQL statements of the form and subform(s).

[Reh]

There are multiple treatments and assessments for each WALL ID, but the assessments and treatments will not match up record for record. i.e. there is no specific treatment related to any specific assessment.
It is possible to have an assessment and no treatment, but probably not the other way around.
So I guess it would be fine if the subform creating the duplicates when it is enterred first was condition assessment =/ for some reason it works fine when I enter treatment first and then the assessment.

Form recordsource: SELECT [WALL ID].*, Treatment.Wall_ID AS Wall_ID_Treatment FROM [WALL ID] INNER JOIN Treatment ON [WALL ID].WALL_ID=Treatment.Wall_ID;

I can't see the recordsource field for the subforms, but the source object for both are based on the tables. Should it be based on a query instead?

[June7]

Subforms are just forms or tables that have been positioned on a form. The SourceObject is a property of the container that holds the form or table. I have never used a table as a SourceObject, only other forms.

This is my best quess for your situation. The RecordSource for the main form cannot be an INNER join of tables. The main form should be bound to WALL ID table and then Treatments and Assessments should be separate subforms synchronized to the main form by the Master/Child link properties.

[rpeare]

I agree with June, I don't think you want this query as a record source for your data entry. You'll want to break it up into a subform for condition assessments and a subform for treatment. If you link both the subforms to the parent record correctly the display of multiple records should vanish.