INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

(OP)

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:

TotalOrder_tbl:
ID	Date	OrderNum Line	Item	Qty

ModifyOrder_tbl:
ID	Date	OrderNum Line	Item	Qty

 
The ID in TotalOrder_tbl is a primary key auto number which matches the ID in the Modify_tbl

Any suggestions?

Thanks

RE: Looking for the most efficient way to update a table...

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

RE: Looking for the most efficient way to update a table...

(OP)

Quote:


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

RE: Looking for the most efficient way to update a table...

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.

RE: Looking for the most efficient way to update a table...

(OP)

Quote:

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?

RE: Looking for the most efficient way to update a table...

(OP)

Quote:


Thanks MajP.

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

CODE

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...


RE: Looking for the most efficient way to update a table...

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close