Filter a subfForm based on a value typed on the main form

[Khalil Handal]

Hi to all,


I have a main form with a subform with a Many-To-Many relationship.
I have a text Box on the main form. My subform has a combo box on it with Datasheet as Default View.
The user has to enter a text in the text box (txtSearch) on the main form so that the sub form will show only records for the search criteria only.
What code should I use in the AfterUpdate Event of the txtSearch text box to filter the subform.
Note: The data entered in the txtSearh might be part of the field.

Khalil

[ranman256]

when user alters the search value:

sub txtSearch_Afterupdate()

if isnull(me.txtSearch) then
   me.subform.form.filterOn =false
else
   me.subform.form.filter = "[field]='" & me.txtSearch & "'"
   me.subform.form.filterOn =true
endif
end sub

[Khalil Handal]

Hi,
Thank you for the reply,
I am having a runtime error 13, Type mismatch.
In addition, the text box (txtSearch) might be part of a record, so I should use LIKE .

The sub Form Row Source is the link table with two foreign key and a primary key.
The only field on the sub form is a combo box with a foreign key as a control source and a record source to select the required field which is column(1).

Khalil

[Welshgasman]

Strings need to be surrounded by single quotes ' unless it contains a single quote, then triple double quotes works, I think?


Date literals with # and in mm/dd/yyyy or yyyy-mm-dd format


Numbers do not need anything


Also for anything other than one item of criteria, I tend to put the the criteria into a string variable and then debug.print it, until correct then also use that in the code.


Added benefit is, if you still cannot see the error, you can copy and paste back here for someone else to spot it.

[Gicu]

I think you'll need to give us more info, at least a screen shot of your form\subform with the combo showing some values and the subform's recordsource. Are the forms linked in anyway via the master\child fields? Have you tried to restrict the subforms record source using the txtSearch and in the after update to simply requery the subform?

Cheers,

[Khalil Handal]

Hi,
Sub Form Record Source is a table with a primary key and two foreign key fields: fkKeyWords and fkBookId

The combo box on the subform has fkKeyWords as the Control Source and (SELECT tblKeyWords.pkKeyWords, tblKeyWords.KeyWords) as a Row Source.

I was able to make it work with the following:

Me!cntKeywordsSubForm.Form.Filter = "[fkKeyWords]=" & Me.txtSearch

Which is not exactly what I want.

Following is a screen shot of the SubForm.


The user needs to enter the keyword and not the corresponding number of fkKeyWords.
In the above screen shot if the user typed Jordan in the txtSearch on the main form, the subform should show 2 records only (first and forth).
Maybe I should requery the subform based on the value in the txtSearch!!!

Thanks

[Gicu]

Add a calculated field to the subform's record source SearchReturn:Instr([Keywords],Forms!frmYourMainForm.txtSearch)>0 and put in its criteria row True.
Now in the AfterUpdate event just leave Me.cntKeywordsSubForm.Form.Requery.
You might want to tweak the record source to return all records if the search textbox is empty.
Cheers,

[June7]

Could use a combobox for user input if it is not set for LimitToList and if you don"t include the fkKeyWords field in RecordSource or at least not as the Bound Column.

As shown by Vlad, you need to do pattern match. This means either InStr() as Vlad shows or LIKE operator. Can be calculated in query or in VBA.

Me.cntKeywordsSubForm.Form.Filter = "[KeyWords] LIKE '*" & Me.txtSearch & "*'"

Me.cntKeywordsSubForm.Form.Filter = "InStr(KeyWords, '" & Me.txtSearch & "')>0"