Add Record using SQL (1 Viewer)

thudson

Old Programmer
Local time
Today, 05:25
Joined
Apr 29, 2011
Messages
68
I am designing a database to add greetings card verses to a database.
Attached is the relationship between the tables.

I have writen an Event procedure, but it says 'Invalid Operation' (Error Number 3219) in the following code on the strSQL line:
Code:
Private Sub Cmd_Add_Rec_Click()

 Dim dbs As Database
 Dim strSQL As String
 
 Set dbs = CurrentDb
 
 strSQL  = "INSERT INTO `Verses` (`EventID`, `MoodID`, `Verse`) VALUES  ('Forms.[AddRecords]![Cbo_Event_Type]','Forms.[AddRecords]![Cbo_Mood_Type]','Forms.[AddRecords]![Txt_Verse]')"
 
 Set rsSQL = dbs.OpenRecordset(strSQL, dbOpenDynaset)
 
 Debug.Print "Recordsets"
 
 End Sub
I am not very experience with Visual Basic, so I am not sure if the syntax is correct.
Can anyone see what I am doing wrong?
 

Attachments

  • Relationships.JPG
    Relationships.JPG
    21.9 KB · Views: 40

sneuberg

AWF VIP
Local time
Yesterday, 22:25
Joined
Oct 17, 2014
Messages
3,506
You don't want the single quotes around the table and field names and you need to concatenate the form references into the string. So it should be something like:

Code:
strSQL  = "INSERT INTO Verses (EventID, MoodID, Verse) VALUES ( '" & Forms.[AddRecords]![Cbo_Event_Type] & "','" & Forms.[AddRecords]![Cbo_Mood_Type] & "','" Forms.[AddRecords]![Txt_Verse] & "')"
and to run it

Code:
dbs.Execute strSQL, dbFailOnError

you don't need to open a record set

if this doesn't work put in a Debug.Print strSQL statement to see how the SQL is formed
 

sneuberg

AWF VIP
Local time
Yesterday, 22:25
Joined
Oct 17, 2014
Messages
3,506
I suggest you consider binding your form to the table and see how that work for you before going through the hassle of doing your own inserts, updates and deletes. The ability to connect a form to a table this way is really one of the main advantages of Access. If it weren't for that I'd consider dumping Access all together and using C# and MYSQL
 
Last edited:

thudson

Old Programmer
Local time
Today, 05:25
Joined
Apr 29, 2011
Messages
68
Hi Sneuberg
Copied your line into the module and removed the line:
Set rsSQL = dbs.OpenRecordset(strSQL, dbOpenDynaset)
and replaced with: dbs.Execute strSQL, dbFailOnError

The strSQL = line is highlighted in blue and says there is a syntax error.

Looks OK to me?

Also, the 2 combo boxes on the form are unbound, but the Row source is the Events table and is bound to column 1 which is the EventID column.
Will this put the ID number into the Verses table, when I get this to work?
 

sneuberg

AWF VIP
Local time
Yesterday, 22:25
Joined
Oct 17, 2014
Messages
3,506
I didn't notice the periods after Forms which should be exclamation points try

Code:
strSQL  = "INSERT INTO Verses (EventID, MoodID, Verse) VALUES ( '" & Forms![AddRecords]![Cbo_Event_Type] & "','" & Forms![AddRecords]![Cbo_Mood_Type] & "','" Forms![AddRecords]![Txt_Verse] & "')"

If this doesn't work. Put a Debug.Print strSQL in the code just after the line above and copy and paste what you get in the immediate window to a post.
 

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 22:25
Joined
Aug 30, 2003
Messages
36,118
I didn't notice the periods after Forms which should be exclamation points try

Code:
strSQL  = "INSERT INTO Verses (EventID, MoodID, Verse) VALUES ( '" & Forms![AddRecords]![Cbo_Event_Type] & "','" & Forms![AddRecords]![Cbo_Mood_Type] & "','" Forms![AddRecords]![Txt_Verse] & "')"

If this doesn't work. Put a Debug.Print strSQL in the code just after the line above and copy and paste what you get in the immediate window to a post.

There's an ampersand missing before the last form reference.
 

sneuberg

AWF VIP
Local time
Yesterday, 22:25
Joined
Oct 17, 2014
Messages
3,506
With pbady's catch that would be

Code:
strSQL  = "INSERT INTO Verses (EventID, MoodID, Verse) VALUES ( '" & Forms![AddRecords]![Cbo_Event_Type] & "','" & Forms![AddRecords]![Cbo_Mood_Type] & "','"  & Forms![AddRecords]![Txt_Verse] & "')"
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 13:25
Joined
May 7, 2009
Messages
19,169
strSQL = "INSERT INTO Verses (EventID, MoodID, Verse) VALUES (" & Forms.[AddRecords]![Cbo_Event_Type] & "," & Forms.[AddRecords]![Cbo_Mood_Type] & ",'" & Forms.[AddRecords]![Txt_Verse] & "')"

since both EventID and MoodID are numeric.
 

thudson

Old Programmer
Local time
Today, 05:25
Joined
Apr 29, 2011
Messages
68
Right!
Now getting 'Object doesn't support this property or method.'

So is the code wrong to set up the connection to the database?

