Subforms Recordsource using 2 criteria

[ShanesLittleT]

Using Access 2000

I have a main form "frmOrders" and a subform (contineous form) on it. The subform has a field on it named "txtCustomers". On the main form I have a unbound text box "txtFind" that I'm allowing the user to type all or part of the customer name then click a cmdButton to find all matching records in the subform. I'm trying to use the subforms recordsource property to do this but having problems. Could someone lend a hand with code?

I would also like to add another criteria using a date field (checking to see if it's Null) from the subform. So two criteria's would be used. Using LIKE from txtFind from the main form and Is Null from date field on the subform.

Thanks in advance for any help you may be able to offer.
Shane

 

[WayneRyan]

Shane,

Base your subform on a query. The query will have all of the
fields in the child table that you want to use.

In the query, in the Criteria row for the search (Key?) field
put:

Like "*" & Forms![YourMainForm]![txtFind] & "*"

Also, in the Criteria for the date field, put:

Is Null

To make it work, use the AfterUpdate of the txtFind control
to put:

Forms![YourMainForm]![YourSubForm].Form.Requery

Wayne

 

[ShanesLittleT]

Thanks Wayne,

I could do as you suggested if that was the only thing I was doing with the subform but I have several other comboboxes and commandbuttons that I'm using to change the subforms query to show Orders according to different criteria's.