Access Filter with Combo Box based on Date

Sorry, forgot about the AfterUpdate event:

Sub cboYear_AfterUpdate()
  FilterIt
End Sub

Same would apply to other combos, as needed.

I know this is going to sound silly but I'm not totally sure where to apply that code to the combo. Sorry for the rookie questions.

When i create my combo box should i point the combo box to a table that holds the lookup values or should i choose the option to type in the option i want?

Your code: will that be applied to each of the drop down option field for the year or does that apply to the entire combo box control.

I've been messing with this on and off most of the day and i i guess I'm just not getting how this is done.

thanks for you patience.

Mark

Your combos would be based on Value Lists. Your Year combo would hold values like 2009, 2008, 2007 etc, while your month would hold values like 1, 2, 3 etc.

The FilterIt code would be a module in the form's Code Module. Your combos would call that procedure in the AfterUpdate event.

What version of Access are you using? If this is Access 2007, are you working with a MultiValue list?

To be honest i didn't know what a multivalue list was until you said it and i looked it up here.

http://office.microsoft.com/en-us/access/HA012337221033.aspx

Wow, what a good idea.. unless there is some issue with using them that you may know of.

To answer your question - My field only contains one value that is pulled from a MySql database table. See my project explanation below. I'm using Access 2007.


It might be easier if i explain my project a little bit.

Our website takes all of our "Quote Request" and saves a copy of them to a MySql database. I setup a ODBC connection in Access to retrieve the records.

The point in this was so i could append more data to each record to indicate which sale person handled the lead, status of the lead. Salesperson and Status is using a Lookup combo box to retrieve these records from 2 separate tables. Along with a Note field "memo type" and a hyperlink field to give a easy link to open our online management system.

I ran into issues that i didn't think of when i linked to the MySql table since this was my first time. I couldn't figure out a way to work directly with the linked table which makes sense now that i think about it, nor could i figure out away to setup a relationship with the linked table for the amended information that would be associated with the MySql record.

So what i did was create a Query that would only appended new records. Actually it was 2 Query's which run off of a single button I created, i may try and automate this later so no user interaction is required. This copied the records to a new table with matching fields and the additional fields for the amended data that will be added later via this form I'm working on with you. I'm not sure if this is good design or not but since part of my data is retrieved in real time i couldn't figure out any other way to do it. :)


I'm getting an error when i try to select a year value from the drop down.

the expression After Update you entered as the event property setting produced the following error:
Error:  Ambiguous name detected: Year_AfterUpdate

I created the module with the following code:

Function FilterIt()

Dim sFilter As String

If Me.Year.Column(0) <> "" Then
  sFilter = "Year(Submitted Date)=" & Me.Year
End If

If Me.Month.Column(0) <> "" Then
  If Len(sFilter) > 0 Then sFilter = sFilter & " AND "
  sfilter = sfilter & " Month(Submitted Date") = " & Me.Month      <-- this line is red like there might be something wrong.
End If

Me.Filter = sFilter
Me.FilterOn = True
End Function



My After_Update Code:


Private Sub Year_AfterUpdate()

Sub Year_AfterUpdate()
  FilterIt
End Sub

End Sub




My fields are named like this:

Year = Combo Box
Submitted Date = Table field with a single date "10/31/2008



thanks for your help.

Mark

First: Don't go down the path of MVF combos ... while they look neat, they're difficult to work with and until they've been "polished" somewhat, shouldn't be used in a production app (at least in my opinion).

If Me.Year.Column(0) <> "" Then
  sFilter = "Year([Submitted Date])=" & Me.Year
End If

If Me.Month.Column(0) <> "" Then
  If Len(sFilter) > 0 Then sFilter = sFilter & " AND "
  sfilter = sfilter & " Month([Submitted Date]) = " & Me.Month      <-- this line is red like there might be something wrong.
End If

Me.Filter = sFilter
Me.FilterOn = True
End Function

I'd STRONGLY encourage you to NOT use spaces in your table names, especially if you're working with a server-based backend. Instead of Submitted Date, name your column DateSubmitted - or better yet dtDateSubmitted, which immediately identifies your column as a DateTime column (i.e. the "dt" prefix). Look up "naming conventions" online for a thorough discussion of this concept.

Also use naming conventions in your Control names. Your combo name "Year" is a reserved word in Access, Jet and pretty much every other database engine out there, and can cause some issues if not handle properly. Instead of Year, name you combo something like cboYear, or cmbYear or something like that. The method you use is not as important as your consistent use of that method - that is no matter what naming convention you use, just make sure you USE it. You'll be glad you did, since this sort of coding error, which seems minor to many people, can have some significant impact as your project grows.

Thanks for your additional input on the naming conventions. I went thru this morning and corrected all of that. I used this guide to sort of give me an idea on how i should proceed.
http://www.mvps.org/access/general/gen0012.htm


Filtering Error Message:

Ok, i keep getting a error when i select one of the Year drop downs.

Compile error: Invalid outside procedure   <- this opens Microsoft Visual Basic Editor and high lights the "0" in the following first line of code:      If Me.cboYear.Column(0) <> "" Then

Here is my naming convention that i have used:

Year Combo Box:
cboYear
cboYear Combo Box also has the following properties:
Row Source: 2008;2009;2010
Row Source type: Value List

Month Combo Box:
cboMonth

Module:
sFilter    I wasn't sure about this one, i had it named something else but after looking at your code i thought this is what i should do.



This is my Module Code:

Option Compare Database

If Me.cboYear.Column(0) <> "" Then
  sFilter = "Year(dtSubmittedDate)=" & Me.cboYear
End If

If Me.cboMonth.Column(0) <> "" Then
  If Len(sFilter) > 0 Then sFilter = sFilter & " AND "
  sFilter = sFilter & "Month(dtSubmittedDate) = " & Me.cboMonth
End If

Me.Filter = sFilter
Me.FilterOn = True
End Function



cboYear After Update Event Procedure Code:

Sub cboYear_AfterUpdate()

  sFilter
 
End Sub

I assumed this was to match the Module name which is why i chose this for the module name.


Thanks Mr Consulting. :)


Mark

Ok, we are close. You were correct, i was using the stand alone module and not the Form Module. It took me little time to figure out how to add to a module without using an event but i think i got it now.

The Year sorts perfect but when i choose a month i get a pop message asking me to:
Enter Parameter Value
Jan

I have formatted my list for months like so:
Jan;Feb;Mar;Apr;etc

Also the reset button DOES clear the filter but it DOES NOT clear the selection from the Combo Boxes. Do i need to do some kind of refresh on the combo box or the entire form?

I know this is kind of running long, if you like I'll go ahead and award points and open a new question so i can give you the points you certainly have earned helping me. I would feel better about this if you would let me do this because i just noticed something else about the sort order of the date that i wanted to ask.

markj72000:
You may find several samples here to choose from:

http://www.granite.ab.ca/access/calendars.htm
http://allenbrowne.com/ser-51.htm
http://www.accessmvp.com/JConrad/accessjunkie/calendars.html
http://www.lebans.com/monthcalendar.htm

markj72000:

somehow the link below is no longer valid:
http://allenbrowne.com/ser-51.htm

thanks bobby but I'm not really sure how to use those controls to apply a filter to my form. I have gotten half of this to work with the Year but when i choose Month it asks me to input a value, i finally figured out that if i put in the corresponding month number like when i choose Mar from the drop down it ask for a value and i put in 3 it filters properly but after no changes to the drop downs have any effect on the filter.

thanks for your time. I'll post a new question with a better description of my total need from the filters.