vba runtime error

[Michelle]

Help!

I keep getting runtime error (-2147217904 80040e10)
No value given for one or more required parameters. It then highlights my
Open statement. Please tell me what's wrong with it.

Dim rst As ADODB.Recordset
Dim strSQL As String

strSQL = "SELECT tblDSRDMTasks.DeptID, tblDSRDMTasks.DSRPartNo,
tblDSRDMTasks.TaskCode, ztblDDT.DSRTask1, ztblDDT.DSRTask2, ztblDDT.DSRTask3,
ztblDDT.DSRTask4, ztblDDT.DSRTask5 FROM tblDSRDMTasks LEFT JOIN ztblDDT ON
(tblDSRDMTasks.DeptID=ztblDDT.DeptID) AND (tblDSRDMTasks.DSRPartNo =
ztblDDT.DSRPartNo) WHERE (((tblDSRDMTasks.DeptID) = [SelectDept]))"

Set rst = New ADODB.Recordset

'Position pointer to DeptID(recordset) selected in Combo33

rst.Open strSQL, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly

 

[Clifford Bass]

Hi Michelle,

It needs the SelectDept parameter to be specified, which requires a
user interface, which ADO does not have. So, you need to ask the user for
the SelectDept value before constructing it, and then put it into the SQL
statement directly.

Clifford Bass

 

[Michelle]

Cliff,
The SelectDept is actually a variable that I created to hold the Combo33
value. You can select the DeptID in that combo box. I tried to put that value
in a public variable so I can use it in the SQL statement. How can I pass
that value to my SQL without the users intervention?

Hi Michelle,

It needs the SelectDept parameter to be specified, which requires a
user interface, which ADO does not have. So, you need to ask the user for
the SelectDept value before constructing it, and then put it into the SQL
statement directly.

Clifford Bass

Help!

I keep getting runtime error (-2147217904 80040e10)
No value given for one or more required parameters. It then highlights my
Open statement. Please tell me what's wrong with it.

Dim rst As ADODB.Recordset
Dim strSQL As String

strSQL = "SELECT tblDSRDMTasks.DeptID, tblDSRDMTasks.DSRPartNo,
tblDSRDMTasks.TaskCode, ztblDDT.DSRTask1, ztblDDT.DSRTask2, ztblDDT.DSRTask3,
ztblDDT.DSRTask4, ztblDDT.DSRTask5 FROM tblDSRDMTasks LEFT JOIN ztblDDT ON
(tblDSRDMTasks.DeptID=ztblDDT.DeptID) AND (tblDSRDMTasks.DSRPartNo =
ztblDDT.DSRPartNo) WHERE (((tblDSRDMTasks.DeptID) = [SelectDept]))"

Set rst = New ADODB.Recordset

'Position pointer to DeptID(recordset) selected in Combo33

rst.Open strSQL, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly

 

[Clifford Bass]

Hi Michelle,

You were almost there. Presuming that your code is being run from the
form on which Combo33 resides you can change the last part of your strSQL =
statement to this:

If DeptID is numeric:
...WHERE tblDSRDMTasks.DeptID = " & [Combo33]

If DeptID is character and will never contain a quote (") symbol:
...WHERE tblDSRDMTasks.DeptID = """ & [Combo33] & """"

If DeptID is character and will might contain a quote (") symbol:
...WHERE tblDSRDMTasks.DeptID = """ & Replace([Combo33], """", """""")
& """"

Hope that helps,

Clifford Bass

P.S. It goes a long to help yourself and anyone to follow if you rename your
controls to more meaningful names. You can including a control type
abbreviation in the name. For example, Combo33 could become
cbSelectDeptartmentID and a text box that contains a last name could be named
txtLastName.

Cliff,
The SelectDept is actually a variable that I created to hold the Combo33
value. You can select the DeptID in that combo box. I tried to put that value
in a public variable so I can use it in the SQL statement. How can I pass
that value to my SQL without the users intervention?

Hi Michelle,

It needs the SelectDept parameter to be specified, which requires a
user interface, which ADO does not have. So, you need to ask the user for
the SelectDept value before constructing it, and then put it into the SQL
statement directly.

Clifford Bass

Help!

I keep getting runtime error (-2147217904 80040e10)
No value given for one or more required parameters. It then highlights my
Open statement. Please tell me what's wrong with it.

Dim rst As ADODB.Recordset
Dim strSQL As String

strSQL = "SELECT tblDSRDMTasks.DeptID, tblDSRDMTasks.DSRPartNo,
tblDSRDMTasks.TaskCode, ztblDDT.DSRTask1, ztblDDT.DSRTask2, ztblDDT.DSRTask3,
ztblDDT.DSRTask4, ztblDDT.DSRTask5 FROM tblDSRDMTasks LEFT JOIN ztblDDT ON
(tblDSRDMTasks.DeptID=ztblDDT.DeptID) AND (tblDSRDMTasks.DSRPartNo =
ztblDDT.DSRPartNo) WHERE (((tblDSRDMTasks.DeptID) = [SelectDept]))"

Set rst = New ADODB.Recordset

'Position pointer to DeptID(recordset) selected in Combo33

rst.Open strSQL, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly