Populating an Invoice from an underlying table.

[Evon]

As simple as it sounds. I have set up a table with a list of items and the costs. I then created a form with a list comprising 10 combo boxes. I want to be able to select one record from the underlying table per combobox on the form.

I am able to do this but the records in the underlying table keeps switching to the primary key field as I go along using the combobox.

Any step-by-step how to do this would be appreciated.

 

[Uncle Gizmo]

From what you've said it appears you are going about it the wrong way.

I would suggest you have a look at the northwind example database supplied free by Microsoft as this shows how to set out an invoicing system.

 

[Evon]

Thanks Uncle, I have been doing that. However, haven't found any quick solution yet. The examples I was looking at are a little complicated.
In the meantime I will continue searching, but hopefully someone here will have a quick fix.

 

[Uncle Gizmo]

I'm not sure a "quick fix" will help you in the long run.

You've done the right thing in having a table to store the product details.

The next thing you need is an invoice table, this would store a record for every single invoice. And info like:- customer name, delivery address, date, basically information pertaining to the invoice.

Then you would have another table which is normally referred to as a "line item table" gets its name from the concept of having a single line for each item that is purchased in the invoice transaction.

Each entry in the invoice table would have a unique ID and this unique ID is duplicated multiple times in the "line item table" to associate the line item to the invoice that the item was purchased under.

MS Access is designed to make it very easy to produce this type of invoicing system basically the line items would appear in a subform on the main form and the main form would hold the address and date information and the like.

You can see all of this in the northwind example.

From your comment did you mean that you have the northwind example but find it difficult to understand how it works?

 

[Evon]

This is what I have. I want to use the comboboxes in the "InvoiceForm" to get data from the "Items" table.
It is a simple setup right now but I inted to add more features, data fields and formatting further down. Just want to clear this hurdle, and I should be good.

 

[Evon]

I have just updated the database as I just realized that some of the products in the Items Table were showing the ID Numbers instead of the item names, and this is the problem I have been having.

 

[Evon]

Okay, just customized the Northwind and that will serve the purpose. Thanks all.

 

[Uncle Gizmo]

The northwind database isn't made for production it's just an example and it does have a couple of problems you should be aware of. I did some videos on the problems a while back I will see if I can find them.

 

[Uncle Gizmo]

Here I demonstrate and show how to fix the problem with the Northwind database:-

Northwind Orders Combo Box: http://www.youtube.com/watch?v=uXcT0VDIiMA&list=PLhf4YcS5AjdrvMrsBlpFMHsB-ED4y39ax