Solved

Unable to update records on master form

Posted on 2013-10-31
10
584 Views
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?
0
Comment
Question by:-MAS
  • 6
  • 4
10 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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,....
0
 
LVL 24

Author Comment

by:-MAS
Comment Utility
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.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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...?
0
 
LVL 24

Author Comment

by:-MAS
Comment Utility
Shall I upload the db? so that u will have an idea
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 500 total points
Comment Utility
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.

JeffCoachman
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 24

Author Comment

by:-MAS
Comment Utility
Uploaded db.
just open only the belwo forms for you to understand
Dataentry
BL-PI_details
Temp_BoLs
upload.accdb
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
<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.


JeffCoachman
0
 
LVL 24

Accepted Solution

by:
-MAS earned 0 total points
Comment Utility
I created a new table withe same structure.
Now its working fine
0
 
LVL 24

Author Comment

by:-MAS
Comment Utility
I created a new tablw with the same structure

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

Author Closing Comment

by:-MAS
Comment Utility
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.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

762 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now