Heres the code:
Code:
Option Compare Database

Private Sub Cmd_Add_Rec_Click()

 Dim dbs As Database
 Dim strSQL As String
 
 Set dbs = CurrentDb
 
strSQL = "INSERT INTO Verses (EventID, MoodID, Verse) VALUES (" & Forms.[AddRecords]![Cbo_Event_Type] & "," & Forms.[AddRecords]![Cbo_Mood_Type] & ",'" & Forms.[AddRecords]![Txt_Verse] & "')"
 dbs.Execute strSQL, dbFailOnError
 
 Debug.Print "Recordsets"
 
 End Sub

Any ideas?
 

MarkK

bit cruncher
Local time
Yesterday, 22:25
Joined
Mar 17, 2004
Messages
8,178
As an alternative, you can add data using a QueryDef created on the fly, and in that case your delimiters are handled, which is much more reliable because this method will not fail if you user adds quotes and/or double quotes in a verse. Like, your insert statement will fail if there is a verse with a line like . . .
Code:
if e'er I see her face again
. . . that single quote will kill your SQL.
Code:
Function InsertEventFromForm(frm As Form_AddRecords) As Boolean
    Const SQL_INSERT As String = _
        "INSERT INTO Verses " & _
            "( EventID, MoodID, Verse) " & _
        "VALUES " & _
            "( p0, p1, p2 )"
    
    With CurrentDb.CreateQueryDef("", SQL_INSERT)
        .Parameters(0) = frm.[Cbo_Event_Type]
        .Parameters(1) = frm.[Cbo_Mood_Type]
        .Parameters(2) = frm.[Txt_Verse]
        .Execute dbFailOnError
        InsertEventFromForm = .RecordsAffected
        .Close
    End With
End Function
Yes, this is more verbose, but considerably more reliable, as an option anyway.

Cheers,
 

thudson

Old Programmer
Local time
Today, 05:25
Joined
Apr 29, 2011
Messages
68
Is this code put in the command button event or as its a function, do I create a module and call the function?
I am relatively new to VBA so can you advise how to call it?
 

MarkK

bit cruncher
Local time
Yesterday, 22:25
Joined
Mar 17, 2004
Messages
8,178
The code posted previously can go in a standard module as a public function.

If you do the insert in the command button event handler you would not have to pass in a reference to the form, since the command button is on the form. In that case the code might be . . .

Code:
Private Const SQL_INSERT As String = _
     "INSERT INTO Verses " & _
         "( EventID, MoodID, Verse) " & _
     "VALUES " & _
         "( p0, p1, p2 )"

Private Sub cmdInsert_Click() 
    With CurrentDb.CreateQueryDef("", SQL_INSERT)
        .Parameters(0) = Me.[Cbo_Event_Type]
        .Parameters(1) = Me.[Cbo_Mood_Type]
        .Parameters(2) = Me.[Txt_Verse]
        .Execute dbFailOnError
        .Close
    End With
End Function
Makes sense?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 13:25
Joined
May 7, 2009
Messages
19,169
you have left out a double qoute on your sql string.
i have trouble using markk code, always "datatype conversion error".
 

thudson

Old Programmer
Local time
Today, 05:25
Joined
Apr 29, 2011
Messages
68
Obviously an error, but where should the double quote go, if one were missing, surely there would be an odd number of double quotes, but I count 8??
The error I get: Expected: Expression
Any ideas?
 

MarkK

bit cruncher
Local time
Yesterday, 22:25
Joined
Mar 17, 2004
Messages
8,178
Sorry, are you talking to me? If there is an error can you say at what line it occurs? If you have modified or customized the code, please post the exact code you are using.
Thanks,
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 13:25
Joined
May 7, 2009
Messages
19,169
not tested:
Code:
Private Sub Cmd_Add_Rec_Click()

 Dim dbs As Database
 Dim strSQL As String
 Dim lngEvent As Long
 Dim lngMood As Long
 Dim varText As Variant
 
 lngEvent = Nz(Me!Cbo_Event_Type.Value, 0)
 lngMood = Nz(Me!Cbo_Mood_Type.Value, 0)
 varText = Me!Txt_Verse.Value
 
 Set dbs = CurrentDb
 
strSQL = "INSERT INTO Verses (EventID, MoodID, Verse) SELECT " & lngEvent & ", " & _
            lngMood & ", " & IIf(IsNull(varText), "Null", "'" & varText & "'") & ";"
 dbs.Execute strSQL, dbFailOnError
 
 Debug.Print "Recordsets"
 
 End Sub
 

sneuberg

AWF VIP
Local time
Yesterday, 22:25
Joined
Oct 17, 2014
Messages
3,506
Seems like I suggested this before but now I can't find where I did and what your answer was so here it is again.

It is so much easier to do this by just binding the table to the form and let Access do the heavy lifting. In the attached database I've added a form named Add Verse which adds verses which seem to be what you are trying to do. The combo boxes in this form are bound to the Verses table and have the Event and Mood tables as row sources.

Please give this a try. If this works for you it's a better solution than writing SQL. If it doesn't work for you please let me know why.
 

Attachments

  • MyGreeting4 Mod.zip
    196.4 KB · Views: 37

Users who are viewing this thread

Top Bottom