Help Using a Combo Box and Button to Filter data

[aeslabby]

Hi there!

I am pretty new at this so please bear with me (like that isn't said a thousand times!). 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

I would like to create a combo box where the user can select the customer (I have 124 records in total, 15 unique customer names) and all tasks relating to that customer are filtered directly in the Form by clicking a single button to do the filter.

I don't want to create dozens of tables and forms to flip through and such (users need it as simple as possible). I have created queries but the only way I have successfully filtered each customer is via buttons...but 15 buttons to filter each individual customer is a pain in the butt, so any help would be REALLY appreciated.

Please let me know if I haven't explained this clearly enough!

Thanks!

 

[ejstefl]

Look at post #13 in this thread. There is an example database.

http://www.access-programmers.co.uk/forums/showthread.php?t=99777&highlight=combo+box+filter

Just a tip - you could have saved yourself plenty of time had you tried a search before posting!

 

[aeslabby]

I actually did search, but I wasn't looking for the wording presented in that thread you provided...

Unfortunately the example you provided generates the data into a subform, and I do not want to do that, I need it to maintain one main form...so any ideas would be extremely helpful.

Thanks!

 

[aeslabby]

Hello again,

So I did end up using one of the databases in the above link for some guidance (thanks!)...but now I have another question. I would like all records to be unfiltered as a start up default, as currently, it is showing only a blank one, and only when I make the appropriate selection in the combo box does any records show up. Another issue is the fact that I attempted to create a button to "Show All Records" (as a macro, driven from a query) but it is not functioning at all. Can someone please tell me where I need to make the change so that I am able to see all records. FYI...the database I used as my example is attached. Mine is set up a little different, as I have more fields and I am using the Form for data entry as well.

THANKS!

 

[ansentry]

Do you have all your data in 1 table?

or do you have 2 tables (1 for Customer and 1 for Tasks) in a 1-Many relationship.

I may be wrong but I think that you need a look up combo instead of filter.

 

[aeslabby]

OH, I do have a look up combo box...and yes, all data is in one table. I only wanted something very simple, the problem is there are so many records, I wanted to be able to filter the data by program. The problem now is the fact that the combo box is working really well...but I cannot manage to remove all filters so that all records are available.

Thanks!

 

[ansentry]

I though that may be the case (1 Table). In my opinion that is your problem.

You should have a Customer Table and a Task Table. The relationship should be 1 customer to many tasks.

When you lookup up a customer (using your combo) you would find the customer record and listed below in the subform would be all the tasks for that customer.

 

[aeslabby]

Sorry for the ignorance...but you are suggesting I should have one table that is just the customer names, and the other with the customer name as well...but also all the task data? Does that mean I have to start my entire db over?

I also didn't want to have more than one screen...can the subform be made to look as if it is all one page? It is pertinent that I do not require the users to have to navigate multiple screens...the interface has to be very basic and user friendly.

Would I require an additional table if I wanted to filter by functional group as well? There are four 'departments' within the team and it would be very handy to be able to filter by group in addition to the other filters.

 

[ansentry]

tblCustomer
CustomerID (AutoNumber -Primary Key)
CusFName
CusSName
CusStreet
CusCity
CusPostCode
etc

tblTask
TaskID (AutoNumber-Primary Key)
CustomerID (Number -Foreign Key - from tblCustomer)
FunctionGroup
TaskStatus
TaskName
TaskDescription
WONumber
FundedHours
Notes


Now you would create a relationship between the 2 tables drag the CustomerID from the tblCustomer and to CustomerID and create a 1-Many relationship.

When you have done that using the wizard make a Main form and Subform.

Which all mean that 1 Customer can have Many tasks.

 

[aeslabby]

I don't require to track any customer address data and such ...but do I have to have a customer ID #? Just curious why I cannot create the relationship with the customer name directly...and also why I need to do this in the first place, as one table will have the customer name and nothing else. Can't I just create a subform using one table? And can I embed the subform in my main form somehow? I absolutely cannot have more than one screen for the users.

Also, I am familiar with queries and relationships (only thing I have done in Access prior to this) but I just want to be clear before I go ahead and start from scratch lol.

Any thoughts re: the capability to filter by functional group as well?

Thanks so much again, I truly appreciate your assistance!

 

[aeslabby]

So I have created a form and subform based very similarily to the suggestions above...but I have some additional concerns. When I open up the Main Form (where Subform resides)...my filters do work 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. 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.

Additionally, I would like an additional combo box 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!!!!!