Dynamic Filter on Unbound Forms (1 Viewer)

Drand

Registered User.
Local time
Today, 16:49
Joined
Jun 8, 2019
Messages
179
Hi folks

I have an unbound form "frmStockInvHistory"

On this form I have 3 tabs, each with a different form displaying different information. These are "FrmStockOutHistory", "FrmInvAddedHistory", and "FrmInvUsedHistory".

I am trying to apply a filter to a field "StockItem" on each form but at this stage have only been testing this on "FrmStockOutHistory". (Once I got this correct I was going to move on to the other two forms.

My code is:

Private Sub txtNameFilter_KeyUp(KeyCode As Integer, Shift As Integer)

Dim filterText As String

'Apply or update filter based on user input.
If Len(TxtNameFilter.Text) > 0 Then
filterText = TxtNameFilter.Text
Me.Form.Filter = "[FrmStockInvhistory]![FrmStockOutHistory]![StockItem] LIKE '*" & filterText & "*'"
Me.FilterOn = True

'Retain filter text in search box after refresh.
TxtNameFilter.Text = filterText
TxtNameFilter.SelStart = Len(TxtNameFilter.Text)
Else
' Remove filter.
Me.Filter = ""
Me.FilterOn = False
TxtNameFilter.SetFocus
End If

End Sub

I have tested this on a standard form without the tabs and it works fine but I cannot get it to work on the form with tabs.

Would appreciate any assistance with this.

Many thanks
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:49
Joined
Oct 29, 2018
Messages
21,358
Hi. Maybe because by a standard form you mean a bound form so Me.Filter works. But for an unbound form, you may have to change that to something like Me.SubformName.Form.Filter.
 

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 22:49
Joined
Aug 30, 2003
Messages
36,118
Also, the filter should just include the field name:

"StockItem LIKE '*" & filterText & "*'"
 

Drand

Registered User.
Local time
Today, 16:49
Joined
Jun 8, 2019
Messages
179
Thanks folks

I tried:

Me.FrmStockOutHistory.Form.Filter = "[StockItem] LIKE '*" & filterText & "*'"

But still does not work.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:49
Joined
Oct 29, 2018
Messages
21,358
Thanks folks

I tried:

Me.FrmStockOutHistory.Form.Filter = "[StockItem] LIKE '*" & filterText & "*'"

But still does not work.
One other thing. You need to use the name of the subform container rather than the form it contains.
 

Drand

Registered User.
Local time
Today, 16:49
Joined
Jun 8, 2019
Messages
179
Sorry I don't know what that means
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:49
Joined
Oct 29, 2018
Messages
21,358
Sorry I don't know what that means
When you go to design view, click once on the subform and tell us what is in the Name property. If you click more than once, you've gone too far.
 

Drand

Registered User.
Local time
Today, 16:49
Joined
Jun 8, 2019
Messages
179
Thank you all.

Using Filteron worked!

Appreciate your assistance folks.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:49
Joined
Oct 29, 2018
Messages
21,358
Thank you all.

Using Filteron worked!

Appreciate your assistance folks.
Hi. Glad to hear you got it sorted out. Good luck with your project.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 13:49
Joined
May 7, 2009
Messages
19,169
it is much better to use Combobox rather than textbox for you filter.
 

Users who are viewing this thread

Top Bottom