VBA Code using INSERT SQL function in Access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hey out there,

I am having trouble getting Access to insert new entries into a table from
the main form. I found this VBA code online that does what I want and all of
the lines seem to run individually in the test window, but somehow, the code
does not run as a whole. Anybody see what I've done wrong?

Thanks a million, code below...

Option Compare Database

Private Sub Status_of_Publication_NotInList(NewData As String, Response As
Integer)
On Error GoTo Status_of_Publication_NotInList_Err
Dim intAnswer As Integer
Dim strSQL As String
intAnswer = MsgBox("The status," & Chr(34) & NewData & Chr(34) & "is not
an option currently." & vbCrLf & "Would you like to add it to the list now?",
vbYesNo + vbQuestion, "Oh, No, You Didn't!")
If intAsnwer = vbYes Then
strSQL = "INSERT INTO[Status of Publication](Status of Publication)"
+ "VALUES ('" & NewData & "');"
DoCmd.RunSQL strSQL
MsgBox "The status category has been added to the list.",
vbInformation, "Way to be, yo!"
Response = acDataErrAdded
Else
MsgBox "Please Choose a status category from the list.",
vbInformation, "You know you want to."
Response = acDataErrContinue
End If
Status_of_Publication_NotInList_Exit:
Exit Sub
Status_of_Publication_NotInList_Err:
MsgBox Err.Description, vbCritical, "Error"
Resume Status_of_Publication_NotInList_Exit
End Sub
 
If intAsnwer = vbYes Then
strSQL = "INSERT INTO[Status of Publication](Status of Publication)"
+ "VALUES ('" & NewData & "');"
Click to expand...

Do you REALLY have a field named the same as the table?? If so, I would change
it ASAP.

Two more things:

(1) Enclose it in square brackets BOTH times;
(2) Add some spaces -- one after "INSERT INTO" and another before the "(".
 
Yo dude,

Can you like, yo, post the error message that you are getting, word up? ;-)

The code on the surface seems to be correct - perhaps you need square
brackets around the ([Status of Publication]) like that.

Hope that helps, if not, post the error you are getting!!

Damian.
 
I put brackets around and checked out my spacing issues. I think that the
big success of the day was changing the names, though. I cannot believe it
works. Hallelujah.

Thanks!

Kp

Bob Hairgrove said:
If intAsnwer = vbYes Then
strSQL = "INSERT INTO[Status of Publication](Status of Publication)"
+ "VALUES ('" & NewData & "');"
Click to expand...

Do you REALLY have a field named the same as the table?? If so, I would change
it ASAP.

Two more things:

(1) Enclose it in square brackets BOTH times;
(2) Add some spaces -- one after "INSERT INTO" and another before the "(".
Click to expand...
 
Thanks, I put the brackets around and followed the other fellow's tips too
and it something worked cause I am adding entries left and right.

AMAZING.

Thanks,
kp

Damian S said:
Yo dude,

Can you like, yo, post the error message that you are getting, word up? ;-)

The code on the surface seems to be correct - perhaps you need square
brackets around the ([Status of Publication]) like that.

Hope that helps, if not, post the error you are getting!!

Damian.

Kirkpatti said:
Hey out there,

I am having trouble getting Access to insert new entries into a table from
the main form. I found this VBA code online that does what I want and all of
the lines seem to run individually in the test window, but somehow, the code
does not run as a whole. Anybody see what I've done wrong?

Thanks a million, code below...

Option Compare Database

Private Sub Status_of_Publication_NotInList(NewData As String, Response As
Integer)
On Error GoTo Status_of_Publication_NotInList_Err
Dim intAnswer As Integer
Dim strSQL As String
intAnswer = MsgBox("The status," & Chr(34) & NewData & Chr(34) & "is not
an option currently." & vbCrLf & "Would you like to add it to the list now?",
vbYesNo + vbQuestion, "Oh, No, You Didn't!")
If intAsnwer = vbYes Then
strSQL = "INSERT INTO[Status of Publication](Status of Publication)"
+ "VALUES ('" & NewData & "');"
DoCmd.RunSQL strSQL
MsgBox "The status category has been added to the list.",
vbInformation, "Way to be, yo!"
Response = acDataErrAdded
Else
MsgBox "Please Choose a status category from the list.",
vbInformation, "You know you want to."
Response = acDataErrContinue
End If
Status_of_Publication_NotInList_Exit:
Exit Sub
Status_of_Publication_NotInList_Err:
MsgBox Err.Description, vbCritical, "Error"
Resume Status_of_Publication_NotInList_Exit
End Sub
Click to expand...
Click to expand...
 
Back
Top