Form filter with checkbox and combo not resetting (1 Viewer)

Lanser · Feb 21, 2019

Hi I have a form to display records but I cant get the filters I have set up to work consistently and revert when reset.

The Form has a combo dropdown to select a type of record and a tickbox to select All if unchecked and only records marked as Active if checked they are not connected so any combination should be possible but they dont always refresh sometimes needing 2 clicks sometimes close and reopen the form. I am sure its something simple but the last few hours google hasnt helped.

the SQL in the underlying query is

Code:

SELECT RegQuery.DocRef, DocVer.VerDate, DocVer.DocVerNo, DocMain.DocTitle, DocMain.DocDept, DocMain.DocType, DocMain.DocStatus
FROM DocMain RIGHT JOIN (RegQuery INNER JOIN DocVer ON (RegQuery.MaxOfDocVerNo = DocVer.DocVerNo) AND (RegQuery.DocRef = DocVer.DocRef)) ON DocMain.DocRef = DocVer.DocRef
WHERE ((([Forms]![Register]![CboType]) Is Null Or ([Forms]![Register]![CboType])=[DocType]))
ORDER BY RegQuery.DocRef;

the VBA used for the tickbox filter is

Code:

Private Sub CboType_Change()
Forms!Register.Requery
    'Me.CboType = Null
End Sub

Private Sub ChkActive_AfterUpdate()
    If ChkActive = True Then
        DoCmd.ApplyFilter , "[DocStatus] = True"
        Else
        Me.FilterOn = False
    End If
    
  Forms!Register.Requery
End Sub

Private Sub ChkActive_Click()
Forms!Register.Requery
End Sub

Private Sub Command30_Click()
Me.CboType = Null
Me.ChkActive = Null
Forms!Register.Requery
End Sub

Ranman256 · Feb 21, 2019

wouldn't it be:

Code:

Private Sub Command30_Click()
Me.CboType = Null
Me.ChkActive = Null
[B]Me.FilterOn = False[/B]
End Sub

Lanser · Feb 21, 2019

Thanks for the quick response, however I just tried that and it doesnt change.

regards
John

isladogs · Feb 21, 2019

Only have code in one event for the checkbox. I suggest the Click event.
A bound checkbox can't be null. Change to false.

I assume this code id in the form Register so just use Me.Requery

Lanser · Feb 22, 2019

Hi isladogs, that has almost works except now when I make a change in the combo I have to toggle the checkbox (click twice) to make the change show.

regards
John

isladogs · Feb 22, 2019

Try reversing the logic
Instead of saying if...=true, change that to false

Lanser · Feb 22, 2019

That just reverses the selection of active v all records
CboType doesn't seem to refresh the query on change until ChkActive is clicked

isladogs · Feb 22, 2019

I've had similar issues in the past. Its solvable if you experiment.
You could also try moving all the code back to after update instead of the click event.
Or try setting a default value?

arnelgp · Feb 22, 2019

firstly you need to change the source query:

Code:

SELECT RegQuery.DocRef, DocVer.VerDate, DocVer.DocVerNo, DocMain.DocTitle, DocMain.DocDept, DocMain.DocType, DocMain.DocStatus
FROM DocMain RIGHT JOIN (RegQuery INNER JOIN DocVer ON (RegQuery.MaxOfDocVerNo = DocVer.DocVerNo) AND (RegQuery.DocRef = DocVer.DocRef)) ON DocMain.DocRef = DocVer.DocRef
WHERE [DocType]=Switch(Nz([Forms]![Register]![CboType],"")="", [DocType], [Forms]![Register]![CboType])
ORDER BY RegQuery.DocRef;

next on the reset button:

Code:

Private Sub Command30_Click()
Me.CboType = Null
Me.ChkActive = Null
Me.FilterOn = False
Forms!Register.Requery

End Sub

Lanser · Feb 22, 2019

Hi Thanks but
Changing the query gives me a Reserved Error Item 3201 message

John

arnelgp · Feb 22, 2019

try using the Query designer, and type the Criteria on [DocType] field.

Lanser · Feb 26, 2019

Think I finally have it sorted

Code:

WHERE (((DocMain.DocType)=[Forms]![Register]![CboType]) AND (([Forms]![Register]![chkActive]) In ([Active],False)))
ORDER BY RegQuery.DocRef;

Form filter with checkbox and combo not resetting (1 Viewer)

Lanser

Registered User.

Ranman256

Well-known member

Lanser

Registered User.

isladogs

MVP / VIP

Lanser

Registered User.

isladogs

MVP / VIP

Lanser

Registered User.

isladogs

MVP / VIP

arnelgp

..forever waiting... waiting for jellybean!

Lanser

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

Lanser

Registered User.

Similar threads

Users who are viewing this thread