how can I add multiple records(products) in one (order)form?

[sams]

Dear,

I am new to access 2007, had access 2003 basics at school. Did some additional reading (book: access 2007 for dummies, ms homepage/tutorials, forums) I want to create an sales/inventory database to make my life at work a lot easier! I’ve planned my db, wrote down everything I need. Then I created my tbl’s contacts/customers, products, orders, transactions. I linked the various tbl’s via relationships. Then I created my forms for data-entry. Now I’m stuck on the new-order form! I want to add multiple records (products) to one customer-order. I tried to use a sub-form made from the transaction tbl, but that didn’t work. Could anybody help me out with this problem? All help and advice is appreciated. I added the db that I am working on so you can take a look.

Thanks in advanced.

Sam

[maximus]

Check out this sample database may be this will help. This is a simple databese wit a main form and a subform. The Main Form reflects the order details and the subform records the products, more over it also displays the sub-form total on the main form.

[sams]

thank you very much for your reply.

that is what i was looking for!
the only thing is that every new product that i add gives it a new orderID.
is it possible that i can add multiple products with the same orderID?, which would make it easier to make a report (invoice) from the order and to track customer orders by orderID.

regards,

Sam

[maximus]

study the database properly. The relationships in particular. ClientId (AutoNmber PK) in the client table is Linked to client Id of Order( Number FK) the Order_ID in the order table is the primary key for ORder table.

So Follow this Structure.

Order Main table Linked with Order Sub Form.

[sams]

I got the order form almost working the way I want to, the only problem is when I add more than one product(record) to the customer-order all products change into the last selected product! Does anyone have a solution for this problem? Any advice is appreciated

Regards,

Sam

i added 2 pictures that display the problem.

[sams]

got it to work now

thanks for the help maximus!

[maximus]

This is what I have done. attaching a mdb file for your reference. i have prepared this very similar to what you want. check it out if this solves your problem mark the thread solved.

[sams]

thanks maximus for all your help.

i compared my db with the file you posted and it's almost 1 to 1 the same

[mcauleyj]

Hi Maximus,

I've just found your d'base "orderformproblem" a great solution for a d'base I'm trying to set up. My only issue is I can't tell how you get the order_link_ID in the Order_Sub table to self populate.

Can you shed any light on this for me please.

Thanks.

[daveirishman]

Hi Maximus,

If u have the time, I would really appreciate if u could share the answer to the above question asked by "mcauley"..
Your "orderformdatabase" is exactly what I need my database to do for a project I am currently working on, but I currently am at a loss trying to backward engineer exactly how it functions due to my lack of experience.

Much Appreciated

David
Ireland.

[maximus]

Hi Maximus,

If u have the time, I would really appreciate if u could share the answer to the above question asked by "mcauley"..
Your "orderformdatabase" is exactly what I need my database to do for a project I am currently working on, but I currently am at a loss trying to backward engineer exactly how it functions due to my lack of experience.

Much Appreciated

David
Ireland.

Check the Order_sub form. The situation is very simple. This code basically updates the price per unit in the Order_sub form table. This can be done in a simpler way:

Private Sub ProductsID_AfterUpdate()
Me.Price = Me.ProductPrice
End Sub

Instead of creating qryOrder_sub you can do the following:

1) Link Product_ID in the Order_Sub Table a Lookup Field and the Source should be Product Table. Make Sure to include all the Fields and hide the Product_ID and the Product price by setting the Column Widths:0;1;0 number of columns should be set to 3.
2) Make a sub form using Order_Sub table and insert the same in the Order_Main form. Do the necessary linking Order_ID and Order_Link_ID.
3) Attach the below mentioned code to the After Update Event of Procuct_ID Combo Box in Order Subform:

Me.Price=Me.ProductsID.Columns(3)

Note: Check Properties of the ProductsID Combo Box in the Sub Form and make sure to include all the Fields and hide the Product_ID and the Product price by setting the Column Widths:0;1;0 number of columns should be set to 3.

Let me know if this helped if you need any information or need help with you database feel free to contact me on my email.

[daveirishman]

Your reply was exactly what I needed Maximus, Thank you my friend!

[deborah kingston]

you solved my problem too thanks

[mormegil]

Hello,
I am also looking now for solution of this problem. I would like to study the solution discussed in this topic, yet I don't know where to find the database posted by Maximus. Could you please help me with this, share it somehow?

[maximus]

hello this is maximus. I am a seldom visitor now compared to what I was before when there were only handful of us solving problems. I am happy to see so many talented coders sharing their views. observe the relationship and the query diagram. if you need more help mail me a sample db. I will be glad to help you out.