Hi Angie,
I'm not well versed in stored procedures. But I think you can do what you want with a simple query. Something like:
Select [field list] From [customer table]
Where [CUST_NAME] like """*" & form_filter_field & "*"""
The route you take to do this depends on how you're going to build the form. Overall, you will probably use the AfterUpdate event of form_filter_field.
For example, if your form is bound to the table and the filter field is not, you could have:
Code:
Private Sub form_filter_field_AfterUpdate ()
If IsNull(Me.form_filter_field) then
Me.Filter = ""
Me.FilterOn = False
Else
Me.Filter = "[CUST_NAME] LIKE ""*" & Me.form_filter_field & "*""
Me.FilterOn = True
Me.Requery
End If
End Sub
Or, you could change the RecordSource of the form. For example:
Code:
Private Sub form_filter_field_AfterUpdate ()
Me.RecordSource = "Select [fields] From [table]"
If Not IsNull(Me.form_filter_field) then
Me.RecordSource = Me.RecordSource & _
" Where [CUST_NAME] LIKE ""*" & Me.form_filter_field & "*"""
End If
Me.Requery
End Sub
If the results will be displayed in a subform, you could simply build the query for the subform like this:
Select [fields] from [table] where [CUST_NAME] like ""*" & [Forms]![nameofyourmainform].form_filter_field & "*"""
Using this method, you won't have to write any code. The subform will automatically filter. You will have to manually build the Master and Child Link fields for the subform. Also, your subform won't work unless the form "nameofyourmainform" is open -- which is not really a problem since your intent will be to use the subform on the main form, it's just difficult to test the subform independent of the main form.
Now, when you say you would like to "return all instances of customers...", you could mean that you want to display them. If so, one of the above solutions will handle that. However, since you mentioned that you wrote a stored procedure, your intent could be to be able to manipulate each of the instances. In that case you'll want to use the SQL to open a record set in VBA. If that is the case, you probably want a more advanced book, something that includes VBA code examples. For example our book "Access 2003 VBA Programmer's Reference". The book is available through Wrox. But when I say "our book" I mean me and the other people who wrote chapters for that book.
Good luck.
Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
http://www.pnwadg.org