Unable to update records on master form

Posted on 2013-10-31
Medium Priority
Last Modified: 2013-11-29
I want to update 2 tables of the same data. below is the command

DoCmd.RunSQL "update BL-PI_mst set BL =  """ & Me.newBLno & """ where BL =  """ & Me.currenbl & """"
DoCmd.RunSQL "update shipment_TB BL =  """ & Me.newBLno & """ where BL =  """ & Me.currenbl & """"

shipment_TB is the subform and BL-PI_mst is main form.

I want to update both the tables from this form with the same data.

how this can be done?
Question by:MAS
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39614235
Still troubled as to why you need to update two tables with the same data?

In a typical relational database , updating need only occur in one place,....
LVL 27

Author Comment

ID: 39614558
I ve a shipment tracking system in which I get details of the shipment first  and we get the tracking no (BoL) later.
I will assign a temp tracking number on 2 tables. Once I get the shipment BoL no  I update the BoL number by the above command.
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39615903
Again, in a properly related system, updating two tables with the same data is not needed.

Can you explain the  difference between these two tables, and explain clear why this is the only way to do what you are asking...?
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

LVL 27

Author Comment

ID: 39616360
Shall I upload the db? so that u will have an idea
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 2000 total points
ID: 39616644
Sure, follow these general guidelines:

1. Back up your database(s).
2. If the database is split, combine the front and back ends into one database file.
3. Remove any startup options, unless they are relevant to the issue.
4. Remove any extraneous records unless they are relevant to the issue.
5. Delete any objects that do not relate directly to the issue.
6. Remove any references to any "linked" files (files outside of the database, Images, OLE Files, ...etc)
7. Remove any references to any third party Active-x Controls (unless they are relevant to the issue)
8. Remove, obfuscate, encrypt, or otherwise disguise, any sensitive data.
9. Unhide any hidden database objects
10. Compile the code. (From the VBA code window, click: Debug-->Compile)
11. Run the Compact/Repair utility.
12. Remove any Passwords, Security and/or login prompts.
13. If a form is involved in the issue, set the Modal and Popup properties to: No
    (Again, unless these properties are associated with the issue)
14. Post the explicit steps to replicate the issue.
15. Test the database before posting.

In other words, ...post a database that we can easily open and immediately see and/or troubleshoot the issue.
Post the explicit steps to see the issue.
And if applicable, also include a clear, graphical representation of the *Exact* results you are expecting, based on the sample data.

Ultimately I need to understand why you need to update a single value to two separate tables.

For example, if you have a Customers table and an Orders table.
The Customer Name is only stored in the Customer table.
But you can "display" the customer name on an Order form by using a query that joins these two tables together.

Now when a Customer changes their name, it is only changed in the Customer table, and hence the new name will be reflected in the query and the form.

LVL 27

Author Comment

ID: 39618486
Uploaded db.
just open only the belwo forms for you to understand
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39626064
<just open only the below forms for you to understand>
I understand what you want but I still believe your design here is flawed.
Your main form has the same recordsource as the first subform?
None of your relationships has Referential Integrity established?

Again, in a standard relational database there is no need to add the same value to two separate tables.
And I see nothing in your design that warrant you to violate this basic principle...
As far as I can tell, the main table would be like a Customer, then the first subform would be like an Order, then the third subform would be like the Order line items.
In this case there are related parent /Child records
In this case you first add the customer to the main form, then add the order to the first subform (Orders) and the Cutsomer ID is automatically added to the first subform.
Then you create an Order Line Item, and the "Order ID" (Not the customerID) is automatically added the second subform.

So here we have Customers linked to Orders via the CutsomerID.
Then the Orders are linked to the Line Items by the Order ID.
In this basic setup you enter the CustomerID only once, and it can be inserted into the subform automatically, ...and also you do not need this same field (CustomerID) in Order Line Items, ...it can be "Displayed" in the Line Items form if you use a query.

So instead of just saying what you want, please explain the relationship between the main form and the two sub forms.

LVL 27

Accepted Solution

MAS earned 0 total points
ID: 39643610
I created a new table withe same structure.
Now its working fine
LVL 27

Author Comment

ID: 39672992
I created a new tablw with the same structure

Appreciate if you could explain why it worked in new table
LVL 27

Author Closing Comment

ID: 39684947
I created anew database with the structure and it worked. Not sure how it worked.

Appreciate if you could explain why it updated in new table.

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question