Difficulties in forming Update statement in VBA

[Carl23]

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:

Private Sub Command31_Click()
'add Report Tracker info into Sheet#
   Dim strSql1 As String
   Dim tblname As String
   Dim MyValue As Integer
   MyValue = InputBox("Enter Table #", "MyInputbox")
   tblname = "Sheet" & MyValue
   strSql1 = "UPDATE [" & tblname & "] SET REPO_NUM = MyValue FROM ReportTracker.Report_Num, Report.Tracker.[Repo_Name], ReportTracker.[Facility_Name] WHERE MyValue = ReportTracker.Report_Num"
   DoCmd.RunSQL strSql1
End Sub

There seems to be an error in the UPDATE statement.

Thanks,
Carl

[Stewart Ross]

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?

-Stewart

[Carl23]

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.

Thanks,
Carl