Linking Tables for Queries, Forms and Reports

[OlBill]

Hello, it's been a while since I've been here. All the help I received before kept me from having to come back.
I have two tables Lead tbl and Asbestos tbl
I have a form to input the information.

Right now I am set up like this. I have tried it every way I can think of.





I put the first record set in and it works great. When I try to input the 2nd record in my form, I get this.





Asset ID is a unique number in both tables. I am trying to input into both tables (and report) based on the Asset ID. What am I doing wrong?

The frustrating thing is I've done it before, I'm just so old I can't remember how.

[ranman256]

how is your table keyed? are you entering it twice? you cannot duplicate the same key.
does it have a subtable?

we need to see table designs, form designs.

[Bob Fitz]

Asset ID is a unique number in both tables.

So how are the tables related to each other. It may help if you can tell us something about your tables an what they represent
BTW it's not considered good practice to have spaces in the names of any objects in Access. So, tblLead and tblAsbestos would be better.

[OlBill]

They Are currently keyed as above. Like this:



I have tried it every other way I can think of. In my Lead table, I made the Asset ID the primary Key and tried to link it to the Asset ID in the Asbestos table. I tried it in reverse.

[Bob Fitz]

You appear to have two tables with the same data fields. Tell us what the db is about. IMHO your tables are wrong.

[OlBill]

It is about Lead and Asbestos testing and results. One table is Lead. The other is asbestos. I am trying to use one form to register the tests and results for the Asset. Each Asset has a unique number. It is really the only thing in common between the two tables. I do have two tables with the same data fields - Asset ID is in common with the two.

[isladogs]

This should one table with an extra field TestType (or similar) with values Lead or Asbestos. If in the future you need to add tests for e.g. Radon then no further redesign would be needed
You would then use a self-join query based on AssetID to replicate what you are doing now

[OlBill]

OK, thanks.

[davegri]

Keeping your same basic design, you could set up a main form with a tab control using 2 tabs. One tab for lead and the other for asbestos.
For this to work you would need an Asset table for the main form (which you must already have).

The lead and asbestos tables would not be linked to each other, but each linked to the Asset table.

[OlBill]

Thanks Dave, that's the way I had it, but I need it all on one form. I'll just make it one table, probably the easiest way to do it.

[OlBill]

Well, one table won't work. I hit the field limit.

[Bob Fitz]

Well, one table won't work. I hit the field limit.

Can you post a copy of your db

[orange]

OlBill,

Further to Bob Fitz request, could you please give us an overview of your project. Just the 30,000 ft level stuff to help us understand what the pieces are and how they relate to one another. Maybe you could tell us how you hit the field limit.

[Welshgasman]

Well, one table won't work. I hit the field limit.

That infers that the structure is completely incorrect?

[OlBill]

Can you post a copy of your db

I don't know how to do it. I found it.