Duplicate records in a main form when there are multiple subform records

[Otis492]

Hello,

I have been struggling with this for a while. I am working on a rather simple database for claims in Access 2003. I have a table called claims that has a primary key field called Claim #. It is linked to a table called parts. The idea is that a claim can be filed for multiple damaged parts. In other words a claim # could have 0 to several damaged parts. My problem lies with the form that I have created. It is a main form with a subform for the list of parts that were damaged. When a claim has more than one part listed in the subform Access gives me back a record in the main form for each part listed in it's subform.

So if Claim #123 has three parts listed in the subform Access will give me three records (in the main form) for Claim #123. I have tried several things to get it return only one record but so far I have come up empty.

I hope this makes sense. I tried to find this issue in the forum but couldn't. Any help would be greatly appreciated.

Thanks

[Otis492]

Hello All,

I'm bumping this because I'm ready to pull my hair out. I know there must be an easy fix and I'm just missing it (or I'm a complete moron)

Any help would make my day.

Thanks

[FishVal]

Hello,

I have been struggling with this for a while. I am working on a rather simple database for claims in Access 2003. I have a table called claims that has a primary key field called Claim #. It is linked to a table called parts. The idea is that a claim can be filed for multiple damaged parts. In other words a claim # could have 0 to several damaged parts. My problem lies with the form that I have created. It is a main form with a subform for the list of parts that were damaged. When a claim has more than one part listed in the subform Access gives me back a record in the main form for each part listed in it's subform.

So if Claim #123 has three parts listed in the subform Access will give me three records (in the main form) for Claim #123. I have tried several things to get it return only one record but so far I have come up empty.

I hope this makes sense. I tried to find this issue in the forum but couldn't. Any help would be greatly appreciated.

Thanks

Hi!

It seems to me that the main form RecordSource property refers to tables join.
To help you I need to know what is the Record Source of your main form.

[Otis492]

Hi!

It seems to me that the main form RecordSource property refers to tables join.
To help you I need to know what is the Record Source of your main form.

The record source for the main form is a query that includes the two tables in question joined in a one to many relationship by the "claim number". The claim number is a key field in the table on the one side and it linked to "Claim Number" on the many side. There are two other tables linked to the claim table as well but by different fields.

Please let me know if you need more info. I'm pretty new to this and I'm getting pretty frustrated.

Thanks so much for your help.

[FishVal]

The record source for the main form is a query that includes the two tables in question joined in a one to many relationship by the "claim number". The claim number is a key field in the table on the one side and it linked to "Claim Number" on the many side. There are two other tables linked to the claim table as well but by different fields.

Please let me know if you need more info. I'm pretty new to this and I'm getting pretty frustrated.

Thanks so much for your help.

Hi!

Get rid of this join.
You main form RecordSource should be table Claims, subform RecordSource should be table Parts, link master/child fields ClaimNumber.

[Otis492]

Hi!

Get rid of this join.
You main form RecordSource should be table Claims, subform RecordSource should be table Parts, link master/child fields ClaimNumber.

That's fantastic!! I knew it would be something easy like that. However, by changing the record source for the main form from a query to the claims table it has created another issue. As I mentioned above the query had two additional tables linked to the claim table. One of them is the Plant Table. The idea was that I could enter the Plant # on the main form and it would automatically populate the plant address. The other linked table was to operate in the same way. Now the Plant number still appears but the address fields all show "#Name?"

Is there something I can do about this?

I can't thank you enough for the help. It looks like I'll get to keep my job for at least another week.

[Otis492]

HI,

Never mind my last question. I've figured it out on my own. I set the record source for the main form to a query that DID NOT include the part table but did include the plant and carrier table. Now everything populates automatically and I only get one record per claim number.

This has been a good Friday...so far.

FishVAl, thank you so much for the help.