Adding Data to a Table USing a Macro in Forms

[ChrisSedgwick]

Hi,

I'm creating an Orders Management database and I need help adding data that has been input into a form, then into its respective table.

Example:

I'm adding a new Order to the database using a form I've created frmAddNewOrder. Within the form which has a form layout on the top half where the data entered will go directly into tblOrders. Then in the bottom half of the same form, a datasheet view of tblOrderDetails.

Within the form I have a command button called 'Add Items', this opens up a dialog form call frmAddItems. This is where the user will add multiple products along with any colours or other options.

What I then want the frmAddItems to include is a command button 'Add Item' which will add the selected items,along with its attibtues (colours etc) to the tblOrderDetails in their respective fields. I then want to add a 'Save and Close' command button then when clicked it refreshes the records so when I return to the frmAddNewOrder the details I've just entered are shown in the datasheet in the bottom half of the form.

Can anyone shed any light on what macro I need to use, if at all I need to use a macro? I hope I've explained my issue well enough. Please let me know if I haven't and what further information I need to offer.

Thanks,
Chris.

 

[CJ_London]

providing you have set your relationship up between the two tables and set the linkchild and linkmaster properties in your subform control (this will happen automatically if you have set your relationships before creating the subform control) then no code or buttons are required - the record will update as soon as you leave that record.

 

[CJ_London]

you might find the video tutorials here

http://www.access-programmers.co.uk/microsoft-access-tutorials/

to be of help

 

[ChrisSedgwick]

providing you have set your relationship up between the two tables and set the linkchild and linkmaster properties in your subform control (this will happen automatically if you have set your relationships before creating the subform control) then no code or buttons are required - the record will update as soon as you leave that record.

Thanks, I think I understand what your referring to. I did created my relationships before I began work on my forms etc. I've attached screenshots of both my relationships and sub-form properties. The 'Link Child' properties were set automatically, as you mentioned.

I've not added any orders yet, as I don't want it to mess up my auto numbering just for the sake a of a test. Although I will test the system before I put it to use.

Do the relationships and properties look OK to you. I know your only seeing a snapshot, however I do appreciate your guidance.

Thanks,
Chris

 

[CJ_London]

I've not added any orders yet, as I don't want it to mess up my auto numbering just for the sake a of a test.

autonumbers should have no relevant value to be presented, they merely identify a record. So you will 'mess up' the numbers the first time you create a new record and then cancel. If you want to reset, delete all the records and compact, that should reset the autonumbering to start at 1 again.

So far as I can see there is nothing wrong with the way you have your relationships set up, but I would consider adding a 'customer order number' to tblOrders rather than relying on the orderID.

It is a bit unusual (but not wrong) to have a composite primary key in tblOrderDetails - this does mean the customer can only order one product per order, which may be OK for your business but since colour is an option they will not be able to order the same product in different colours.

If you want them to be able to do so, then you need to extend the composite key to include colour. Looks like the same might apply to packaging.

Or more 'traditionally', you would add an autonumber OrderDetailID field and use that as the primary key.

 

[ChrisSedgwick]

I've created a new field in the tblOrdersDetails called 'OrderDetailsID' - will this now allow me to add the same product, in different colours to the same order (see attachment)?

Will I need to change anything in tblPackaging or tblColours as you mentioned, or will this 'traditional' way not require me to do that?

Were the 'Link Child' properties correct on my last post, regarding updating the inputting data to the relevant tables. All I need to do is just close the close the form, is this correct?

Thanks,
Chris

 

[CJ_London]

will this now allow me to add the same product, in different colours to the same order

they will

tblPackaging or tblColours

No, you don't need to change these

linkchild/master is correct

the row you are on will update when you close the form - subject to if any fields are required and not completed, the system will prompt the user to either complete the field or abort the update/insert.

Just some other thoughts - although it looks like your customers are consumers rather than businesses so some may not apply, but suggest you consider.

I still think you need a customer order number field in tblOrders - otherwise if the customer has multiple orders, how will you match each order to the customer order? Also, many customers require this anyway so they can match at their end - and they require it on the invoice.

Customers sometimes have different delivery addresses to invoice addresses - so you might want to consider having a separate address table - the customer record would link to this for the invoice address and the order table would link for the delivery address.

Customers sometimes have more than one contact so you might want to consider having a contact table - customerID, name, position, phone/mobile/email

Consumers often have delivery instructions, i.e. 'Leave in Shed', 'don't deliver after 5pm' so you may want to add this to the order table, either as a specific field or a general notes field

Sometimes orders have split deliveries, your design only allows for one. If this is the case, you need to create a delivery table which would link back to the orderdetails table and would contain as a minimum a delivery date and quantity - so you can part deliver

You may also want to consider delivery tracking, in which case you need a tracking number or other reference from your courier to include with the delivery information.

 

[ChrisSedgwick]

they will

I still think you need a customer order number field in tblOrders - otherwise if the customer has multiple orders, how will you match each order to the customer order? Also, many customers require this anyway so they can match at their end - and they require it on the invoice.

This is a good point, however I've created a form called frmCustomerOrdersList which includes two Sub forms (see attached). I was hoping as I navigate through the customers it would display all of their orders below in the sub forms, which are frmOrders and frmOrderDetails Sub form. Will this not work as I intended?

I thought each order a customer places would be attached to the 'OrderID' field which in turn would be displayed in the in the frmCustomerOrderList as I navigated through.

 

[CJ_London]

Will this not work as I intended?

It will work as intended - I'm just saying

a) your customer may have their own order number which they will want to see on any documentation - or you will need to quote to them if you talk to them

b) using an order number as you are is potentially fraught with problems if you don't want to have gaps in the numbers

As an experiment (take a copy of the db if you don't want to risk spoiling it), start to add a new order and the autonumber field will populate with 1, then hit the esc key to abort (perhaps the customer has changed their mind. or perhaps you have just realised you have added this order already), now enter another order - and the autonumber field will be populated with 2 - so there is now no order numbered 1

 

[ChrisSedgwick]

I've created a copy to make a test and when I try to add a product to an order I'm shown this error message...

"You cannot add or change a record because a related record is required in the table tblOrders."

Where might I start with this?

 

[ChrisSedgwick]

Solved - I wasn't refreshing the form before it was opened.

Thanks for you help.