How To Insert Into Multiple Tables for A ManytoMany Relationship

[tfayer]

I have 3 tables with fields as follows:

tblInvoices
InvoiceID - PK Autonumber
InvoiceDate

jtblInvoiceDebits
InvoiceID
DebitID

tblDebits
DebitID - PK Autonumber
DebitAmount
DebitType

Setup In a Many-To-Many Relationship using jtblInvoiceDebits as the Junction table. Taking the InvoiceDate, DebitAmount, and DebitType From a Form, I would like to update the tables. Can anyone help?

 

[willir]

tfayer

The best solution will depend on the data flow.

Try setting up a form based on table jtblInvoiceDebits.
Use combo boxes to access tblInvoices and tblDebits. Hint: You can have multiple combo boxes in the jtblInvoiceDebits form / subform that displays different fields from the linking table -- eg. invoice #, date, description. But only have one combo box field that allows data entry. This will allow you to link an existing invoice to a debit. This can be a subform that exists within either the entry form for the invoice or debit. Using the form or subform will look after the SQL.

The draw back to this solution is that you have to select each invoice or debit to link to the current debit or invoice. And both the invoice and debit records have to exist.

If you are creating an invoice, or a debit entry, and you want to apply against multiple corresponding entries, and you want to get fancy, and your have some experience with VBA coding. For example, you have a debit that you want to apply against several invoices. Use a continuous form that allows you to check-off to apply.

Upon creating a debit entry, after you select the customer, the form displays all the outstanding invoices. The user selects / checks-off the invoices to apply the debit against. Logic makes sure the dollar amounts balance. This solution is more advanced since it will involve coding, a contuous form and/or and array.

Richard