Recordset - too few parameters

[Guest]

Hiya.

I have a form with 2 combo boxes. 1 is dependent on the other.

1. cbo_Division
2. cbo_Function

The intention is that when the Division is selected, the function combobox
will be populated with those functions within the selected division.
I got this to work no problems (without using code - just a smart row source
query in cbo_Function that references cbo_Division).

Then I realised that some divisions have no functions within them. For these
I wanted to disable cbo_Function when these divisions were selected. But I
can't get my code to work (chucks up the 'Too few parameters. Expected 1'
error). Can anyone help?

Code (I got rid of most dims (rst As recordset etc), but might put them back
- same error regardless):

Private Sub cbo_Division_Change()

Dim sqlstring As String

sqlstring = cbo_Function.RowSource
If CurrentDb.OpenRecordset(sqlstring).RecordCount = 0 Then
cbo_Function.Enabled = False
Else
cbo_Function.Enabled = True
End If

End Sub

SQL:
SELECT [tbl_Function].[Function_ID], [tbl_Function].[Function_Name] FROM
tbl_Function WHERE
((([tbl_Function].[Division_ID])=[Forms]![frm_Projects]![cbo_Division]));

Thanks,

Basil

 

[Guest]

Figured it out again.

The problem was that it was referencing cbo_Function from within the SQL
string. This threw up the error.

Solution: Compile the SQL within VBA as so:

Private Sub cbo_Division_Change()

Dim sqlstring As String

cbo_Function.Requery

sqlstring = "SELECT [tbl_Function].[Function_ID],
[tbl_Function].[Function_Name] " _
& "FROM tbl_Function WHERE ((([tbl_Function].[Division_ID])=" &
cbo_Division & "));"

If CurrentDb.OpenRecordset(sqlstring).RecordCount = 0 Then
cbo_Function.Enabled = False
Else
cbo_Function.Enabled = True
End If

End Sub

So how come I can't give myself an 'answer' on MSDN? It shows this query
still unresolved, but I have resolved it myself. A bit pants that.

B

 

[Marshall Barton]

I have a form with 2 combo boxes. 1 is dependent on the other.

1. cbo_Division
2. cbo_Function

The intention is that when the Division is selected, the function combobox
will be populated with those functions within the selected division.
I got this to work no problems (without using code - just a smart row source
query in cbo_Function that references cbo_Division).

Then I realised that some divisions have no functions within them. For these
I wanted to disable cbo_Function when these divisions were selected. But I
can't get my code to work (chucks up the 'Too few parameters. Expected 1'
error).

Just use this one line of code right after the
cbo_Function.Requery line:

Me.cbo_Function.Enabled = (Me.cbo_Function.ListCount > 0)

 

[Guest]

Your solution is much better!

I shuld have thought of that - I'm sure I figured that out a few years back.

Thanks. B