Using a combo box to filter and display records in a subform

[secondangel]

Hi ive tried the serach and some filter code i found in the forums but they dont do what i requrie so figured id ask for direct help.

I have a table full of information and i have it set as a form.

Ive popped it as a subform in a mainform.

on the mainform is a combobox that i want to use to filter ALL records in the subform.

A few questions.

Should the subform be of type datasheet ?

Does the code go in the OnUpdate part of the combobox or is it better to have a button so that it can be done several times if need be without closing and reopening the form.

I have some filter code that finds the first record but then you can still see all the other records after it

I just want to pick and display all records where combobox = the fieldname.

Do i do a recordset findfirst and then flag it as visible and then do a rst.movenext and loop through the whole table flagging the record as visible as yes if it matches ?

Any help much appreciated as always.


For help.

Table is tblSupplierCatalogue
Field is SupplierName
Combo is supCombo

Mainform is formSupCatChoice
Subform bit is formSupCatalogue (this is just as a normal tabular display form - should it be datasheet ?)


Thank you

Ashley

 

[ajetrumpet]

Just based on the title of this thread, see if this helps..

 

[secondangel]

this just may do the trick. will report back. thanks very much

 

[secondangel]

the only bit which WONT work on my bit is

me.mysub.requery

Whats mysub ? Is it the subform name ?

Thanks alot

 

[ajetrumpet]

MYSUB is the subform container name. like this:

formname.subformContainer.FORM.SubformControlName.Action

 

[secondangel]

ok i put in design view and named the subform container as mysub.

The button works (i.ie i dont get a debug error) however it doesnt filter or requery the records in teh subform

The cancel button on mine works, it clears the combos makes them invisible etc but even when seletcing a new selection from the first combo box the second combo box has remebered the values based on the first time i selected.

Any ideas

Thanks

Ashley

 

[secondangel]

never mind

i forgot to change

SELECT orders.* FROM orders WHERE (orders.customer = forms!frm!cbocustomer OR forms!frm!cbocustomer IS NULL) AND   (orders.item = forms!frm!cboitem OR forms!frm!cboitem IS NULL) AND   (orders.carrier = forms!frm!cbocarrier OR forms!frm!cbocarrier IS NULL);

to my own named stuff.

works now on the filtering

EXCEPT when i clear then it doesnt clear the second combo box values.

I tried a mysub requery to kind of clear but it seems setting a null value to the second box isnt actually happening

Any ideas

Thanks again

Ashley