Two SQL statements in form -- UPDATE works; INSERT doesn't

[Rachel Garrett]

I am trying to use SQL to update two tables based upon the user
updating a value in a form. One is working; when the user changes a
value, the value in the table changes appropriately. With the second
SQL statement, nothing seems to happen. The difference is that I am
using an INSERT statement in for the one that doesn't work. Can anyone
tell me if there is something wrong with the following syntax?

Private Sub Milestone_1_target_date_AfterUpdate()

'mySQL2 appends historical information to [Record of Target Date
Changes]
Dim mySQL2 As String
Dim myAssessmentDate As String
myAssessmentDate = Format(Date, "MM/DD/YYYY")

mySQL2 = "INSERT INTO [Record of Target Date Changes]"
mySQL2 = mySQL2 + " ( [QNameMilestoneAssmDate], [Question Name],
[Milestone], [Target date pre-assessment], "
mySQL2 = mySQL2 + "[New target date after assessment], [Assessment
date] )"
mySQL2 = mySQL2 + " VALUES ('TestingKey', 'Testing QName', 1, "

'1/1/2020 is dummy data for testing purposes.

mySQL2 = mySQL2 + "#1/1/2020#, #" + myString
mySQL2 = mySQL2 + "#, #" + myAssessmentDate + "# 1/1/2020# )"

'Hide VB's automatic warning messages about running SQL on a button
click

DoCmd.SetWarnings False
DoCmd.RunSQL mySQL2

'Re-enable warnings after SQL is done running
DoCmd.SetWarnings True

End Sub

 

[Rachel Garrett]

Correction - I made some copy/paste errors. This is the code:

 

[Steve Sanford]

Hi Rachel,

There was a missing quote in front of the last "#" the line;

mySQL2 = mySQL2 + "#" + myAssessmentDate + #)"


The only other thing I can see is it looks line you are trying to put a
string in a date field??

You have a field named [QNameMilestoneAssmDate] which implies this is a
date, but the data is 'TestingKey'.

I modified your code some. I added error handling and changed the "+" to "&".

Try running this:

'----code beg-------
Private Sub Milestone_1_target_date_AfterUpdate()
On Error GoTo HandleError

'mySQL2 appends historical information to [Record of Target Date Changes]

Dim mySQL2 As String
Dim myAssessmentDate As String
Dim db As DAO.Database

Set db = CurrentDb
myAssessmentDate = Format(Date, "MM/DD/YYYY")

mySQL2 = "INSERT INTO [Record of Target Date Changes]"
mySQL2 = mySQL2 & " ( [QNameMilestoneAssmDate], [Question Name],
mySQL2 = mySQL2 & " [Milestone], [Target date pre-assessment], "
mySQL2 = mySQL2 & "[New target date after assessment], [Assessment date] )"
mySQL2 = mySQL2 & " VALUES ('TestingKey', 'Testing QName', 1, "

'1/1/2020 is dummy data for testing purposes.

mySQL2 = mySQL2 & "#1/1/2020#, # 1/1/2020#, "
mySQL2 = mySQL2 & "#" & myAssessmentDate & "#)"

Debug.Print mySQL2

' Insert the record
db.Execute mySQL2, dbFailOnError

HandleError_Exit:
Exit Sub

HandleError:
MsgBox Err.Number & " - " & Err.Description
Resume HandleError_Exit

End Sub
'----code end-------


Let me know....

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Correction - I made some copy/paste errors. This is the code:

Private Sub Milestone_1_target_date_AfterUpdate()

'mySQL2 appends historical information to [Record of Target Date
Changes]
Dim mySQL2 As String
Dim myAssessmentDate As String
myAssessmentDate = Format(Date, "MM/DD/YYYY")

mySQL2 = "INSERT INTO [Record of Target Date Changes]"
mySQL2 = mySQL2 + " ( [QNameMilestoneAssmDate], [Question Name],
[Milestone], [Target date pre-assessment], "
mySQL2 = mySQL2 + "[New target date after assessment], [Assessment
date] )"
mySQL2 = mySQL2 + " VALUES ('TestingKey', 'Testing QName', 1, "

'1/1/2020 is dummy data for testing purposes.

mySQL2 = mySQL2 + "#1/1/2020#, # 1/1/2020#, "
mySQL2 = mySQL2 + "#" + myAssessmentDate + #)"

'Hide VB's automatic warning messages about running SQL on a button
click

DoCmd.SetWarnings False
DoCmd.RunSQL mySQL2

'Re-enable warnings after SQL is done running
DoCmd.SetWarnings True

End Sub