Unable to update records on master form

Posted on 2013-10-31
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...?
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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 500 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, 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, 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses
Course of the Month10 days, 23 hours left to enroll

628 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