creating data entry form from multi-table query

[spence]

I'm having difficulty creating a data entry form from a simple select query
and I can't for the life of me figure out why. I have the following:

tblVendor
[VendorID]
[VendorName]

tblEmployee
[EmployeeID]
[EmployeeName]

tblInvoice
[InvoiceID]
[VendorID]
[EmployeeID]
[Amount]

My goal is to create a simple data entry form where I can enter new records
into tblInvoice but can use VendorName and EmployeeName rather than having to
enter the VendorID and EmployeeID respectively. The query returns all my
records but when I create a form based on it I am able to edit records but
not create new ones. (It just beeps at me if I try to add a field in the new
record at the bottom of my form.) I've tried it with "Data Entry" set to both
yes and no and neither one works.. This seems simple but it's got me stymied.

thanks.

 

[Allen Browne]

Trying to enter new records into multiple tables using the one form is
generally an exercise in frustration.

Better to create a form for each one.
Where appropriate, you can use subforms, e.g. a main form bound to
tblVendor, with a subform bound to tblInvoice.

I'm guessing that your example is a simplification of your actual case. For
a real-world scenario, you always need a tblInvoiceDetail to handle the line
items on the invoice, so the interface would be a main form bound to
tblInvoice with a subform bound to tblInvoiceDetail.

You would probably use combos on the main form for selecting ghe VendorID
and EmployeeID. You can use the DblClick event of those combos to open the
respective forms for adding more vendors/employees, and then use the
AfterUpdate event of those forms to Requery the combo so it hears about the
new record.