UPDATE WHERE SQL - Advice.

[JacobSP]

Hey guys.

This is my first proper thread to Access Forums after my introduction one so here goes!

I have a table in my database called (RiskForm1) and it has many fields in it one of which is called TotalImpact. Now I have some SQL/VBA code earlier in my project which inserts values into all my fields on my table from an excel spreadsheet, other than TotalImpact which is left empty (deliberately).

Now I have a text box on my form called TotalImpactText and then a button which once pressed should update the table with the value in the TotalImpactText box. This is done this way as it needs to be a manual value placed into the table after the rest of the records have been inputted into the table.

My question is, how do I go about updating the TotalImpact field in the table for the LAST record placed into the RiskForm1 table? Here is some code I already have:

Private Sub ImportManual_Click()
Dim UpdateSQL As String

 UpdateSQL = "UPDATE RiskForm1 SET " & _
                "TotalImpact = !TotalImpactText.Text" & _
                "WHERE TotalImpact = "

                DoCmd.RunSQL UpdateSQL, dbFailOnError

Now obviously I need to set a WHERE statement otherwise every field in my table will become the value inside the textbox, so, is my code close at all to what I need? I feel im very close but just need a little guidance.

Any help is appreciated, thanks guys.

Jake

 

[spikepl]

I smell something fishy here - such as Excel design permeating into your db, just because that is where you get your data from.

Total Impact sounds like a sum (runnning sum?) , that you are sticking onto one record among many, "for the LAST record", even though it concerns all the records. It is therefore a derived value that belongs to whatever it is that binds the specific records together, and does not belong in those records.

Can you show a snapshot from the Relations window, and explain the fields and what this is about?

 

[JacobSP]

You are right about the excel design, but I dont think its the underlying problem as there isnt really a problem, its just my knowledge doesnt stretch far enough with VBA to know how to WHERE the last row of a table. Total impact has to be inputted manually using the text box and button because on the excel spreadsheet it is calculated by a macro.

The reason I wish for it to update the last record is becuase once the selected Excel spreadsheet has been loaded in, all of its worksheets are then populated into a list box, the user then chooses the worksheet to import and then clicks "import selected worksheet" button. Runs the code and inserts the values from the excel worksheet into the table and everything works fine. Then after the table has been updated the user HAS to input the manual Total Impact value into the text box and then press the "import manual values" button. Thats why I wish to update the last row in the table as I know that has to be the row they will be putting the value in because they have just created it with the previous insert. Does that make more sense? I thought that updating a record with one value from a textbox would be much simpler, but after hours of trying it seems not!

And to sum the database up it is an access application that allows health and safety risk managers to insert excel spreadsheets which are then stripped of their values and placed into a table. The excel spreadsheets are all the same so the insert is always right but just happens to find different values depending on what they have inserted into the excel file before hand. They include fields such as the name of the risk, the risk numbers, descriptions of the risk, addresses etc. Thanks again for the quick reply.

 

[spikepl]

The bit about getting Excel into Acces I did get. For what purpose? What is the objective of this work cycle?

IS the data to be kept in the future? Will you want to run any calculations/statistics/reporting on it? OR expand the capabilities?

The reason I am asking is that you have not dispelled my sense of fishy smells

Access /VBA can do calculations too - some of them are easier in Excel, but it still can.

 

[JacobSP]

Damn the fishy smells!

Once the data has been put into the tables it will then be reported upon, the Risk Excel Sheets are used by the company all year round and are their templates for writing details about their health and safety jobs and 3 maybe 4 times a year they will all be imported into this access database and then reported upon for future reference.

 

[spikepl]

So my nose did not deceive me then. If you want to do further processing of data, you should not let Excel dictate how data is stored internally. Storage of data and display/input of it are two different things in a database, as opposed to in Excel.


Anyways: If you wish to ignore the invaluable advice that my oracular proclivities force me to dispense, then if your form is bound to the table then you just need to move the form to the last record - look up the DoCmd.GotoRecord. And then your button could simply put the value in the desired field of that record.

 

[JacobSP]

Thank you Spike for all your help. I will have a look at the DoCmd.GotoRecord and report with my success.

If you have the time, would you mind elaborating on what you mean by allowing Excel to dictate how my data is stored? As the job goes I dont really have much freedom, I am only hired to write the form for easy storing of the data/reporting. But if there is anything I can suggest to the company then maybe I will do so. Extra scooby snacks for Jacob?

 

[spikepl]

Ppl tend to recreate Excel sheets in Access, which is like throwing the baby out with the bathwater. On an Excel sheet you often have a bunch of columns that contain same type of info, just for different times, or units or whatever - and that is a major killer of easy processing, if reproduced in a database.

In your case, you have some data identifying the specific sheet, of which some may be repeated across sheets during the year, and some rows of data, in which one field is "special". Logically, the value you stuff in the total belongs to the entire case, not one specific record. It is just an Excel-cheat that ppl do it like they do it, because it is the easiest.


But I am rambling. Without any more specific info it is hard to say what would ideally be the best. As long as you do not need to do much with your data, you can live with what you have. If you later decide to drop the Excel bit, and let each user input data directly, then you would definitely want to design a proper data structure, and not just copy Excel.

 

[Pat Hartman]

its just my knowledge doesnt stretch far enough with VBA to know how to WHERE the last row of a table

Technically, there is no such thing as the last row of a table. Tables are unordered sets of data therefore order has no meaning. Every row is equal to every other row. And at any given time any row could be the last row of a table..

Calculated values are rarely (and this case isn't an exception) stored in tables. If you want a total, calculate it in a footer on a report or create a totals query to calculate it as needed.

If you want to learn SQL syntax, use the QBE to build queries and then switch to SQL view to examine the string that the QBE created.