Looking for the most efficient way to update a table... 2

[irethedo]

I have a table called TotalOrder_tbl which contains order information.

In my database the user can select a button to display order information on a form
called Order_frm. On the Open form event for this form, an input box is used to allow the user to enter
an order number and then a query pulls all of the line items of that order from the table TotalOrder_tbl
and creates the table ModifyOrder_tbl.

Then, the table ModifyOrder_tbl is used as a record source for a form called Modify_frm which allows
the user to add, change, or delete items to the ModifyOrder_tbl.

I have a button on the Modify_frm called Update and I would like for this to take the contents of
the table ModifyOrder_tbl and replace the fields that match these in the table TotalOrder_tbl.

I am guessing that an update query would do this but not sure how to set this up.

Fields in the two tables:

[Pre]
TotalOrder_tbl:
ID Date OrderNum Line Item Qty

ModifyOrder_tbl:
ID Date OrderNum Line Item Qty

[/Pre]
The ID in TotalOrder_tbl is a primary key auto number which matches the ID in the Modify_tbl

Any suggestions?

Thanks

 

[MajP]

What is the purpose of creating ModifyOrder_tbl? Why do these records need to be put in a separate table? Seems overly complicated.

 

[irethedo]

MajP (TechnicalUser)19 Dec 17 04:58
What is the purpose of creating ModifyOrder_tbl?
Why do these records need to be put in a separate table?
Seems overly complicated.

Thanks for the reply MajP...

Perhaps my logic is a little twisted here but I am using a query to create a table of only the items belonging to a specific
order that is selected by the user.

That table is used as a record source for a form used by the user to edit the items for that specific order.

I chose to have the table as the record source rather than a query so that records are updated when the fields are
edited by the user but I wasn't sure if it was a good idea to use my main table for this as I intend this to be a multiple user
(front-end/ back-end) database (which might not make a difference.

So all I have left is to ask, what is a better way of doing this?

Thanks

 

[Andrzejek]

Eliminate ModifyOrder_tbl, do all your Selects and Updates on TotalOrder_tbl.
No copying data between tables needed.


---- Andy

There is a great need for a sarcasm font.

 

[irethedo]

Andrzejek (Programmer)19 Dec 17 13:09
Eliminate ModifyOrder_tbl, do all your Selects and Updates on TotalOrder_tbl.
No copying data between tables needed.

How do I only display the selected order on the form instead of the whole table?

 

[MajP]

How do I only display the selected order on the form instead of the whole table

https://msdn.microsoft.com/en-us/vba/access-vba/articles/docmd-openform-method-access

Look at the 4th argument.

 

[irethedo]

https://msdn.microsoft.com/en-us/vba/access-vba/ar...

Look at the 4th argument.

Thanks MajP.

I set the On load event for my form (Modify_frm) as:

Private Sub EditRecords_Click()
Dim PWD As String

PWD = InputBox("Please enter the sales Order Number that you wish to view...", "", Default, 100, 100)
DoCmd.OpenForm "Modify_frm", , , "SO_NO = " & PWD

End Sub

but I am not sure how to set the load form event code for the continuous subform (Modify_sfrm) which
holds the order information in order for it to only display the selected order items...

 

[MajP]

The subform should be linked to the master form by Sales order number. There should be no code required.