Creating an Update Append Query from a button

[Beckiferanne]

Hi guys,

Hopefully I will explain what I've been trying to do properly! I have a query that creates a ranking for stores energy usage in different areas. Someone has asked me to include the previous ranking so they can check for improvements etc.

Now I have done the ranking part and created a table to store the previous ranking but I am struggling creating a query that updates the table. Ideally I would like it to check whether a store is there, if so update the previous ranking column and if not add the store. At the moment I am trying to be clever and do this from a button but it doesn't seem to be working! Can someone kindly point me in the right direction?

Private Sub UpdateAppend_Click()
On Error GoTo AddNewRecord
 
 
'Update the column Previous Ranking in the table
strSQL = "UPDATE tbl_[Temp - League Table Previous Rank] SET " & _
                    "PreviousRanking =  & "[Ranking], "
                    WHERE
'This part searches for whether the record is already there based on Town and Address
                    "([Reporting - League Table (Elec) 2].[Town])=" & [Town] & ") AND (" & _
                    "([Reporting - League Table (Elec) 2].[Address])=" & [Address] & ") ;"
 
DoCmd.SetWarnings (False)
DoCmd.RunSQL strSQL
DoCmd.SetWarnings (True)
Exit Sub
 
'If the record isn't there, the query will add it
AddNewRecord:
INSERT INTO tbl.[Temp - League Table Previous Rank],
Values ([Ranking],[Town],[Supplies],[Address],[Date Changed]) ,
SELECT [Reporting - League Table (Elec) 2].[Ranking] AS [PreviousRanking],  [Reporting - League Table (Elec) 2].[Town]  AS Town, [Reporting - League Table (Elec) 2].[Supplies] AS Supplies,[Address] AS Address;
 
DoCmd.SetWarnings (False)
DoCmd.SetWarnings (True)
 
 
End Sub

[zmbd]

Now I have done the ranking part and created a table to store the previous ranking

Does your data have a date/time field associated with it?
If so, then set one query to base the ranking on time period one and the second on time period two; thus, you do not need the VBA nor storage table. This is my number one suggestion!



Looking at your code:
"[Reporting - League Table (Elec) 2]"
Is a poor field name due to the dash and the parenthesis
-It is best practice when naming fields, tables, and files to avoid the use of anything other than alphanumeric characters and the underscore (spaces although allowed are problematic from a programing point of view and best avoided) and it is VERY importaint to avoid all reserved words and tokens:
Access 2007 reserved words and symbols
AllenBrowne- Problem names and reserved words in Access

Your first SQL string has quite a few typos - this fixes those:

strSQL = "UPDATE tbl_[Temp - League Table Previous Rank] SET " & _
                    "PreviousRanking =  " & [Ranking] & ", " & _
                    "WHERE " & _
                    "([Reporting - League Table (Elec) 2].[Town])=" & [Town] & ") AND (" & _
                    "([Reporting - League Table (Elec) 2].[Address])=" & [Address] & ") ;"
 

I suggest you take another look at your second block of SQL for the same types of errors.


Next

but it doesn't seem to be working! Can someone kindly point me in the right direction?

You've told us what you want to happen, which is much better than most posts ( d(^_^)b ); however, you've not told us about any errors or how it's not working; however, I'm sure that the typos in your SQL is certainly one major issue.
You should take a look at the basic troubleshooting found here:[*]> Before Posting (VBA or SQL) Code


--
If you insist on the table then I would look the execute method:
For an example of how to use this method; read thru the post: (How do I show just ONE warning while running multiple delete queries : Post#6 so that you have an idea of what you are looking at then open the vba editor.

If you would like to go this route (and I highly recommend this method if you insist on storing your past results), then post back any questions.