Filtering ComboBox based on entered text

[Ian Chappel]

I am trying to alter the Rowsource of a ComboBox to display only records
that match any text I have typed into the Combo. I have AutoExpand set to
No.

All works fine apart from two niggles:

1. I can't seem to detect when I only have narrowed down to only one
record - I am getting an error message "Object variable or With block
variable not set" if I include the "If .Recordset.RecordCount > 1 Then"
line.

2. I would like to avoid having the contents of other instances of this
ComboBox (it is on a Continuous Form) loose their displayed contents when I
filter the Rowsource. I have a feeling thought that this might not be
possible!

Here's my code:
_____________________________________________________________________

Private Sub cboNominal_Change()

Dim strEntered As String
Const strMainSQL = "SELECT tblNominals.NomID, tblNominals.NomName FROM
tblNominals"
Dim strWhereSQL As String

With Me!cboNominal
strEntered = .Text
.RowSourceType = "Table/Query"
If Len(strEntered) < 3 Then
strWhereSQL = ""
.RowSource = strMainSQL
Else
strWhereSQL = " WHERE tblNominals.NomName Like '*" & strEntered
& "*'"
.RowSource = strMainSQL & strWhereSQL
'If .Recordset.RecordCount > 1 Then ' **********This line seems
to be the problem **********
.Dropdown
'End If
End If
End With

End Sub
_____________________________________________________________________

Thanks.

 

[Jeff Boyce]

Ian

I'd suggest you look into Recordset. Access needs to have one defined
before you can reference it. The somewhat cryptic error message alludes to
this.

I'm not quite visualizing what your design is on your other question.

 

[Ian Chappel]

Thanks Jeff

I have tried to see what I'm doing wrong, but think I may be trying to use a
sledgehammer to crack a nut! All I want to do is detect when a combobox
lists only one record, and then skip "Dropdown". What would be the simplest
way to do this?

My second (lesser) problem is that the same combobox in *other* records on
my continuous form (or datasheet) loose their displayed contents when I'm
filtering the rowsource of the combobox in my *current* record of my
continuous form (or datasheet). This is not a serious problem, although does
not look good. The combo naturally retains it's value, but because the value
is no longer a part of the rowsource, the combo display is blank. As I say,
I'm not sure this is solvable?

Ian

 

[Jeff Boyce]

Ian

On the former, I believe you could check the .ListCount property of the
combobox control.

I'm not sure I understand your second question. Are you equating a
continuous form and a datasheet view?