473,326 Members | 2,081 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Apply Filter based on Selection of ComboBox

Okay I am using Access 2007 with several tables and several forms.

The main data is saved to table: Load_Records
There is a Form that updates that table named: Add New Loads To Database
There is another Form used for printing custom reports named: Generate Reports

The Generate Reports form automatically creates and populates a recordset by data binding to the Load_Records table. In the footer of this form there are three combo box's named: CompanySelect, DriverSelect and TruckSelect

The values of the combo box's are populated automatically from tables/columns named: Company!Company, Driver!Driver and Truck!Truck

What I want to do is have ALL THE RECORDS of Load_Records filtered automatically OnChange of any or all of these combo box's.

I have played around with DoCmd.ApplyFilter but have had no success in getting it to work correctly.
Nov 5 '08 #1
10 13432
ADezii
8,834 Expert 8TB
Here is the general idea, assuming all Text Fields:
Expand|Select|Wrap|Line Numbers
  1. Dim strFString As String
  2.  
  3. strFString = "[Company] = '" & Me![cboCompany] & "' And " & _
  4.              "[Driver] = '" & Me![cboDriver] & "' And " & _
  5.              "[Truck] = '" & Me![cboTruck] & "'"
  6. Me.Filter = strFString
  7. Me.FilterOn = True
Nov 5 '08 #2
Okay thanks for the tip but it isn't working for me.....here is a screen shot of the form. The Combobox items are named cboCompany, cboDriver, cboTruck and cboDate.

Here is the code I am using:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub btnFilter_Click()
  3.  
  4.     Dim strFString As String
  5.  
  6.     strFString = "[Company] = '" & Me![cboCompany] & "' And " & _
  7.                  "[Driver] = '" & Me![cboDriver] & "' And " & _
  8.                  "[mmddyy] = '" & Me![cboDate] & "' And " & _
  9.                  "[Truck] = '" & Me![cboTruck] & "'"
  10.     Me.Filter = strFString
  11.     Me.FilterOn = True
  12.  
  13. End Sub
  14.  
  15.  

Nov 5 '08 #3
ADezii
8,834 Expert 8TB
Assuming [mmddyy] is a Date/Time Field (should Rename!):
Expand|Select|Wrap|Line Numbers
  1. Dim strFString As String
  2.  
  3. strFString = "[Company] = '" & Me![cboCompany] & "' And " & _
  4.   "[Driver] = '" & Me![cboDriver] & "' And " & _
  5.   "[mmddyy] = #" & Me![cboDate] & "# And " & _
  6.   "[Truck] = '" & Me![cboTruck] & "'"
  7. Me.Filter = strFString
  8. Me.FilterOn = True
Nov 5 '08 #4
Still doesn't work....every time I click the Apply Filter button I get a single empty row in the recordset.

This is driving me bonkers.....
Nov 5 '08 #5
ADezii
8,834 Expert 8TB
Are there Records that actually meet the criteria making up the Filter?
Nov 6 '08 #6
Yes there are records that meet the criteria of the combo box selections.
Nov 6 '08 #7
ADezii
8,834 Expert 8TB
If I send you my E-Mail Address in a PM (Private Message), would you be willing to send me the Database, so that I could have a first hand look at it?

P.S. _ I only have Access 2007 in work, so I would have to wait until I can get some free time at work in order to diagnose the problem.
Nov 6 '08 #8
If I send you my E-Mail Address in a PM (Private Message), would you be willing to send me the Database, so that I could have a first hand look at it?

P.S. _ I only have Access 2007 in work, so I would have to wait until I can get some free time at work in order to diagnose the problem.
Sure, but I must warn you I am an amateur with Access in every sense of the word....
Nov 7 '08 #9
ADezii
8,834 Expert 8TB
I have the DB and will take a look as soon as I can.
Nov 8 '08 #10
ADezii
8,834 Expert 8TB
Sure, but I must warn you I am an amateur with Access in every sense of the word....
Here goes, venuspcs:
  1. Make sure that the On Click() Event of the Apply Filter has [Event Procedure] as its setting.
  2. Copy and Paste this exact code into the Click() Event of the Apply Filter Button. This code has been tested and is operational.
    Expand|Select|Wrap|Line Numbers
    1. Dim strFString As String
    2.  
    3. strFString = "[Company] = '" & Me![cboCompany] & "' And " & _
    4.              "[Driver] = '" & Me![cboDriver] & "' And " & _
    5.              "[mmddyy] = '" & Me![cboDate] & "' And " & _
    6.              "[Truck] = '" & Me![cboTruck] & "'"
    7.  
    8. Me.Filter = strFString
    9. Me.FilterOn = True
Nov 8 '08 #11

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: seansan | last post by:
Hi, suppose I have one report that reports some data to me based on a functional area that I define. We have about 20 func_areas and every time the report is the same. Is there some way to have...
1
by: Cassie Pennington | last post by:
I have tried to filter one combo box based on a selection in another combo box, by using the following sample code: tblfield=forms!!. However, the combobox only seems to recognise the first...
16
by: Nhmiller | last post by:
I already have a report designed under Reports. When I use filtering in Forms or Tables, I see no way to select that filtered list when I am in Reports, and there appears to be no way to do the...
3
by: google | last post by:
This is something I've done plenty of times in '97, but I can't seem to get it to work correctly in Access 2003. Say, for example, I have a form with an unbound combobox, the data source is a...
11
by: Bob | last post by:
I am in the process of upgrading an Access database to SQL Server (and climbing that learning curve!). The wizard happily upgraded all the tables and I can link to them OK using ODBC. The...
2
by: jujubean | last post by:
I have a form with five comboboxes. In the first, the user can select one of four items. The other four comboxes are hidden. Based on the selection made in combobox one, I would like one of the...
6
by: pouj | last post by:
what i want to do is basically have form where my selection in the combox make the diffrence with what the form reports. this is what the underlying informaion is.... software is access 2007 i...
11
by: billa856 | last post by:
Hi, I have project in MS Access.In that I have one form in which there is one combobox .I want to know when I select an item from that combobox and click on submit button then it should open a...
1
by: BSTAFFORD | last post by:
I need help. I have a form with a 5 comboboxes. the form also contains a subform query with the saved records from those 5 combobox categories. What I'd like to happen is as a new record is...
1
by: viranadim | last post by:
I am having problems with attempting to filter a form based on criteria selected in a combo box from another form. I am attempting to filter a form called "Ford CheckList" that is based on a table...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.