Can I use and UPDATE statement on a Click Event?

hbrehmer

Member
Local time
Today, 03:31
Joined
Jan 10, 2020
Messages
78
Hi All,
I have unique challenge. I have a main form that connects two subforms. Each subform get's it's data from 2 different queries/tables. But I have data in both forms that need to be updated into a different table. I want to use the UPDATE statement on a click button event. Am I on the right track with this statement? :


Private Sub TEST_UPDATE_Click()
Update "Order Details" 'My table to update

Set QtyShipped = [Forms]![frmCustShipment]![subCustOKShip].[Form]!Me![Qty2Ship] 'I want to update QtyShipped from Qty2Ship
PSID = [Forms]![frmCustShipment]![subCustShipment2].[Form]!Me![PSID] 'Same idea as line one
DateShipped = [Forms]![frmCustShipment]![subCustShipment2].[Form]!Me![ShipDate] 'Same idea as line one

FROM [Forms]![frmCustShipment] 'This is the main form that has both subforms that are getting the field data from

WHERE ODID = [Forms]![frmCustShipment]![subCustOKShip].[Form]!Me![ODID] 'this is the identifier that finds the exact record to update

End Sub

Or do I need to do two separate Updates from tables only after the records are saved?

Thank you for your input?

Heidi
 
Hi Heidi. Most of the time, you would use one query per table you want to update.
 
Each subform get's it's data from 2 different queries/tables. But I have data in both forms that need to be updated into a different table.

That's 2 tables that show data, and 2 others where you need to save data? That sounds weird and like you have a weird table structure.


Now on to code: VBA is the coding language of Access forms/reports/modules; SQL is the standard for all databases (ORACLE, MySQL, Access, SQL Server, etc.) for writing queries. When you want an Access form to do something you write VBA, when you want to do something with data inside a table (SELECT, UPDATE, INSERT, DELETE) you write SQL. That means if you want to have a button do something to a table (UPDATE) you must have the VBA code write SQL (the SQL is inside the VBA).

This also means you can't just write an SQL statement in VBA. You must construct a string which contains the SQL and then execute that string. In VBA you do that via DoCmd.RunSQL (https://docs.microsoft.com/en-us/office/vba/api/access.docmd.runsql). Check that link out.

The problem with your code is it isn't VBA, its syntatically incorrect SQL. Here's a starting point for yoru code that should work (although you should run it in a backup of your database because it will change your data and not in the manner you want):

Code:
Private Sub TEST_UPDATE_Click()
  ' updates [Order Details].QtyShipped 
  strSQL="UPDATE [Order Details] SET QtyShipped=" &  [Forms]![frmCustShipment]![subCustOKShip].[Form]!Me![Qty2Ship]
  ' SQL statement that will run on database 

  DoCmd.RunSQL strSQL
  ' executes SQL statement

End Sub

Again, do not run this on your actual database. Make a copy and run it there (No linked tables either). To get it to do what you want you must change strSQL to the correct SQL statement you want to execute.
 
As the others have already mentioned, what you are doing is almost certainly wrong. Best to step away from the keyboard and let us help you think through your problem rather than just giving you a code solution.
 
I can run another query with the two tables to get the data that is needed to update the inventory table I have.

As Plog mentioned, I may have too many tables. I have a PARTS table and a PART INVENTORY table that are linked by PARTID. I used the PARTS table to keep all the information about a part, and the PART INVENTORY table to keep all the transactions of parts in and out of inventory. I have structured my database similar to Northwind, but we are a manufacturer, not a seller. I have the routing of my order: Create an Order, which allows multiple parts to that Order, I create multiple "production tickets" for each part of the Order (tables are as follows: ORDERS, ORDER DETAILS, ORDER SCH DETAILS). Once the production ticket is closed from my ORDER SCH DETAILS, it adds parts to my PART INVENTORY. Everything works in my database up to this point.

Where I am troubled is with the printing of a packing slip to ship the parts to my customer and then updating the PART INVENTORY removing the parts shipped. Sometimes we ship all the parts made, other times we ship only what the customer ordered, leaving a balance in inventory. I'm not trying to make this difficult. I just have a company that doesn't want to by a canned ERP system. So with my limited programming abilities, I've been elected to create this monster. :)

Any advice would be appreciated.
 
Generally when you want to track inventory you do so by adding up the credits and debits to inventory in a transaction table. This is a crude example:

tblInventory
inv_ID, inv_Date, inv_Qty, Product_ID
1, 1/1/2020, 100, 2
2, 1/1/2020, 50, 7
3, 2/2/2020, -13, 2
4, 3/3/2020, -17, 2
5, 3/3/2020, -49, 7

Then when you want product inventory you run this simple query:

Code:
SELECT Product_ID, SUM(inv_Qty) AS Inventory FROM tblInventory GROUP BY Product_ID

Your system is essentially double storing data--once by itself then as a total. With the query no code is needed, just insert recrods into tblInventory and that query will take it into account and accurately calculate the inventory.
 
Generally when you want to track inventory you do so by adding up the credits and debits to inventory in a transaction table. This is a crude example:

tblInventory
inv_ID, inv_Date, inv_Qty, Product_ID
1, 1/1/2020, 100, 2
2, 1/1/2020, 50, 7
3, 2/2/2020, -13, 2
4, 3/3/2020, -17, 2
5, 3/3/2020, -49, 7

Then when you want product inventory you run this simple query:

Code:
SELECT Product_ID, SUM(inv_Qty) AS Inventory FROM tblInventory GROUP BY Product_ID

Your system is essentially double storing data--once by itself then as a total. With the query no code is needed, just insert recrods into tblInventory and that query will take it into account and accurately calculate the inventory.

I would normally agree with you, however, each "part" made can be held in inventory, shipped to a vendor for a secondary process, or placed on hold for future orders (more than one customer orders the part), so I have a different transaction ID code for each phase of the part. The owners want to see where the parts are and how many are in the inventory count overall. I have queries to keep track of each transaction type: 1)ADD from Production (which is handled at the production ticket level); 2) Hold for Order; 3) Off Hold; 4)Ready for Secondary; 5) Ready for Customer; 6) Distroy/Scrap; 7) Shipped; 8)Returned from Customer. If I didn't have to track where the parts were, this would be a lot simplier.
 
My crude example was just that. To accomodate your transaction types you would simply add a field to that table for it. Then instead of a query for each type you would add that transaction type field to the query and GROUP BY it.

Perhaps you can post a copy of your database or set up the Relationship Tool expand everything so we can see the fields adn then post a screenshot of it.
 
My crude example was just that. To accomodate your transaction types you would simply add a field to that table for it. Then instead of a query for each type you would add that transaction type field to the query and GROUP BY it.

Perhaps you can post a copy of your database or set up the Relationship Tool expand everything so we can see the fields adn then post a screenshot of it.

No worries. This database project is really taxing my programming abilities. I have many successes with the help from this group, but then I'm stumped in other areas. I am thinking of breaking down my inventory transactions into two different tables and query them for the inventory view on the part form. I would really like an opinion on my database, but it is already up to 4480 KB. I don't think I can post a copy here.
 

Users who are viewing this thread

Back
Top Bottom