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
  • 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 25

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...?
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

LVL 25

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 25

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 25

Accepted Solution

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

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 25

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

803 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