Complex Filter on ComboBox

  • Thread starter Thread starter tiger0268 via AccessMonster.com
  • Start date Start date
T

tiger0268 via AccessMonster.com

I am currently trying to add a filter to a form that shows all records. I
use the combo to add various types of filters to the form so I cannot change
the query behind it. I am attempting to create a filter that shows all
accounts that are pending. This is what I got so far:

Private Sub PendingCbo_Change()

If Me.PendingCbo.Value = "ComboValue" Then

Me.Filter = ([Forms]![FormName]![AccountRequested] = Yes) And (([Forms]!
[FormName]![AccountDenied] = No) Or ([Forms]![FormName]![AccountCreated] = No)
)
Me.FilterOn = True

Me.RcrdCnt.Value = Form.RecordsetClone.RecordCount
Me.RcrdCnt.Visible = True
Me.CloseBtn.SetFocus
End If
End Sub

The basic logic of the filter is to grab all records where a person has
requested for an account, but their account hasn't been created or denied
access. I am fairly good with VBA, but I am relatively new to using filters
so could someone just point me in the right direction.

Many Thanks!
 
You did not say what is or is not working or what error you are getting, if
any.
The only problem I see is the use of Yes and No. They are not VBA
constants. If you have defined them as constants yourself, then Yes should
be -1 and No should be 0.

Private Sub PendingCbo_Change()

If Me.PendingCbo.Value = "ComboValue" Then
Me.Filter = [Forms]![FormName]![AccountRequested] = True And ([Forms]!
[FormName]![AccountDenied] = False Or [Forms]![FormName]![AccountCreated] =
False)
Me.FilterOn = True
Me.RcrdCnt = Me.RecordsetClone.RecordCount
Me.RcrdCnt.Visible = True
Me.CloseBtn.SetFocus
End If
End Sub
 
It still will not pull up any records....Is there something else that could
be hindering this?
You did not say what is or is not working or what error you are getting, if
any.
The only problem I see is the use of Yes and No. They are not VBA
constants. If you have defined them as constants yourself, then Yes should
be -1 and No should be 0.

Private Sub PendingCbo_Change()

If Me.PendingCbo.Value = "ComboValue" Then
Me.Filter = [Forms]![FormName]![AccountRequested] = True And ([Forms]!
[FormName]![AccountDenied] = False Or [Forms]![FormName]![AccountCreated] =
False)
Me.FilterOn = True
Me.RcrdCnt = Me.RecordsetClone.RecordCount
Me.RcrdCnt.Visible = True
Me.CloseBtn.SetFocus
End If
End Sub
I am currently trying to add a filter to a form that shows all records. I
use the combo to add various types of filters to the form so I cannot change
Click to expand...
[quoted text clipped - 22 lines]
Many Thanks!
Click to expand...
Click to expand...
 
What values are in these controls:
[Forms]![FormName]![AccountRequested]
[Forms]![FormName]![AccountDenied]
[Forms]![FormName]![AccountCreated]

Is FormName the active form?

tiger0268 via AccessMonster.com said:
It still will not pull up any records....Is there something else that could
be hindering this?
You did not say what is or is not working or what error you are getting, if
any.
The only problem I see is the use of Yes and No. They are not VBA
constants. If you have defined them as constants yourself, then Yes should
be -1 and No should be 0.

Private Sub PendingCbo_Change()

If Me.PendingCbo.Value = "ComboValue" Then
Me.Filter = [Forms]![FormName]![AccountRequested] = True And ([Forms]!
[FormName]![AccountDenied] = False Or [Forms]![FormName]![AccountCreated] =
False)
Me.FilterOn = True
Me.RcrdCnt = Me.RecordsetClone.RecordCount
Me.RcrdCnt.Visible = True
Me.CloseBtn.SetFocus
End If
End Sub
I am currently trying to add a filter to a form that shows all records. I
use the combo to add various types of filters to the form so I cannot change
Click to expand...
[quoted text clipped - 22 lines]
Many Thanks!
Click to expand...
Click to expand...
Click to expand...
 
Yes, FormName is the active form. All three are just regular yes/no fields...
..I have not defined them to be anything else. I want the Requested field to
be set to Yes and the other two set to No.
What values are in these controls:
[Forms]![FormName]![AccountRequested]
[Forms]![FormName]![AccountDenied]
[Forms]![FormName]![AccountCreated]

Is FormName the active form?
It still will not pull up any records....Is there something else that could
be hindering this?
Click to expand...
[quoted text clipped - 23 lines]
Click to expand...
 
