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
"Evi" <evwool[ at ]TakeItOutntlworld.com> wrote in message
news:trwTk.96895$yK5.82385[ at ]newsfe24.ams2...
[Quoted Text] > 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
>
>
>
>
>
>
|