Building a searchable Form/Dashboard

[CStedron13]

First time post... please be gentle...

I've got a data set that is basically two critical fields and then a bunch of descriptive fluff. The critical fields are Customer and Product. The end game here is to have a dashboard where I can search a customer value and have the database return all of the products that exist for said customer, OR search by a product and have the database return all of the customers who have ordered that product. (If I can only accomplish one of these, I would definitely want to search by Customer.)

Ok guys and gals, restore my faith in humanity... :)

[Seth Schrock]

There are many ways to do this depending on if you want simplicity of design or if you want better looks. I'll give you simplicity of design and we can tweak it if you want a different look.

I would create a form based on your data set and set the Form's Default View property to Continuous Forms. This will give it a data sheet like view, but still make it so that you have a form header and footer. In the header, put two textboxes and a button. The textboxes will be what you type in for your search. The button will actually do the filtering of your data set. Name your textboxes txtCustomer and txtProduct.

Now for the functionality. I'm going to guess that the Customer and Product fields are text fields (correct me if I'm wrong because it matters). In the button's On_Click event, enter the following code:

dim strFilter as String

If Me.txtCustomer <> "" Then
   strFilter = "Customer = '" & Me.txtCustomer & "'"
End If

If Me.txtProduct <> "" Then
   If strFilter = "" Then
      strFilter = "Product = '" & Me.txtProduct & "'"
   Else
      strFilter = strFilter & " And Product = '" & Me.txtProduct & "'"
   End If
End If

Me.Filter = strFilter
Me.FilterOn = (strFilter <> "")

[CStedron13]

Thank you for the reply!!

I've got my text boxes and button created, but I'm not sure where to enter the code. (I'm in 2007.) I can right-click the button, and there's an option for "Build Event," but it wants to walk me through what I'm trying to do instead of just letting me enter the code. (I've got a dropdown for "Add New Action.") What do I need to do to enter the macro?

Thanks!

[Seth Schrock]

In the button's property window, there are different tabs. One of these is called Event. The very top event for a button is On Click. Click inside the empty box and a button will appear on the right side of it having three periods (...). Click this button, select Code Builder and then click OK. This will then take you to where you need to add the code.

[CStedron13]

Ok, got it. Everything looks good, except when I try to enter a value into one of the text boxes to run the search, I'm unable to enter anything, and I get "Control can't be edited; it's bound to unknown field "txtCustome r.""

[Seth Schrock]

Then you have need to clear the Control Source from txtCustomer.