Update SQL in Access VBA -- Updating Table Values From Another Table

Question

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

Rethink your processing. You update from the very table you just imported? Also, FROM clause is not allowed in Access SQL update statements. Look into UPDATE ... INNER JOIN. — Parfait
– Parfait, Commented Jan 21, 2016 at 2:47
Thanks for the feedback! I'm using tbltemp in connection with a form that I have -- I don't want tblCalendar to be directly linked to the forml. The process is: 1. All Information is stored in tblCalendar 2. When a particular form is opened, I have code that tells access to copy data from the tblCalendar into tblTemp, but it only grabs the data that is associated with a particular court date. 3. Anyone using the form can now view and edit only the information for the court date they indicated (and they can add additional data). So far, everything works. My issue is going the other way. — Brian H
– Brian H, Commented Jan 28, 2016 at 16:08

Oscar Anthony · Accepted Answer · 2016-01-21 14:22:28Z

1

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

edited Jan 21, 2016 at 14:22

answered Jan 20, 2016 at 21:26

Oscar Anthony

1905 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Oscar Anthony Over a year ago

@Parfait Good point! I confused with the SQLMove query. I will change that in my answer. Thanks

Oscar Anthony Over a year ago

oh my... @Parfait thank you for reviewing it with me! I put a space before it.

Brian H Over a year ago

Wow, thank you for all of that feedback. I've actually been away from work for a few days, so I haven't had a chance to review any of the responses. I'll let you know what happens when I get back into my code. Thanks!

Brian H Over a year ago

I've made all the suggested changes and I'm happy to say that there aren't any errors for the code. Thanks to all involved! Unfortunately, when I close the form no changes are reflected in tblCalendar. I'm working on figuring out why, but any suggestions would be greatly appreciated. I'm thinking it might relate to the Primary Key for both tables -- not sure though.

Oscar Anthony Over a year ago

@BrianH Try to put the code in the UserForm_QueryClose() procedure of your form instead of UserForm_Close

|

Alan Hadsell · Accepted Answer · 2016-01-20 21:25:49Z

0

Remove the single quotes from "Where 'CourtDate = tblCalendar.CourtDate.Value'"

answered Jan 20, 2016 at 21:25

Alan Hadsell

4702 silver badges7 bronze badges

Collectives™ on Stack Overflow

Update SQL in Access VBA -- Updating Table Values From Another Table

2 Answers 2

9 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related