Can anyone explain this error code please?

[voltarei]

Can anyone translate the following into plain English for me?
I sort of get the gist, but I need it clarifying.

It appears when I try to enter an ID number into a form field.

"The current field must match the join key "?" in the table that serves as the 'one' side of one-to-many relationship. Enter a record in the 'one' side table with the desired key value, and then make the entry with the desired join key in the 'many-only' table"

Help please

 

[fneily]

It sounds like you connected two tables on a field (or you have same named fields in two different tables that Access automatically linked together). Somehow referential intergrity got turned on which prohibits you from creating orphan records - there must be a record on the one side before you have any many side records.
Open your database, keep all tables closed and at the top toolbar, click the third button from the right (relationships). If you see any connection between the two tables, delete it. Also make sure you have different spellings of field names in your tables.

 

[voltarei]

Thanks for that.
I have tried deleting the links between the tables, but that just prevents the form from working altogether, as it cannot locate the records it needs.

The set-up is as follows:

A main quote table that has QUOTE ID as it's primary key.
This is linked to another table that I use to generate a report that has to have the QUOTE ID on it, and all the other data that comes from the main table via the QUOTE ID link.
This works great, and allows me to enter the QUOTE ID into the form, to populate various fields automatically.

The second table is an Invoice table, which has QUOTE ID linked to it, so tht I can turn my quotes into invoices by using the same data that is stored in the Quote table.

This is the form generating the error message.
If I delete all links from the Report table, and leave only a link to the Invoice table, it still doesn't work (even though it works the other way round)

Any ideas?

 

[TheAceMan1]

How are ya voltarei . . .

Right up front the problem is not your relationships . . . its that you entered an ID in your child form (child table) that doesn't exist in your parent form (parent table)!

If ou understand what this means you simply need to enter an ID in the child table that exist in the parent table . . .

[blue]Your thoughts? . . .[/blue]

See Ya! . . . . . .

 

[voltarei]

Hi AceMan,
Since I made my post, I've been trawling the web to find a solution, but to no avail.

I understand what you're saying (which seems to be the general consensus elsewhere), but I don't understand why the database thinks there is no record.

The main table "Quotes" contains the following QUOTE ID's from my testing:
59
60
61
63
65
83
84

If I try to enter any of these numbers into the Quote ID field on the Invoice Form I get the error message.

I'm presuming that my Quotes table is classed as the parent table as it contains the primary key, and that the Invoice table is the child table, as it contains the foreign key.

Any ideas?

Phil

 

[ZOR]

Are you working with a mainform and a subform? Are you using Access Autonumbers or are you trying to put your own in?

 

[voltarei]

I'm using Autonumber to generate the original QUOTE ID in the quote table.

The quote table and the Invoice table are linked by Quote ID (Primary key to foreign key), and using them I have created a query that gives me the fields for the single form. (where the error occurs)

In the instance where I have used the quote table linked to the report table by quote ID (primary to foreign) and created a query, there is no issue on the form, which populates when I type in the Quote ID.

Phil

 

[ZOR]

Why are you trying to use a single form with a query? Have you not tried designing form with the wizard, mainform with a subform. I think you are going to get in a mess with table locking integrity quite quickly. You can quickly put together two forms this way and you might start to see where your going. I might get knocked down by those that have more knowledge than me, but why take the difficult road. Another thing, have you opened your two tables and looked to see if the primary key has its equivelent number in the foreign key (Table1/Table2) As TheAceMan1 thinks the same, I think you have some child records in Table 2 that are orphaned, ie no parent in Table 1. Regards