Problems filtering recordset with combobox

  • Thread starter Thread starter hedgracer
  • Start date Start date
H

hedgracer

I have a combobox which has the following under the RowSource
property:

SELECT DISTINCTROW Onyx_Allocation.Month FROM Onyx_Allocation GROUP BY
Onyx_Allocation.Month ORDER BY Onyx_Allocation.Month;

There is only one column in the combobox which is populated with data
from the month column of the Onyx_Allocation table. The data is as
follows:

01/31/2010
02/28/2010

I have the following code under the AfterUpdate in the combobox:

Private Sub cboMthSelection_AfterUpdate()

Me.Form.Filter = "[Month] = '" & Me.cboMthSelection & "'"
Me.Form.FilterOn = True
End Sub

The problem is that when I select 01/31/2010 one or two rows of
02/28/2010 show up. The same thing happens when I select 02/28/2010
(one or two rows of 01/31/2010 show up). What is causing this? Any
help is appreciated. Thanks.
 
hedgracer said:
I have a combobox which has the following under the RowSource
property:

SELECT DISTINCTROW Onyx_Allocation.Month FROM Onyx_Allocation GROUP BY
Onyx_Allocation.Month ORDER BY Onyx_Allocation.Month;

There is only one column in the combobox which is populated with data
from the month column of the Onyx_Allocation table. The data is as
follows:

01/31/2010
02/28/2010

I have the following code under the AfterUpdate in the combobox:

Private Sub cboMthSelection_AfterUpdate()

Me.Form.Filter = "[Month] = '" & Me.cboMthSelection & "'"
Me.Form.FilterOn = True
End Sub

The problem is that when I select 01/31/2010 one or two rows of
02/28/2010 show up. The same thing happens when I select 02/28/2010
(one or two rows of 01/31/2010 show up). What is causing this? Any
help is appreciated. Thanks.
Click to expand...


What data type is this "Month" field? If it's a date/time field, you
probably need to use a filter expression like this:

Me.Form.Filter = "[Month] = #" & Me.cboMthSelection & "#"

"Month" is a bad name for a field, by the way, because it's the same as the
Month() function, and that can lead to confusion if you aren't very careful.
 
I have a combobox which has the following under the RowSource
property:
Click to expand...
SELECT DISTINCTROW Onyx_Allocation.Month FROM Onyx_Allocation GROUP BY
Onyx_Allocation.Month ORDER BY Onyx_Allocation.Month;
Click to expand...
There is only one column in the combobox which is populated with data
from the month column of the Onyx_Allocation table. The data is as
follows:

I have the following code under the AfterUpdate in the combobox:
Click to expand...
Private Sub cboMthSelection_AfterUpdate()
Click to expand...
Me.Form.Filter = "[Month] = '" & Me.cboMthSelection & "'"
Me.Form.FilterOn = True
End Sub
Click to expand...
The problem is that when I select 01/31/2010 one or two rows of
02/28/2010 show up. The same thing happens when I select 02/28/2010
(one or two rows of 01/31/2010 show up). What is causing this? Any
help is appreciated. Thanks.
Click to expand...

What data type is this "Month" field?  If it's a date/time field, you
probably need to use a filter expression like this:

    Me.Form.Filter = "[Month] = #" & Me.cboMthSelection & "#"

"Month" is a bad name for a field, by the way, because it's the same as the
Month() function, and that can lead to confusion if you aren't very careful.

--
Dirk Goldgar, MS Access MVP
Access tips:www.datagnostics.com/tips.html

(please reply to the newsgroup)- Hide quoted text -

- Show quoted text -
Click to expand...

Month is a varchar(50) field. It is part of a linked table on sql
server 2005. sorry I didn't explain that.
 
Month is a varchar(50) field. It is part of a linked table on sql server
2005. sorry I didn't explain that.
Click to expand...

I see. Then my earlier suggestion won't work, and it's not clear to me why
you are getting some unexpected records in the results. Your original
filter string looks reasonable. You do have an unnecessary ".Form"
qualifier:
Me.Form.Filter = "[Month] = '" & Me.cboMthSelection & "'"
Me.Form.FilterOn = True
Click to expand...
Click to expand...

In this context, "Me" is the form, so you just need

Me.Filter = "[Month] = '" & Me.cboMthSelection & "'"
Me.FilterOn = True

However, I don't think that has anything to do with the problem.

When you drop down the combo box, do you see dates that are formatted
exactly as you posted before:

That is, are they always formatted MM/DD/YYYY, with 2 digits for month and
day, and 4 digits for year?

Have you verified that the data in the [Month] field in the form's
recordsource -- a text field, so far as Access should be concerned -- is
also formatted exactly the same way?

Is the form based directly on the table, or is its recordsource a query? If
a query, what is the SQL of the query?
 
Month is a varchar(50) field. It is part of a linked table on sql server
2005. sorry I didn't explain that.
Click to expand...

I see.  Then my earlier suggestion won't work, and it's not clear to mewhy
you are getting some unexpected records in the results.  Your original
filter string looks reasonable.  You do have an unnecessary ".Form"
qualifier:
Me.Form.Filter = "[Month] = '" & Me.cboMthSelection & "'"
Me.Form.FilterOn = True
Click to expand...
Click to expand...

In this context, "Me" is the form, so you just need

    Me.Filter = "[Month] = '" & Me.cboMthSelection & "'"
    Me.FilterOn = True

However, I don't think that has anything to do with the problem.

When you drop down the combo box, do you see dates that are formatted
exactly as you posted before:

That is, are they always formatted MM/DD/YYYY, with 2 digits for month and
day, and 4 digits for year?

Have you verified that the data in the [Month] field in the form's
recordsource -- a text field, so far as Access should be concerned -- is
also formatted exactly the same way?

Is the form based directly on the table, or is its recordsource a query?  If
a query, what is the SQL of the query?

--
Dirk Goldgar, MS Access MVP
Access tips:www.datagnostics.com/tips.html

(please reply to the newsgroup)
Click to expand...

The drop down on the combobox has the dates formatted exactly like I
posted them. The data in the Month field in the form's recordsource is
the same format as the format in the combobox. The data in the
combobox is from a query in the RowSource property that I posted
earlier.

I removed the Form as you stated. Now the row count is okay for
01/31/2010 (no 02/28/2010 bleeding through) but there is still
01/31/2010 rows bleeding through when 02/28/2010 is selected in the
combobox.
 
hedgracer said:
The drop down on the combobox has the dates formatted exactly like I
posted them. The data in the Month field in the form's recordsource is the
same format as the format in the combobox. The data in the combobox is
from a query in the RowSource property that I posted earlier.
Click to expand...

And the recordsource of the form?
I removed the Form as you stated. Now the row count is okay for 01/31/2010
(no 02/28/2010 bleeding through) but there is still
Click to expand...
01/31/2010 rows bleeding through when 02/28/2010 is selected in the
combobox.

Hmm. That doesn't really make sense to me as being the result of that one
change. You aren't working in an ADP, are you?

After you have assigned to the form's .Filter property based on a combo box
value of "02/28/201", please verify and post the value of the .Filter
property.

Does it make any difference if you change the code to:

Me.FilterOn = False
Me.Filter = "[Month] = '" & Me.cboMthSelection & "'"
Me.FilterOn = True

?
 
Back
Top