title: Update SQL in Access VBA -- Updating Table Values From Another Table
body: <p>I'm fairly new to Access VBA and SQL coding. So far, I've been able to find most of the answers to issues I've had by using the internet. I'm currently trying to write some code in MS Access (2013) VBA that updates the data in one table from another table in the same database when a particular form closes.</p>
  
  <p>I've worked out several errors so far, but I'm stuck on a syntax error in the "UPDATE" for SQLReplace. There could be other errors that I don't know about yet, but I'm not sure. Any help/Guidance would be greatly appreciated!</p>
  
  <p>Thanks!</p>
  
  <pre><code>Private Sub Form_Close()
  
  Dim SQLMove As String
  Dim SQLReplace As String
  
  Dim CountyCaseType As String
  Dim CaseNumber As String
  Dim County As String
  Dim FirstName As String
  Dim MiddleName As String
  Dim LastName As String
  Dim Facility As String
  Dim VOL As String
  Dim Diagnosis As String
  Dim AppearanceWaived As String
  Dim Dismissed As String
  Dim HearingResults As String
  Dim Disposition As String
  Dim DOB As String
  Dim Minor As String
  Dim Sex As String
  Dim ClerkName As String
  Dim Judge As String
  Dim CourtDate As String
  
  
  CountyCaseType = "Tables!tblTemp.CountyCaseType.Value"
  CaseNumber = "Tables!tblTemp.CaseNumber.Value"
  County = "Tables!tblTemp.County.Value"
  FirstName = "Tables!tblTemp.FirstName.Value"
  MiddleName = "Tables!tblTemp.MiddleName.Value"
  LastName = "Tables!tblTemp.LastName.Value"
  Facility = "Tables!tblTemp.Facility.Value"
  VOL = "Tables!tblTemp.VOL.Value"
  Diagnosis = "Tables!tblTemp.Diagnosis.Value"
  AppearanceWaived = "Tables!tblTemp.AppearanceWaived.Value"
  Dismissed = "Tables!tblTemp.Dismissed.Value"
  HearingResults = "Tables!tblTemp.HearingResults.Value"
  Disposition = "Tables!tblTemp.Disposition.Value"
  DOB = "Tables!tblTemp.DOB.Value"
  Minor = "Tables!tblTemp.Minor.Value"
  Sex = "Tables!tblTemp.Sex.Value"
  ClerkName = "Tables!tblTemp.Clerk.Value"
  Judge = "Tables!tblTemp.Judge.Value"
  CourtDate = "Tables!tblTemp.CourtDate.Value"
  
  SQLMove = "INSERT INTO tblCalendar SELECT * FROM tblTemp"
  SQLReplace = "UPDATE tblCalendar " &amp; _
                       "SET tblCalendar.CountyCaseType.Value = CountyCaseType, " &amp; _
                       "    tblCalendar.CaseNumber.Value = CaseNumber, " &amp; _
                       "    tblCalendar.County.Value = County, " &amp; _
                       "    tblCalendar.FirstName.Value = FirstName, " &amp; _
                       "    tblCalendar.MiddleName.Value = MiddleName, " &amp; _
                       "    tblCalendar.LastName.Value = LastName, " &amp; _
                       "    tblCalendar.Facility.Value = Facility, " &amp; _
                       "    tblCalendar.VOL.Value = VOL, " &amp; _
                       "    tblCalendar.Diagnosis.Value = Diagnosis, " &amp; _
                       "    tblCalendar.AppearanceWaived.Value = AppearanceWaived, " &amp; _
                       "    tblCalendar.Dismissed.Value = Dismissed, " &amp; _
                       "    tblCalendar.HearingResults.Value = HearingResults, " &amp; _
                       "    tblCalendar.Disposition.Value = Disposition, " &amp; _
                       "    tblCalendar.DOB.Value = DOB, " &amp; _
                       "    tblCalendar.Minor.Value = Minor, " &amp; _
                       "    tblCalendar.Sex.Value = Sex, " &amp; _
                       "    tblCalendar.ClerkName.Value = Clerk, " &amp; _
                       "    tblCalendar.Judge.Value = Judge, " &amp; _
                       "FROM tblTemp " &amp; _
                       "Where 'CourtDate = tblCalendar.CourtDate.Value'"
  DoCmd.SetWarnings False
  DoCmd.RunSQL (SQLMove)
  DoCmd.RunSQL (SQLReplace)
  DoCmd.SetWarnings True
  End Sub
  </code></pre>