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

Difficulties in forming Update statement in VBA

P: 22
I would like to update the table, tblname, to take in other columns and data from the ReportTracker Table so I created the following code:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command31_Click()
  2. 'add Report Tracker info into Sheet#
  3.    Dim strSql1 As String
  4.    Dim tblname As String
  5.    Dim MyValue As Integer
  6.    MyValue = InputBox("Enter Table #", "MyInputbox")
  7.    tblname = "Sheet" & MyValue
  8.    strSql1 = "UPDATE [" & tblname & "] SET REPO_NUM = MyValue FROM ReportTracker.Report_Num, Report.Tracker.[Repo_Name], ReportTracker.[Facility_Name] WHERE MyValue = ReportTracker.Report_Num"
  9.    DoCmd.RunSQL strSql1
  10. End Sub
There seems to be an error in the UPDATE statement.

Mar 29 '12 #1
Share this Question
Share on Google+
2 Replies

Expert Mod 2.5K+
P: 2,545
Hi Carl. I have split this into a new question following on from this thread.

You are confusing the syntax of the update statement, with FROM introduced where it is not necessary amongst other things. You have not implemented what I mentioned in my last post in the previous thread, that you need to include the value of variable MyValue - you cannot reference the variable by name in the SQL string.

Could you explain your requirements clearly, telling us what fields you wish to update and to what values?

Mar 29 '12 #2

P: 22
FYI, I am a pharmacy consultant to nursing homes and am actually using Access to import my reports that are generated in Excell to separate out the physician comments in a separate report. What I am trying to do is automate the process so the user can enter a minimal amount of information. That is why I am tring to import my Excel files as Sheet1, Sheet2 and not call them by their Excel file name ie Name of Facility, Name of Unit and Date...

So what I have come up with is a ReportTracker Table in Access that keeps track of the Report_Date1, Report_Date2, Report_Date3 since we may visit a unit more than once a month, a default populated Facility_Name (each nursing home has there own Access file since we may write over 100 reports in the larger homes, the nursing unit and the field that tracks the Sheet #, Report_Number.

The other tables are imported from Access. As, I mentioned I am calling them Sheet1, Sheet2,etc. Their fields are Resident, Physician, Comment, Physician_Comment, and REPO_NUM. The Join can be made between the tables using REPO_NUM and ReportTracker.Report_Num.

Once the tables (Sheet1, Sheet2, etc) are appended, I plan to use the table to use them as a record source in each nursing units monthly report.

Mar 29 '12 #3

Post your reply

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