DoCmd.RunSQL to update record calculating values from that record (1 Viewer)

Below is a line of code that trying to use...

DoCmd.RunSQL "Update [Deal Record] SET [Seller - Balance Paid]=" & ((([Seller - Invoice Amount] + [Seller - Swiss VAT]) / InvoiceAmountAfterVAT) * (Me.AlreadyPaid + Me.PaymentAmount)) & " WHERE [Seller - Invoice Reference]='" & Me.InvoiceNumber & "'"

This updates all the records where the [Seller - Invoice Reference] equals the value from the form Me.InvoiceNumber all ok. However... this updates the values for the [Seller - Balance Paid] with the values for [Seller - Invoice Amount] and [Seller - Swiss VAT] from the first record in that table and not the specific record it's updating.

I want the update to update the values based on the balance for that field for the record that it's updating and not the first record in the table.

Tried a few options and searched but not found the answer.
Thanks for any help!!

This reeks of a spreadsheet mindset. Databases are a different animal. Less is more in this world.

First, you generally don't store values in a table--instead you calculate them when you need them. Anything you can logically determine from your data (e.g. Age from Date of Birth, Balance from transactions, etc.) you don't calculate and store, you simply calculate.

Second, you don't use field names to store data values. Anytime you start prefixing or suffixing fieldnames with a common value, that common value should be a value in a field, not the name of a field. So, all those 'Seller -' fields probably shouldn't exist, instead you would have a _Type field and the value 'Seller' would go into it and apply to that entire recrod. .

I reallly think what you are posting about now is a symptom of a larger problem and you should address that. Can you complete the Relationship tool in Access and post a screenshot so we can see your tables and their fields? I would properly structure your tables before moving forward.

I can't deny that there is almost certainly a lot of that - come from a spreadsheet background. And a fair bit of VBA.

The deal record table has lines for each "deal" that we do. Each of these deals can be invoiced to both a buyer and a seller. Then when invoice there is an invoice created to a counterparty that could be for multiple deals/records. The balance on that invoice total but the split between that balance and the balance on each deal within that invoice has no direct link and needs manual intervention. Therefore calculation as such isn't a simple matter.

That's why was hoping to use the update to put in the value against that record from the total invoice amount, but this amount can later be independently changed.

Have attached the relationships - but actually not all shown at-all here as quite a few I have that use linked Combi boxes etc on forms and VBA update, so although the data isn't duplicated the links aren't all shown in reality.

Thanks

Jon

Yeah, you really need to read up on normalization:

https://en.wikipedia.org/wiki/Database_normalization

You've commited the 2 sins I listed above a few times over. Essentially it comes down to you do not have enough tables to accomodate your data. [Deal Record] should probably be at least 3 tables with a lot less fields overall.

For example all that broker data goes to a new table without the word 'Broker' in any field name. Most likely that new table looks like this:

BrokerDeals
BrokerDealID, autonumber, primary key of table
DealRecordID, number, foreign key to id in Deal Record table
BrokerName, ?, holds data currently in [Broker X] fields
BrokerPct, ?, holds data currently in [Broker X Pct] fields
BrokerConfirmed, ?, holds data currently in [Broker X Confirmed] fields
BrokerNumber, number, will hold number suffixed in field names now if needed

That's it, just 6 fields instead of 12. However, it would have 4 records (one for each Broker Number) instead of just the one record currently in Deal Record.

That's just one example, there are others where this approach applies ([Seller - ], [Buyer - ] fields).

Again, read up on normalization (https://en.wikipedia.org/wiki/Database_normalization) and give it a shot with properly structuring Deal Records.

While I can't disagree - this is a working database that I can't (for risks to the business) make wholesale changes to the format.

Some of what you say couldn't work (but the majority is fair).

From where I am here and now though was hoping that if a more generic piece of code:

DoCmd.RunSQL "Update [Deal Record] SET [Balance Paid]=" & (([Invoice Amount] + [Swiss VAT]) / Me.PaymentAmount) & " WHERE [Invoice Reference]='" & Me.InvoiceNumber & "'"

Where Me.Payment amount comes from the form. And the balances Invoice Amount and Swiss VAT are those applicable to the individual record in [Deal Record] that being changed... is there any way that can get the update to pick the values in that record being updated rather than the values in the first record in the database as currently doing.

jrp1007 said:

this is a working database that I can't (for risks to the business) make wholesale changes to the format.

Click to expand...

To follow on a bit from what Plog is saying... He's giving you one of the standard messages that we often give to Excel users moving to MS Access. It's not a small problem you are facing, it's not a difference in working Styles. What it is, is the beginnings of a major pain in the ass...

You can carry on in the style you are, "flat file" and many do... As your database becomes more and more advanced you will run into more and more problems. Now if you're pretty good VBA then you're going to be able to work around these problems to some extent, but eventually you're going to realise that you should have change the structure right at the beginning. I can practically guarantee it... For more information have a read of my blog here:-

http://www.niftyaccess.com/excel-in-access/

where I provide text, images and videos explaining the problem. I also provide a tool to help solve the problem. If you want a copy of the tool for free email me and I will explain how you can get it for free...

...this is a working database that I can't (for risks to the business) make wholesale changes to the format...

Click to expand...

Car analogy #1: You don't fix a flat on a moving car. My advice is to read up on normalization, open a new database file, structure your tables to accomodate your data, make sure your existing data "fits" into the new database and then move it once you know everything will work.

Car analogy #2: You've driven/pushed your car into a mechanic shop, told him it barely starts, backfires horribly when it does, black smoke comes out of a few places while driving and then told him you only want to use duct tape and coat hangers to make it work. He explains all the things that needs to happen to fix your car correctly, but you say the duct tape/coat hanger fix is good enough because you only need to get down the road 2 miles for now. He might actually be able to do that, but he knows that in 2 miles you'll be stuck again trying to add more duct tape and coat hangers to fix something else.

But I like duct tape...

Minty said:

But I like duct tape...

Click to expand...

I do too, I used to love writing complicated VBA to fix problems with an ill structured database. But eventually the fun goes out of it, your monitor is crazed Like a backstreet shop window from you banging your head against it. You, yourself are crazed by the sleepless nights due to constantly modifying the VBA code. The nagging doubt that you should have started doing it properly in the first place, but you keep going... until, one day you crack!!! You realise that you've just got to start from scratch and do it properly... How do I know this? Yes, been there, done that, never again!