Insert Into not working

[jackie77]

Hi all

Hoping someone can help me with this, I have a check box on a form that after update I want to insert details into a table

CurrentDb.Execute "INSERT INTO Communication([CommunicationType],[Date],[JobID],[MethodReportedID], [Notes])VALUES ('22', Now(), " & JobID & ", '6', ' Comment ');"

It kind of works but instead of coping what the user has entered into the inputBox it simply appears as "Comment" I have tried typing this in various ways but can't seem to call the string Comment


Any help would be appreciated

Cheers

Jackie

[FONT=Calibri][SIZE=3]Private Sub AccountHold_AfterUpdate()[/SIZE][/FONT]
[FONT=Calibri][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Calibri]      Dim Response As Integer[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]      Dim Comment As String[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]      [/FONT][/SIZE]
[FONT=Calibri][SIZE=3]     [/SIZE][/FONT]
[FONT=Calibri][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Calibri]      ' Displays a message box with the yes and no options.[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]      Response = MsgBox(prompt:="Do you wish to update the Job Record and log the status change? 'Yes' or 'No'.", Buttons:=vbYesNo)[/FONT][/SIZE]
[FONT=Calibri][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Calibri]      ' If statement to check if the yes button was selected.[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]      If Response = vbYes Then[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]                If Me.AccountHold = -1 Then[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]                    Comment = Inputbox("Please enter why account is on Hold?")[/FONT][/SIZE]
[FONT=Calibri][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Calibri]                        CurrentDb.Execute "INSERT INTO Communication([CommunicationType],[Date],[JobID],[MethodReportedID], [Notes])VALUES ('22',   Now(), " & JobID & ", '6', ' Comment ');"[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]                        MsgBox "The Status has been changed and recored" & Chr(13) & Chr(10) & " and the record has been updated successfully!"[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]                        Me.StatusID = 29[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]                        Forms![JobForm1].Refresh[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]                        End If[/FONT][/SIZE]
[FONT=Calibri][SIZE=3]                        [/SIZE][/FONT]
[SIZE=3][FONT=Calibri]                If Me.AccountHold = 0 Then[/FONT][/SIZE]
[FONT=Calibri][SIZE=3]                [/SIZE][/FONT]
[SIZE=3][FONT=Calibri]                 Comment = InputBox("Please enter why account Status is changing?")[/FONT][/SIZE]
[FONT=Calibri][SIZE=3]      [/SIZE][/FONT]
[SIZE=3][FONT=Calibri]                          CurrentDb.Execute "INSERT INTO Communication([CommunicationType],[Date],[JobID],[MethodReportedID],[Notes])VALUES ('23',   Now(), " & JobID & ", '6', 'Comment');"[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]                        MsgBox "The Quote has been sent to the Printer" & Chr(13) & Chr(10) & " and the record has been updated successfully!"[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]                        Me.StatusID = 11[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]                        Forms![JobForm1].Refresh[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]                        End If[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]                        Exit Sub[/FONT][/SIZE]
[FONT=Calibri][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Calibri]      If Response = vbNo Then[/FONT][/SIZE]
[FONT=Calibri][SIZE=3]      [/SIZE][/FONT]
[SIZE=3][FONT=Calibri]        Me!AccountHold.Undo[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]        MsgBox "PLEASE NOTE - The account status has not been changed!!"[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]        End If[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]        Exit Sub[/FONT][/SIZE]
[FONT=Calibri][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Calibri]    End If[/FONT][/SIZE]
[FONT=Calibri][SIZE=3]End Sub[/SIZE][/FONT]

 

[pbaldy]

You have to concatenate the Comment variable just as you did JobID, except surrounding it with quotes. A tutorial here that covers several options:

http://www.baldyweb.com/BuildSQL.htm

 

[jackie77]

Hi Paul

Thanks for the reply, I had a look at the tutorial cheers this has given be a better understanding as I have not used insert commands in vba before

I have tried various

"&Comment&" - Comes back Error To few Parameters
'&Comment&' - inserts &Comment&

Not sure where I'm going wrong

Regards

Jackie

 

[pbaldy]

At the simplest:

...'" & Comment & "')"

 

[jackie77]

Works perfect - You are fab, been working on that for ages your help really is most appreciated

Thanks so much again
Jackie

 

[pbaldy]

Happy to help Jackie!