Windows 7 32bit 
Access 2003
I have a simple database for my business. I want to generate invoices.
There are 2 tables - customers and invoices. The customer table has fields for the customer's name and billing address. It has an autonumber field (customer number) as a primary key.
The invoice table has fields for the customer's first and last name, the customer number, description of work, and pricing info (labor, materials, etc). It has an autonumber field (invoice number) as a primary key.
They have a one-to-many relationship. (customers being the "one")
I would like to use a form to enter new invoices to the invoice table. I would like to select a customer's last name (I think from a combo box - this is part of the question) and have all of their billing info appear on the form. I would then enter new invoice info - description of work, prices, etc - into the new invoice record. I think the record source for the form would have to be the invoices table because that's where the new invoice info is going...is that right?
My experience in Access so far is I've created various simple tables, queries with various criteria, forms, and reports. I've used a combo box on a form to select a record and display the various fields for the selected record. I've changed properties of various objects and controls, tried a few simple macros. I have not ventured into VBA yet.
Where I'm stuck is when I want to add a new invoice table record, I can't seem to be able to use the combo box to select a record from the customers table and populate the fields in the new invoice form. From other posts and forums I've looked at, it seems to relate to row sources and the table record source - this is where I'm confused. Also, is using a combo box to select a customer the best way to get the data - ALL of the billing info, not just the last name - from the customers table?
In summary, I would like to use a form to add a new record to the invoice table and have the customer's billing info (address, etc - which are fields in the customer table) appear on the form when I select their last name from a list. Or as a result of typing in their last name with an auto-complete type of function. I would then proceed to enter in the invoice data into the fields from the invoice table.
Once the new invoice table record is created, I would like to do the same in a report - pull the billing info from the customer table and invoice details from the invoice table.
Thank you. I hope this is enough information and that I've described the problem clearly.
Reply With Quote
