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...?
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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

Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

789 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