Filtering form containing combo boxes

[Evi]

My subform has a list of names which are in combo boxes. The form contains
EspID and the combo which is bound to EspID but shows the word rather than
the ID field.
If I right-click this field, I can enter >ro and filter out any names which
come before ro...

But is there a way of doing this in code?

At the moment, I have a second subform in my main form which is linked to a
table that acts as a bookmark so I can type say ro into this and when the
form opens, it will use DLookup to find the EspID of the word that is
greater than ro and find this in my subform using FindFirst, but I can't
figure out a way of filtering the subform to only show those words which are
greater than ro. I thought that putting a text box in my form with
=cboEnglish.Column(1)
so that it displays the text part of the combo box would work but neither
Filter nor FindFirst recognise this text box.

Any suggestions?

Evi

 

[Evi]

OK I've found a way round this (although I'd still like to know how to do it
as in my original question, if anyone can think of a way). I've added the
lookup table to the underlying query of my subform and added the field which
I want to filter to my subform. The code which makes it work is as follows:

Private Sub cmbFilterSub_Click()
On Error GoTo Err_cmbFilterSub_Click
'filter subform using text in FrmReached subform

Dim MyText As String
Dim MySub As Control
Dim Crit As String
MyText = Me.FrmReached.Form.Reached

Crit = "[QryTransEspEng].[Esperanto]>'" & MyText & "'"
'must mention underlying query of the subform or I get an error message
'specified field could refer to more than one table

Set MySub = Me.FrmEspEngSub
MySub.Form.Filter = Crit
MySub.Form.FilterOn = True
Set MySub = Nothing

Exit_cmbFilterSub_Click:
Exit Sub

Err_cmbFilterSub_Click:
MsgBox Err.Description
'clean up if it crashes
MySub.Form.FilterOn = False
Set MySub = Nothing
Resume Exit_cmbFilterSub_Click

End Sub