Run Time Error 'No value given for one or more required parameters' (1 Viewer)

[AunN]

Please have a look at the code:

Private Sub Command0_Click()
Dim name1 As String
name1 = Text1.Value
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
conn.Provider = "Microsoft.Jet.OLEDB.4.0"
conn.Open ("C:\Data\SampleDB.mdb")
Dim myCommand As ADODB.Command
Set myCommand = New ADODB.Command
myCommand.ActiveConnection = conn
myCommand.CommandText = "INSERT INTO Table1(name) VALUES(name1)"
Dim rs As ADODB.Recordset
Set rs = myCommand.Execute(myCommand.CommandText)
rs.Close
Set rs = Nothing

Executing this code generates Run time error - No value given for one or more required parameters

Please resolve my problem!

 

[pbaldy]

Try

myCommand.CommandText = "INSERT INTO Table1(name) VALUES('" & name1& "')"

 

[boblarson]

First off, it is best to NOT use Access Reserved Words as the names of fields or objects. In this case you have used the word NAME. So you need to enclose it in square brackets. (I would suggest going back and fixing things so you didn't have to use NAME as the name of the field because that particular one can be especially nasty and difficult at times.

Also, you need to concatenate in the variable so that the value is passed.

So the revision:
myCommand.CommandText = "INSERT INTO Table1([name]) VALUES(" & name1 & ")"

but since NAME is probably TEXT (I assume), you would need to add quotes:

myCommand.CommandText = "INSERT INTO Table1([name]) VALUES(" & Chr(34) & name1 & Chr(34) & ")"

 

[AunN]

@ Boblarson

You are right! As name is text field and i tried this:

myCommand.CommandText = "INSERT INTO Table1([name]) VALUES(" & Chr(34) & name1 & Chr(34) & ")"

but now i m getting

"Run Time error - 91 Object Variable or with Block Variable not set" at line
"rs.close"

and one thing more i m using "myCommand.Execute" before "rs.close"

What to do now?

 

[boblarson]

You don't need the rs code at all. Have you tried using the Execute method of your Command object?

Second, is this a DIFFERENT database you are trying to update or the one the code is in? If it is the same one the code is in, you should use

Dim conn As ADODB.Connection
Set conn = [B][COLOR=red]CurrentProject.Connection[/COLOR][/B]

 

[AunN]

@Boblarson

actually this is my first code in VBA and i don't have full understanding about your statement "You don't need......."

secondly i have applied:
Dim conn As ADODB.Connection
Set conn = CurrentProject.Connection

as it was the same project but now i m getting error at
conn.Provider = "Microsoft.Jet.OLEDB.4.0"

and the error is " Run Time Error 3705 - Operation is not allowed when object is open "
What to do now?

 

[boblarson]

Okay, let's start the code from scratch:
yours revised -

Private Sub Command0_Click()
Dim name1 As String
Dim conn As ADODB.Connection
Dim myCommand As ADODB.Command
 
 
name1 = Text1.Value
 
Set conn = CurrentProject.Connection
 
Set myCommand = New ADODB.Command
 
myCommand.ActiveConnection = conn
 
myCommand.CommandText = "INSERT INTO Table1([name]) VALUES(" & Chr(34) & name1 & Chr(34) & ")"
 
myCommand.Execute

The rest was not needed so I removed what was extraneous.

 

[AunN]

It's done Boblarson!!

You are great..thanku..

can i get ur email for further corresponding..as i m learning basics and i need to take some genuine help..

 

[boblarson]

can i get ur email for further corresponding.

Sorry, I only respond to posts in the forums as it allows others to get benefit of the help and not just for a single individual.

When you have questions, post your questions. We have a LOT of people here who can help. So you also aren't constrained by my schedule. Others can help just as well as I can.