having trouble opening recordset

[omar.norton]

I am trying to create a from with a series of combo boxes that each
query a different field (called Specific01, Specific02 etc., except
the first field which is called Condition). Each combo box has a SQL
statement in it's rowsource so it will only display distinct records
in it's field where all the previous fields match the choices chosen
in previous combo boxes, so eventually your choices will be narrowed
down until you come up with a final answer (i.e. the rowsource for the
final combo box only has one record). If this final answer is reached,
rather than displaying it in a new combo box's rowsource I want a text
box to display the answer. I am trying to put a piece of code into the
after update event of each combo box as below (this is what appears in
the first combo box, cboCondition):

Dim myConnection As ADODB.Connection
Dim myRecordSet As New ADODB.Recordset
Dim strSQL As String
strSQL = Me.cboSpecific01.RowSource
Debug.Print strSQL
Set myConnection = CurrentProject.Connection
myRecordSet.ActiveConnection = myConnection
myRecordSet.Open strSQL, , adOpenStatic

If myRecordSet.RecordCount 1 Then
Me.cboSpecific01.Visible = True
Me.cboSpecific01 = Null
Else
Me.cboSpecific01.Visible = False
Me.txtResult = myRecordSet.Fields(3).Value
Me.txtResult.Visible = True
Me.txtResult.Requery

myRecordSet.Close
End If

so if the next combo box (cboSpecific01) will only contain one record,
it is not shown and instead txtResult displays this final answer. If
it contains more than one choice, the next combo box (cboSpecific01)
is displayed.

For the above case, strSQL contains "SELECT DISTINCT
tblSpecifics.Specific01 FROM tblSpecifics WHERE (([Condition]=Forms!
frmMain!cboCondition)); ".

When I run the code I get the following message: "Run time error
'-2147217904 (80040e10)': No value given for one or more required
parameters" and the myrecordset.open line is highlighted. Before this
I tried using a DCount function instead of recordsets but I couldn't
get this to work either: I just got a "You cancelled the previous
operation" error message.

Can anyone see where I am going wrong? Any help would be much
appreciated.

[tricard]

On Dec 8, 8:09 am, [e-mail] wrote:

I am trying to create a from with a series of combo boxes that each
query a different field (called Specific01, Specific02 etc., except
the first field which is called Condition). Each combo box has a SQL
statement in it's rowsource so it will only display distinct records
in it's field where all the previous fields match the choices chosen
in previous combo boxes, so eventually your choices will be narrowed
down until you come up with a final answer (i.e. the rowsource for the
final combo box only has one record). If this final answer is reached,
rather than displaying it in a new combo box's rowsource I want a text
box to display the answer. I am trying to put a piece of code into the
after update event of each combo box as below (this is what appears in
the first combo box, cboCondition):

Dim myConnection As ADODB.Connection
Dim myRecordSet As New ADODB.Recordset
Dim strSQL As String
strSQL = Me.cboSpecific01.RowSource
Debug.Print strSQL
Set myConnection = CurrentProject.Connection
myRecordSet.ActiveConnection = myConnection
myRecordSet.Open strSQL, , adOpenStatic

If myRecordSet.RecordCount 1 Then
Me.cboSpecific01.Visible = True
Me.cboSpecific01 = Null
Else
Me.cboSpecific01.Visible = False
Me.txtResult = myRecordSet.Fields(3).Value
Me.txtResult.Visible = True
Me.txtResult.Requery

myRecordSet.Close
End If

so if the next combo box (cboSpecific01) will only contain one record,
it is not shown and instead txtResult displays this final answer. If
it contains more than one choice, the next combo box (cboSpecific01)
is displayed.

For the above case, strSQL contains "SELECT DISTINCT
tblSpecifics.Specific01 FROM tblSpecifics WHERE (([Condition]=Forms!
frmMain!cboCondition)); ".

When I run the code I get the following message: "Run time error
'-2147217904 (80040e10)': No value given for one or more required
parameters" and the myrecordset.open line is highlighted. Before this
I tried using a DCount function instead of recordsets but I couldn't
get this to work either: I just got a "You cancelled the previous
operation" error message.

Can anyone see where I am going wrong? Any help would be much
appreciated.

I have had the exact same problem in the past. What you need to do is
pass parameters to that command/recordset. Your Forms!frmMain!
cboCondition is a parameter in the query so you must pass that
parameter to the command that will make the recordset. I am sorry if
my syntax is off a bit, I use DAO and not ADO on a regular basis, but
you should get the jist ;)

Dim rs as new ADODB.recordset
Dim cmd As new ADODB.Command
Dim prm as new ADODB.Parameter
'set the parameter's reference to the first parameter (the only
parameter) of your command that
'represents your recordset
cmd.commandtext = cbo.rowsource
cmd.commandtype = <text type>
Set prm = cmd.Parameters(0)
'set the actual value of the parameter to the value of Forms!frmMain!
cboCondition
prm.value = <THE ACTUAL VALUE OF Forms!frmMain!cboCondition>
Set rs = cmd.Execute()
'your rs should now have the proper data returned
Another way I do this technique (and I use it alot in my forms) is to
have an afterupdate routine for each combo box that explicitly loads a
new rowsource for the next combo box the user will enter (or you the
gotfocus() routine and load the rowsource on entering the combo box
each time -- this is a bit slower though).

Best regards,

Tim