Programatically updating second table based on search results of first table

[hrreece]

I have an Access 2002 database that has a form that can be used to review individual records. At the bottom of the form are buttons that are linked to functions that allow the user to "Find a record using search criteria", "Delete the current record" and so on. After a user has used the search criteria to find a specific record, I would like to use the "delete" function on the form to not only delete the record, but also update another table. I want to do this programatically rather than developing a parameter query where the user would have to enter the values manually found in the search result. When the delete button is selected it would use the value from one of the fields in the current table's search result as a criteria for updating a different field in another table. The value that is updated in the second table is a calculated value equal to the value found in the field before the update + the value found in a field of the record that is to be deleted.

I have used some macros in the past and have limited understanding of VBA programming.

Thanks in advance for any assistance you can provide.

[nico5038]

Looks like you're trying to create a dangerous system.
By deleting records that hold a value, no tracing can be done wether or not the value in "the other table" is correct.
Basically that value is deductable from the records, when they wouldn't have been deleted.
The safest way is to "forget" the correction of a field in the other form and to use e.g. a "DateDeleted" on the records found by the user. Just filter the form to show records with an empty "DateDeleted" and for the user it looks like the record has disappeared, but it will allow you to record the user that did the deletion too, incase someone might wonder what happened with some data....
To get the needed summation, just create a GroupBy query to Sum all "Deleted" rows.

Nic;o)

[hrreece]

Thanks for the quick reply. The delete function is only used when an incorrect record has been entered. The way the program is currently set up is that once a record is recorded a report is printed automatically showing the recorded information. On occassion there is a need to cleanse the master table of an incorrect record. Since one of the fields in the master table is related to another table, I must update a field in the second table to reflect the deletion of the record in the main table. I have been unable to write an update query for the second table that uses the search results from the first table as the criteria and one of the fields from that query result as a variable that needs to be added back to a field in the second table.

[nico5038]

This does imply that you're storing calculated data in a tablefield. Somewhere when the incorrect record is inserted there must be a function that adds (subtracts?) the value to the "field in the other table", else you wouldn't need a correction....

Basically your table design is against the normalization rules and the need for an update here reveals why we normalize tables. When you have a normalized tabledesign, the "field in the other table" wouldn't exist, but would be calculated in a query when needed.

As Access tables can be processed "outside" forms, a direct delete of a row in the table would corrupt your figures.....

Getting the idea ?
Can you still change your tabledesign ?

Nic;o)

[hrreece]

The application was one that was developed several years ago and I am sure does not follow the best normalization rules throughout the entire database. Over the years there have been add-on's etc that also too have probably moved the original design away from the best methods and practices for databases.

With this being said, the users of this application are NOT access literate and use the forms and functions embedded in them to run the application to track customer transactions. The transaction table (Transmas) includes various fields with one of the fields being a "contract number" (ContractNo). Not all transactions have a contract number, but when one does the price for an item is not pulled from a regular price schedule located in a separate table but from a price field located in the contract table for the item as long as the quantity purchased does not exceed the allowed "running" total for the contract. Once a customer purchases an amount equal to what was originally contracted, the price for subsequent purchased items reverts to the regular price. In a separate table (Contracts), details can be found in fields for the contract such as the "Contract Number" (ContractNo), "Price" (ContractPrice), "Initial Contracted Quantity" and "Contract Quantity left" (ContractRemain). The only like fields between the two tables is Contract Number (ContractNo) which can be found in both the Transmas and the Contract tables. When a customer purchases an item using the contract price, the quantity of that item is deducted from Contract Quantity Left field located in the Contract table. This field represents a running total for the contract and is the field that the Transmas table "looks-up" when determing what price to use for the transaction.

When an data entry person notices that a mistake has been made after a transaction is recorded, they need to have a way to update the (ContractRemain) field in the Contract table. Presently they use the delete function to remove the incorrect record from the Transmas table after searching the Transmas table. They then must remember to add back the number of items purchased in the deleted transaction to the (ContractRemain) field in the Contract table to match the value before the incorrect transaction was added. My desire was to build a function, macro etc that as part of the delete step would call up the appropriate record in the Contract table and then automatically add back to the specific contract a revised ContractRemain value via a calculated field.

Hopefully, this provides additional detail.

[nico5038]

Well described, you're creating a fine systemdescription this way :-)

For changing the value in the other table we'll need an UPDATE query.
Syntax:

UPDATE <tablename> SET <fieldname> = <new value>
 

Best to add the needed code "behind" the [Delete] button on the form as you'll have to use the value before deleting the record.
The code would look like:

' correct the value
currentdb.execute ("UPDATE Contract SET ContractRemain = ContractRemain 
- " & [number of items purchased] & " WHERE ContractNo=" & Me.ContractNo)
' delete the record
currentdb.execute ("DELETE * FROM Transmas WHERE ContractNo=" & Me.ContractNo)
' show it
Me.Requery
 

Best to make first a copy of the database for testing ! Also make sure that [number of items purchased] holds the correct fieldname.

Nic;o)

[hrreece]

We are getting so close to success I can taste it. When I try to run the following update statement before the delete command I get a message from Microsoft access that there is "a syntax error in the update statement". I have looked at the code several times and can not find the error. Please note that the field names shown in the attached are not the same as in earlier posts, but are indeed the correct field names used in the tables/

CurrentDb.Execute ("UPDATE Contracts SET Bushels Remaining = Bushels Remaining + " & [Totbush] & " Where ContractNo=" & Me.ContractNo)

Thanks for your assistance

[nico5038]

Your fieldname has an embedded space, thus [ and ] is mandatory like:

CurrentDb.Execute ("UPDATE Contracts SET [Bushels Remaining] = [Bushels Remaining] + " & [Totbush] & " Where ContractNo=" & Me.ContractNo)
 

Guess you know now why I NEVER use spaces in fieldnames, just capitalization works visually the same: BushelsRemaining

Nic;o)

[hrreece]

Thank you. The code worked perfectly. I have also learned some valuable tips that will help me in the future. I can now quit pulling my hair out for at least awhile!!!

Roger

[nico5038]

Glad I could help before you turned bold :-)

Success with your application !

Nic;o)