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.

 

[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.

btw I have used a replace function to remove the semicolon at the end
of the SQL string but the same problem occurs