Form/Subform - cannot find matching key field(s)

[CQMMAN]

Hello, I have two tables:

tbInvoices (table name)
ID
transdate
InvoiceNum
PartNum
Quantity
SiteID
Notes

tblProducts (table name)
partnum
Partdesc

Both tables are joined with partnum.

I have a form with all of the tblinvoices fields on it, and a subform with
the tblproducts on it. I want to be able to create a new invoice record by
filling in the information in the fields, and then jump down to the subform
and be able to create a record that contains the tblinvoices.partnum value
and a description that I enter.

When I fill in the details and click on the subform, I get an error saying

"The microsft get engine cannot find a rcord in the table "tblproducts" with
key matching field(s) "tblinvoices.partnum".

I am not sure how to get around this. Perhaps they shouldn't even be in a
form-subform relationship?

help!

 

[Jeff Boyce]

I may not understand your particular situation, but it seems like something
is missing. When I think about invoices, I think about three "things". One
is the invoice, with information about date, to whom, etc.

A second is a list of items that might (MIGHT) be included as part of an
invoice.

The third is a unique combination of an item and an invoice -- i.e., an
"invoice detail" record.

Unless your situation's business rules require that an Invoice can ONLY be
for one item, consider rethinking your table design (before doing anything
else with forms).

Good luck

Jeff Boyce
<Access MVP>

 

[CQMMAN]

Thanks Jeff.

Yes you are correct, however the main point of this is going to be the
tracking of licences so the actual invoice is not really that important. It
would just be nice to know what invoice a particular licence was on.

There are definitely more strucutural changes that I will need to make, but
creating a simple form at least
allows me to make sure I can enter the detail in a format similar to how the
user (probably me!) will see it.

As for the form, I fixed the problem. I originally joined the two tables in
a query and based the form on that. The fix was to base the form on the two
tables, rather than on the query...


Cheers
CQMMAN