Form Question - Filter Issue

[aeslabby]

I am creating a small database to track task data (manpower tracking basically) where I have the following fields:

Customer/Functional Group/Task Status/Task Name/Task Description/WO #/Funded Hours/Notes


So I have created a form and subform where a 'Customer' is selected on the main form using a combo box...and that part is functioning very well. BUT, I want to be able to show all records at startup, as well as whenever the user requires to do so. I tried creating a simple button based on a "Show all Records" query/macro...but it doesn't work on either form. Also note that when I open the Subform alone, all records are showing (says 1 - 124 at bottom) but not when I open the main form, so I assume the issue has something to do with the way my Main form is set up.

Additionally, I would like an additional combo box on the Main Form to filter by functional group. So basically, the users can filter by Customer, then somehow select All Records, and filter by Group as well.

Any assistance would be SOOO helpful!

THANKS!!!!!

 

[jkl0]

What is your code behind the On Change on the combo box?

 

[aeslabby]

I don't have anything at all...would I possibly need some sort of refresh command?

 

[jkl0]

If you use the combo box as a filter for your subform instead of linking the Child & Master fields then you can accomplish what you want to do. I'm not quite sure if you can filter with your Child & Master fields linked.

 

[aeslabby]

How do I find the Master and Child links? I cannot find them for some reason...

Thanks for your response by the way!

 

[Joebax]

Open your main form and select the subform in the properties window drop down. Under the data tab you should see the linking fields.

I would try this:
Forget the main form and work only with your sub form (which would then be a main form).

Your record source will be set to all rows in the table or query that you are connected to.

You can then place a combo box with a record souce pointing to a customers query. Set the primary key as column 1 and the customer description as column two. Hide the key column by setting the width to 0. When I use the control wizard this is the default setup.

Now in the combo box after_update property change the forms record source to "original query text" + "where Customer_ID = " & cboCustomer.Value

The form should automatically refresh.

Next you can place a check box control that you can set for 'All Customers'. If you check this you can disable the combo box and change the record source back to the query text that will return all rows. When the box is unchecked you enable the combo box and force setting a value so that the after update event will fire and set your record source.
I use this method currently - Me.cboCustomers.Value = Me.cboCustomers.ItemData(0).

There are likely better ways to do this but thats what I would try.

 

[aeslabby]

Hello again!

Thanks for the assistance, but this doesn't seem to be working...since I have removed the subform, once I create a combo box, no matter what I do to it, it never filters anything at all and actually has changed my data in some cases within my table. I am so confused at this point! lol

Additionally, I am not sure what you mean re: the statement about the After Update property change...what is original query text? Shouldn't there be more code included here? I don't know much about coding, so apologies for the confusion.

Thanks and Regards,

Amy

 

[Joebax]

You should be working with what was your sub form. I am guessing the fields in this table would be "Functional Group/Task Status/Task Name/Task Description/WO #/Funded Hours/Notes". With a Customer_ID that links to your customer table.

When you run the form with no changes at all, you should be viewing all entried regardless of customer. If you use the wizard and choose tabular format. Or if you develop from scratch you should have the form set to Continous Form rather than Single Form.

When you have the form up in design view, make sure your properties window is open or else open it. There is a little button on the tool bar.

There is a drop down in the properties window. Make sure "Form" is selected. Check the record source property. It should read:
"Select Field1, Field2, ... FROM Table"
or
"Select * FROM Table"
Where Fieldx is your field names and Table is your table name.

If your record source is a table name or query definition then I would rebuild the record source using the ... next to the box in the properties window. If you rebuild it this way then you will have the query text as I show above available to view and change.

Now for the combo box if you use the wizard you need to be sure to choose the option to "Save for later use" rather than "Store in field..." Otherwise you can change your data in the tables. Also on the first page of the combo box wizard make sure you choose the first option and not the third.

Basically when the wizard finishes you should note the following properties. Row source for the combo box should say something like "Select Customer_ID, Customer_Name FROM Customers". The bound column should be 1 so that when you reference cboCustomers.Value it will return the Customer_ID. Your control source should be empty.

So if you run your form now you should have a combo box that that looks blank but when you open it should list all of your customer names. No matter what you choose you should be viewing all rows of the child table.

Back in design mode select the combo box and look at properties. Under the event tab you should see After Update. Click on the little ... button next to it and choose Code Builder and this should open up VBA.

In the sub routine you need the following code. Me.RecordSource =
"Select Field1, Field2, ... FROM Table where Customer_ID = " + cboCustomers.Value
or
"Select * FROM Table where Customer_ID = " + cboCustomers.Value"
Where Fieldx is your field names and Table is your table name.

This will filter you form to show only those records related to this customer. You can change customers and view only thier records.

Next you build the check box to show all records and have it checked by default in the form load event... If you havent found another solution already and you get up to this point I can explain how to have the check box change your form record source back and forth.

Hope this helps