form/subform using like

[peggypph]

I have design a form and subform where the records being listed in the subform depends on the "product code" field in the main form. I do not use link field because it is allowed to input wild character in the "product code" field. e.g. product code ="*", then display all the products, product code="A*", it display all the products starting with "A". So I design a query in record source field of the subform "select * from product where prod_code like forms![parent form]![prod_code]". Every time the user click or input the "product code" in the main form, the subform will be refresh by command "requery".

Under my design, it works fine to input "A*" in "product code" field in main form when it is the first time to run the program. BUT after I input the exact product code e.g. "ABC-1"(it will show one record), later I input "A*" , NO records are displayed in the subform. If I input "*", it can still display all records. But I can not use "A*" or "AB*" anymore except I input the full product code, "*" or restart the program.

Is there some problem on my query define in record source of the subform?

 

[Oldsoftboss]

You might have to post an example for us to look at.

To me it seems you need to requery the record source each time. I would base the subform on a query. You can run the query from the database window with reference to the form field. Once you have the query working as you expect, make it the record source for your subform.

Dave

 

[peggypph]

attached pls find my program.

Test method:
1) open product_maintenance form which will link product form
2) key in "AC*" in product code field in product_maintenance form,
it will display all records with "AC....." product code
3) key in specific product code e.g. "ACS-1000" which will display only 1 record
4) key in "AC*" again, it display NO records
5) if I key in "*", it still display ALL records, but I can not key in "AC*' to display partial