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.
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!
Thanks!
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 " & _
"SET tblCalendar.CountyCaseType.Value = CountyCaseType, " & _
" tblCalendar.CaseNumber.Value = CaseNumber, " & _
" tblCalendar.County.Value = County, " & _
" tblCalendar.FirstName.Value = FirstName, " & _
" tblCalendar.MiddleName.Value = MiddleName, " & _
" tblCalendar.LastName.Value = LastName, " & _
" tblCalendar.Facility.Value = Facility, " & _
" tblCalendar.VOL.Value = VOL, " & _
" tblCalendar.Diagnosis.Value = Diagnosis, " & _
" tblCalendar.AppearanceWaived.Value = AppearanceWaived, " & _
" tblCalendar.Dismissed.Value = Dismissed, " & _
" tblCalendar.HearingResults.Value = HearingResults, " & _
" tblCalendar.Disposition.Value = Disposition, " & _
" tblCalendar.DOB.Value = DOB, " & _
" tblCalendar.Minor.Value = Minor, " & _
" tblCalendar.Sex.Value = Sex, " & _
" tblCalendar.ClerkName.Value = Clerk, " & _
" tblCalendar.Judge.Value = Judge, " & _
"FROM tblTemp " & _
"Where 'CourtDate = tblCalendar.CourtDate.Value'"
DoCmd.SetWarnings False
DoCmd.RunSQL (SQLMove)
DoCmd.RunSQL (SQLReplace)
DoCmd.SetWarnings True
End Sub
There are several potential errors in your code:
You do not need to add .Value to the end of an attribute to get its actual value.
As you are working directly in Access, you to not need the Tables! part either. That is the syntax used when dealing with recordsets. For example, write tblTemp.CountyCaseType instead of Tables!tblTemp.CountyCaseType.Value
The values of your variables are not in the SQL string. You have to concatenate them to the SQLReplace String using [&]. For example, write
SQLReplace = "UPDATE tblCalendar " & _
"SET tblCalendar.CountyCaseType = " & CountyCaseType & ", " & _
" tblCalendar.CaseNumber = " & CaseNumber & ", " & _
....
As @AlanHadsell pointed out, remove the single quotes from the WHERE clause.
Where 'CourtDate = tblCalendar.CourtDate.Value'
should be
WHERE CourtDate = tblCalendar.CourtDate
But as I said in 3) CourTDate is a String variable, so it needs to be concatenated. Your final WHERE clause should be:
"WHERE " & CourtDate & " = tblCalendar.CourtDate"
You don't need the FROM tblTemp clause in the SQLReplace String.
EDIT: As @Parfait pointed out, tblTemp does not exist in scope of the SQLReplace statement. You should do an INNER JOIN to fix that:
UPDATE tblCalendar INNER JOIN tblTemp ON tblCalendar.CourtDate = tblTemp.CourtDate SET ...
After fixing everything, your final code should look like:
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 = "tblTemp.CountyCaseType"
CaseNumber = "tblTemp.CaseNumber"
County = "tblTemp.County"
FirstName = "tblTemp.FirstName"
MiddleName = "tblTemp.MiddleName"
LastName = "tblTemp.LastName"
Facility = "tblTemp.Facility"
VOL = "tblTemp.VOL"
Diagnosis = "tblTemp.Diagnosis"
AppearanceWaived = "tblTemp.AppearanceWaived"
Dismissed = "tblTemp.Dismissed"
HearingResults = "tblTemp.HearingResults"
Disposition = "tblTemp.Disposition"
DOB = "tblTemp.DOB"
Minor = "tblTemp.Minor"
Sex = "tblTemp.Sex"
ClerkName = "tblTemp.Clerk"
Judge = "tblTemp.Judge"
CourtDate = "tblTemp.CourtDate"
SQLMove = "INSERT INTO tblCalendar SELECT * FROM tblTemp"
SQLReplace = "UPDATE tblCalendar " & _
"INNER JOIN tblTemp ON tblCalendar.CourtDate = tblTemp.CourtDate " & _
"SET tblCalendar.CountyCaseType = " & CountyCaseType & ", " & _
" tblCalendar.CaseNumber = " & CaseNumber & ", " & _
" tblCalendar.County = " & County & ", " & _
" tblCalendar.FirstName = " & FirstName & ", " & _
" tblCalendar.MiddleName = " & MiddleName & ", " & _
" tblCalendar.LastName = " & LastName & ", " & _
" tblCalendar.Facility = " & Facility & ", " & _
" tblCalendar.VOL = " & VOL & ", " & _
" tblCalendar.Diagnosis = " & Diagnosis & ", " & _
" tblCalendar.AppearanceWaived = " & AppearanceWaived & ", " & _
" tblCalendar.Dismissed = " & Dismissed & ", " & _
" tblCalendar.HearingResults = " & HearingResults & ", " & _
" tblCalendar.Disposition = " & Disposition & ", " & _
" tblCalendar.DOB = " & DOB & ", " & _
" tblCalendar.Minor = " & Minor & ", " & _
" tblCalendar.Sex = " & Sex & ", " & _
" tblCalendar.ClerkName = " & Clerk & ", " & _
" tblCalendar.Judge = " & Judge
DoCmd.SetWarnings False
DoCmd.RunSQL (SQLMove)
DoCmd.RunSQL (SQLReplace)
DoCmd.SetWarnings True
End Sub
To finish, instead of declaring a String variable for each attributes in tableTemp that you want to copy, and then assigning some values to them, you can simply omit the declarations and put the attributes dicrectly in the SQL. That will geatly reduce the length of your code as follow:
Private Sub Form_Close()
Dim SQLMove As String
Dim SQLReplace As String
SQLMove = "INSERT INTO tblCalendar SELECT * FROM tblTemp"
SQLReplace = "UPDATE tblCalendar " & _
"INNER JOIN tblTemp ON tblCalendar.CourtDate = tblTemp.CourtDate " & _
"SET tblCalendar.CountyCaseType = tblTemp.CountyCaseType, " & _
" tblCalendar.CaseNumber = tblTemp.CaseNumber, " & _
" tblCalendar.County = tblTemp.County, " & _
" tblCalendar.FirstName = tblTemp.FirstName, " & _
" tblCalendar.MiddleName = tblTemp.MiddleName, " & _
" tblCalendar.LastName = tblTemp.LastName, " & _
" tblCalendar.Facility = tblTemp.Facility, " & _
" tblCalendar.VOL = tblTemp.VOL, " & _
" tblCalendar.Diagnosis = tblTemp.Diagnosis, " & _
" tblCalendar.AppearanceWaived = tblTemp.AppearanceWaived, " & _
" tblCalendar.Dismissed = tblTemp.Dismissed, " & _
" tblCalendar.HearingResults = tblTemp.HearingResults, " & _
" tblCalendar.Disposition = tblTemp.Disposition, " & _
" tblCalendar.DOB = tblTemp.DOB, " & _
" tblCalendar.Minor = tblTemp.Minor, " & _
" tblCalendar.Sex = tblTemp.Sex, " & _
" tblCalendar.ClerkName = tblTemp.ClerkName, " & _
" tblCalendar.Judge = tblTemp.Judge"
DoCmd.SetWarnings False
DoCmd.RunSQL (SQLMove)
DoCmd.RunSQL (SQLReplace)
DoCmd.SetWarnings True
End Sub
Remove the single quotes from "Where 'CourtDate = tblCalendar.CourtDate.Value'"
