Split form filter via combo boxes

[killermonkey]

This is my first post and I've googled for 2 days trying to figure it out. About to lose my mind trying to make this work so I've come to you all for help.

I have a database with over 4000 records in it. New records are input daily via a form. In an attempt to make this easier I wanted to be able to filter this form by month and year through combo box choices. It works...kinda. I have the combo boxes for both month and year and upon changing the month it filters accordingly. However, upon opening the form it asks me for a parameter value. If I type it in everything is kosher but I don't want to type it in every time and can't for the life of me figure out why it's doing it. Additionally the combo boxes are creating new columns on the split form. I don't need these and would prefer that they don't exist. I just want the combo boxes to act as filters not input any data. Lastly I'd like the filters to update whether there is a change to either combo box whereas as it is now it only updates on the month meaning if I change the year I have to go back and change the month for both to update properly. Here is the code I'm using for the combo boxes and attached is a picture of the split form.

Private Sub Month_Change()
'if nothing is filled
If IsNull(Me.Month) And IsNull(Me.Yr) Then
Me.FilterOn = False
Exit Sub
ElseIf Me.Month <> 0 And IsNull(Me.Yr) Then
'if only model is filled
Me.Filter = "MName='" & Me.Month & "'"
ElseIf IsNull(Me.Month) And Me.Yr <> 0 Then
'if only purpose is filled
Me.Filter = "TheYear=" & Me.Yr
ElseIf Me.Month <> 0 And Me.Yr <> 0 Then
'if both model and purpose are filled
Me.Filter = "MName='" & Me.Month & "'" & "AND TheYear=" & Me.Yr
Else: Exit Sub
End If


Me.FilterOn = True
End Sub

[Missinglinq]

What is the Record Source for the Form? If it references the Comboboxes, you can eliminate the Parameter Boxes by assigning Values to the Comboboxes in the Form_Load event.

You say

Here is the code I'm using for the combo boxes

but you only posted code for the Month Combobox. If you want the changing of the Year to trigger the re-filtering, you'll need similar code in the OnChange event of that Combobox, as well.

To be honest, this type of code really belongs in the AfterUpdate event of Comboboxes. The OnChange event fires every time a character is entered into a Control, and if a user tried entering a Month or Year through the keyboard, rather than using the dropdown and mouse, it would re-filter with each keystroke, and this could lead to unpredictable results.

Assuming that the Comboboxes are Unbound, as they should be (and appear to be) they are not 'inputting data.' If you don't want them showing on the Datasheet portion of the Form, you'll have to move them into the Header Section of the Form.

And lastly, 'Month' is the name of a Function, in Access VBA, and as such, probably is a poor name for the Combobox; you might want to change it to something else, like cboMonth, which keeps it from being mistaken for the Function, by the Access Gnomes, and tells other developers who might be working on this, at a later date, that it is the name of a Combobox.

Linq ;0)>

[killermonkey]

Linq,

Thank you for your reply because of it I was able to figure it all out.

I moved the comboboxes into the header...no more added columns

and used this code so that it would update regardless of which combobox I changed.

Private Sub cboYear_AfterUpdate()
'if nothing is filled
If IsNull(Me.cboMonth) And IsNull(Me.cboYear) Then
Me.FilterOn = False
Exit Sub
ElseIf Me.cboMonth <> 0 And IsNull(Me.cboYear) Then
'if only model is filled
Me.Filter = "MName='" & Me.cboMonth & "'"
ElseIf IsNull(Me.cboMonth) And Me.cboYear <> 0 Then
'if only purpose is filled
Me.Filter = "TheYear=" & Me.cboYear
ElseIf Me.cboMonth <> 0 And Me.cboYear <> 0 Then
'if both model and purpose are filled
Me.Filter = "MName='" & Me.cboMonth & "'" & "AND TheYear=" & Me.cboYear
Else: Exit Sub
End If


Me.FilterOn = True
End Sub


Private Sub cboMonth_AfterUpdate()
'if nothing is filled
If IsNull(Me.cboMonth) And IsNull(Me.cboYear) Then
Me.FilterOn = False
Exit Sub
ElseIf Me.cboMonth <> 0 And IsNull(Me.cboYear) Then
'if only model is filled
Me.Filter = "MName='" & Me.cboMonth & "'"
ElseIf IsNull(Me.cboMonth) And Me.cboYear <> 0 Then
'if only purpose is filled
Me.Filter = "TheYear=" & Me.cboYear
ElseIf Me.cboMonth <> 0 And Me.cboYear <> 0 Then
'if both model and purpose are filled
Me.Filter = "MName='" & Me.cboMonth & "'" & "AND TheYear=" & Me.cboYear
Else: Exit Sub
End If


Me.FilterOn = True
End Sub


Thank you for your help, very much appreciated.

[Missinglinq]

Glad we could help!

Linq ;0)>