Multiple records from one form!

[RobinL]

I have two tables:

ORDERS

Order Number (primary key)
Customer
Order date
Dispatched
Paid
etc

ORDER ITEMS

(Autonumber)
Order Number (related to order no in the other table)
Item


I want a form on which I can enter the data for order number, customer etc, and then, from combo boxes, select up to 8 items from the catalogue (the combo boxes are liked to another table with the catalogue)

this form then must create a new record in the ORSERS table, and multiple records in the ORDER ITEMS table (one for each item selected in the combo boxes).

Is this possible, and how could it be done?

Thanks a lot,

Robin

At the moment I have one form, with record source ORDERS, and then another embedded table ORDER ITEMS, with the combo boxes. however, I'm pretty new to acces in general. I figure I would need to write code to achieve my goal, and although I am ok at VD code, I dont really know where to start with access.

 

[PrittyMe143]

i don't think you need to access the VB code anymore. have you created the Relationship? if you did, then if you'll create the form using the wizard, automatically, it will update to the tables you have mentioned.

well, based on my experience... it worked.

 

[JustMaybe]

Yer that sounds about right. It should automatically update. Is there a reason your using combo boxes rather than a subform?

 

[Rich]

You should have a One to Many relationship between Orders and OrderDetails.
The form wizard will build the forms for you if your not sure how to do it manually

 

[RobinL]

All of my relationships are set up fine, with one to many relationships, I'm pretty sure that they are fine.

I don't think I have explained the situation very well. The file can be obtained from:

http://robin.netmag.easyspace.com/college/EP Solutions Database.mdb

the form I am working on is frmOrders. I think if you have a look at the tables and the form, you can see its a bit more complex than using the wizard. I could be wrong though.

I'd really appreciate it if someone could help - we haven't had much teaching on Access and we are expected to do our final year courseowork in it! I've tried books, but they don;t seem to go into enough detail.

My MSN robinlinacre@hotmail.com in case you think that would help.

Thanks a lot,

Robin

 

[sambo]

Make a MainForm, "frmOrders" and a SubForm, "frmOrderItems".

Include all the fields from your respective tables on these two forms.

In the BeforeInsert Event of the SubForm, "frmOrderItems" put this code...

Me.Order Number = Forms!frmOrders![Order Number]


... Doing this will insure that each newly added "Order Item" is properly associated with the Active Order Number.

Hope this helps

 

[RobinL]

oh, cool, thanks that was one thing that I wanted it to do