Challenged with INSERT record into table using VBA in Access

[infoscion]

Hi All:
I have been working on a project to track customer behavior. Once the data is entered into a form it is supposed to be tranferred onto a adtabase through the click of a button. I have developed the code to a good extent. The code does get compiled but gives a run time error and I have no idea as to how fix it.The problem is with the EXECUTE method of the connection object. The error that I get is
"No value given for one or more required parameters". In several cases I have the got the error as a Synatax error pointing to the INSERT statement as well.


Ideas and suggestiosn are appreciated.Here is what the code looks like.

Private Sub Command11_Click()
Dim Cn As ADODB.Connection
Dim CmdCommand As New ADODB.Command
Dim rs As ADODB.Recordset

Dim strSQL As String

Dim Con As String
'open recordset
Set Cn = New ADODB.Connection
Set rs = New ADODB.Recordset
rs.CursorType = adOpenKeyset
rs.LockType = adLockOptimistic
rs.CursorLocation = adUseClient
Set CmdCommand = New ADODB.Command
'open databaseconnection to schedule database

strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\Desktop\dbform.mdb"
Cn.Open strCon
'SQL to insert a new record into the databse containg the values in the from
strSQL = "INSERT INTO Table1 ([Idea],[names],[namee],[timing])VALUES(" & Str(Me!Ideas) + "," & (Me!names)
strSQL = strSQL & "," & (Me!namee) & "," & Me!timing + ");"

MsgBox (strSQL)
' With CmdCommand
' .ActiveConnection = Cn
'set the insert or update sql statement to the command text
' .CommandText = strSQL
'execute the command
' .CommandType = adCmdText
' .Execute
'End With
Cn.Execute (strSQL)
Set Cn = Nothing

'rs.Close
' Set rs = Nothing

End Sub

Regards,
Info

 

[afryer]

Hi,

I might be missing something here, but in the code:

strSQL = "INSERT INTO Table1 (,[names],[namee],[timing])VALUES(" & Str(Me!Ideas) + "," & (Me!names)
        strSQL = strSQL & "," & (Me!namee) & "," & Me!timing + ");"

What is the + sign for? If you are trying to concatenate then you use the & symbol to do it.

Andrew

 

[RoyVidar]

The field names indicates the fields may be text, then you'll need text delimiters (single quotes). Also, using the + operator when concatenating gives anomalities when Null (well perhaps the ampersand too, but it's probably better;-))

[tt]trSQL = "INSERT INTO Table1 ([ignore][/ignore],[names],[namee],[timing]) VALUES ('" & Str(Me!Ideas) & "','" & (Me!names)
strSQL = strSQL & "','" & (Me!namee) & "','" & Me!timing & "');"[/tt]

Also check out the Access fora, there's seven of them. Do a forum search from the top of the page....

Roy-Vidar

 

[infoscion]

Hi Folks:
Thank you very much for the response. The '+' sign does the same thing as the '&' sign. Also, I had missed out on the sigle quote thing and now it is working fine. Again, thank you very much guys.

Regards,
Info

 

[RoyVidar]

Let me post a slight disagreement, the + sign can be very different from the ampersand under certain circumstances.

What is the result of the following two samples in the immediate pane (ctrl+g)?

[tt]? Null & "sometext"
? Null + "sometext" [/tt]

- so take care...

Roy-Vidar