find the prob

this code gives error that no value given to 1 or more required parameters
and shows ps = nothing
in line set ps =objconn.execute(p)

i am using a combo box to show a list of products and when a product is selected its related article num is shown in the text box



Private Sub Command1_Click()
Dim objConn As ADODB.Connection
Set objConn = New ADODB.Connection
objConn.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Data source=c:\database\DB.mdb;Persist Security Info=False")
Dim p As String
p = " select article from product where pname =" & Combo1.Text & " "
Set ps = objConn.Execute(p)
Text1.Text = p
Form11.Text1.Text = Combo1.Text
Form11.Show
End Sub

You have several things going amiss here.

When running a SELECT query, you need to use the Open method of the recordset object. You do not use the connection object.

Also, for your own benefit, use variables that mean something.

VB Code:

'by looking at the code I can figure out what is going on
'but, this is not a good coding practice to get into
Dim p As String

Try this

VB Code:

Private Sub Command1_Click()
Dim objConn As ADODB.Connection
Set objConn = New ADODB.Connection
Dim objRS As ADODB.Recordset
Set objRS = New ADODB.Recordset
objConn.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Data source=c:\database\DB.mdb;Persist Security Info=False")
Dim sSQL As String
sSQL = "select article from product where pname = '" & Combo1.Text & "' "
'NOTE:  Strings must be encapsulated in single quotes
objRS.Open sSQL, objConn
Text1.Text = objRS.Fields.Item("article").Value
objRS.Close
Set objRS = Nothing
End Sub

Quote:

---

Originally Posted by Hack

You have several things going amiss here.

When running a SELECT query, you need to use the Open method of the recordset object. You do not use the connection object.

Also, for your own benefit, use variables that mean something.

VB Code:

'by looking at the code I can figure out what is going on
'but, this is not a good coding practice to get into
Dim p As String

Try this

VB Code:

Private Sub Command1_Click()
Dim objConn As ADODB.Connection
Set objConn = New ADODB.Connection
Dim objRS As ADODB.Recordset
Set objRS = New ADODB.Recordset
objConn.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Data source=c:\database\DB.mdb;Persist Security Info=False")
Dim sSQL As String
sSQL = "select article from product where pname = '" & Combo1.Text & "' "
'NOTE:  Strings must be encapsulated in single quotes
objRS.Open sSQL, objConn
Text1.Text = objRS.Fields.Item("article").Value
objRS.Close
Set objRS = Nothing
End Sub

---

thanx it really worked