Doh! I don't know why I didn't see this earlier. Your filtering backwards.
What you need to filter on is the field in the form's recordset that matches
what is in the controls. So it should be something like this with the correct
field names:

Private Sub PendingCbo_Change()

If Me.PendingCbo.Value = "ComboValue" Then
Me.Filter = "[AccountRequested] = -1 And ([AccountDenied] = 0 Or
[AccountCreated] = 0")
Me.FilterOn = True
Me.RcrdCnt = Me.RecordsetClone.RecordCount
Me.RcrdCnt.Visible = True
Me.CloseBtn.SetFocus
End If
End Sub

Remember, in the Me.Filter, it is not the controls on the form, but the
field names in the form's recordset.
tiger0268 via AccessMonster.com said:
Yes, FormName is the active form. All three are just regular yes/no fields...
..I have not defined them to be anything else. I want the Requested field to
be set to Yes and the other two set to No.
What values are in these controls:
[Forms]![FormName]![AccountRequested]
[Forms]![FormName]![AccountDenied]
[Forms]![FormName]![AccountCreated]

Is FormName the active form?
It still will not pull up any records....Is there something else that could
be hindering this?
Click to expand...
[quoted text clipped - 23 lines]
Many Thanks!
Click to expand...
Click to expand...
Click to expand...
 
PMFJI,

You shouldn't need the Forms... references inside the filter. Your old
Filter was being evaluated as all true or all false, returning all or no
records, based on the form's current record. You just need the field
names on the left of the operators(=) in the filter.

It doesn't cost much to stick any SQL into a string variable and print
it out in the immediate pane. It may save you hours of debugging.

---air code---
If Me.PendingCbo.Value = "ComboValue" Then
Dim strFilter as String
strFilter = "([AccountRequested] = -1 AND [AccountDenied] = 0)"
strFilter = strFilter & " OR [AccountCreated] = 0"

'Put filter SQL into immediate pane to for test
Debug.Print strFilter

Me.Filter = strFilter
Me.FilterOn = True
....


HTH,

Kevin
 
I do not know how to use the immediate window, but here is my new code:

Private Sub PendingCbo_Change()
Dim strfilter As String
If Me.PendingCbo = "GCCS" Then
strfilter = "([GCCSDADS_Action] = 0 Or [GCCSDADS_Action_Denied] = 0)"
Me.Filter = strfilter & "[GCCSDADS_Reqd] = -1 And "
Me.FilterOn = True
Me.RcrdCnt.Value = Form.RecordsetClone.RecordCount
Me.RcrdCnt.Visible = True
Me.CloseBtn.SetFocus
End If
End Sub

I am now getting an error that says:

Run-time error '2448':

You can't assign a value to this object.

PMFJI,

You shouldn't need the Forms... references inside the filter. Your old
Filter was being evaluated as all true or all false, returning all or no
records, based on the form's current record. You just need the field
names on the left of the operators(=) in the filter.

It doesn't cost much to stick any SQL into a string variable and print
it out in the immediate pane. It may save you hours of debugging.

---air code---
If Me.PendingCbo.Value = "ComboValue" Then
Dim strFilter as String
strFilter = "([AccountRequested] = -1 AND [AccountDenied] = 0)"
strFilter = strFilter & " OR [AccountCreated] = 0"

'Put filter SQL into immediate pane to for test
Debug.Print strFilter

Me.Filter = strFilter
Me.FilterOn = True
...

HTH,

Kevin
I am currently trying to add a filter to a form that shows all records. I
use the combo to add various types of filters to the form so I cannot change
Click to expand...
[quoted text clipped - 22 lines]
Many Thanks!
Click to expand...
Click to expand...
 
Nevermind, I figured it out. I built a query that would show the correct
results and then used the Where condition from it as my filter.
I do not know how to use the immediate window, but here is my new code:

Private Sub PendingCbo_Change()
Dim strfilter As String
If Me.PendingCbo = "GCCS" Then
strfilter = "([GCCSDADS_Action] = 0 Or [GCCSDADS_Action_Denied] = 0)"
Me.Filter = strfilter & "[GCCSDADS_Reqd] = -1 And "
Me.FilterOn = True
Me.RcrdCnt.Value = Form.RecordsetClone.RecordCount
Me.RcrdCnt.Visible = True
Me.CloseBtn.SetFocus
End If
End Sub

I am now getting an error that says:

Run-time error '2448':

You can't assign a value to this object.
PMFJI,
Click to expand...
[quoted text clipped - 28 lines]
Click to expand...
 
Back
Top