By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,623 Members | 1,892 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,623 IT Pros & Developers. It's quick & easy.

Creating an Update Append Query from a button

P: 1
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?

Expand|Select|Wrap|Line Numbers
  1. Private Sub UpdateAppend_Click()
  2. On Error GoTo AddNewRecord
  3.  
  4.  
  5. 'Update the column Previous Ranking in the table
  6. strSQL = "UPDATE tbl_[Temp - League Table Previous Rank] SET " & _
  7.                     "PreviousRanking =  & "[Ranking], "
  8.                     WHERE
  9. 'This part searches for whether the record is already there based on Town and Address
  10.                     "([Reporting - League Table (Elec) 2].[Town])=" & [Town] & ") AND (" & _
  11.                     "([Reporting - League Table (Elec) 2].[Address])=" & [Address] & ") ;"
  12.  
  13. DoCmd.SetWarnings (False)
  14. DoCmd.RunSQL strSQL
  15. DoCmd.SetWarnings (True)
  16. Exit Sub
  17.  
  18. 'If the record isn't there, the query will add it
  19. AddNewRecord:
  20. INSERT INTO tbl.[Temp - League Table Previous Rank],
  21. Values ([Ranking],[Town],[Supplies],[Address],[Date Changed]) ,
  22. 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;
  23.  
  24. DoCmd.SetWarnings (False)
  25. DoCmd.SetWarnings (True)
  26.  
  27.  
  28. End Sub
Apr 30 '14 #1
Share this Question
Share on Google+
1 Reply


zmbd
Expert Mod 5K+
P: 5,397
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:
Expand|Select|Wrap|Line Numbers
  1. strSQL = "UPDATE tbl_[Temp - League Table Previous Rank] SET " & _
  2.                     "PreviousRanking =  " & [Ranking] & ", " & _
  3.                     "WHERE " & _
  4.                     "([Reporting - League Table (Elec) 2].[Town])=" & [Town] & ") AND (" & _
  5.                     "([Reporting - League Table (Elec) 2].[Address])=" & [Address] & ") ;"
  6.  
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.
Apr 30 '14 #2

Post your reply

Sign in to post your reply or Sign up for a free account.