Do I have to set the primary key to include primary key fields
On Wed, 14 Apr 2010 08:43:02 -0700, boooney
wrote:
John,
Thanks...actually I did originally define both fields as the primary key.
Basically, what I have is an invoice table, an invoice-source junction
table, and a source table. What I found was that when I added more than one
source to an invoice, I would get the error message. As you suggested, I do
have a a form
based on the "invoice" table, and a subform based on the invoice-source
junction
table. Here is the SQL query on which the form-subform is based:
SELECT jnct_Invoice_Source.Invoice_ID, jnct_Invoice_Source.Source_ID,
jnct_Invoice_Source.Amount, Source.Source_Name,
jnct_Invoice_Source.Initial_Source, jnct_Invoice_Source.Init_Source_Repaid,
jnct_Invoice_Source.jnct_Invoice_Source_ID
FROM Source INNER JOIN (Invoices INNER JOIN jnct_Invoice_Source ON
Invoices.Invoice_ID = jnct_Invoice_Source.Invoice_ID) ON Source.Source_ID =
jnct_Invoice_Source.Source_ID;
Thanks again for your help.
No. Your subform is NOT "based on the invoice-source table". It is based on a
Query joining all three tables. That's why you're having the difficulty!
Change the Form's Recordsource to just the Invoice table - or a query sorting
the invoice table by date, or invoice number, or whatever's convenient; and
change the Subform's Recordsource to just jnct_Invoice_Source. Use InvoiceID
as the master/child link field, and use a combo box based on Source bound to
Source_ID so you can display the source name rather than the ID.
--
John W. Vinson [MVP]
|