FILTER problems using IF..THEN statements!! 1

[FuzzyBear9]

Hi,

I am trying to write some VB code in Access 97. I want to filter information depending on the user's selection. I have successfully created an options group, but it does not account for two types of scenarios:

I have a two forms, one called frmSurvey and the other frmTracking. On frmSurvey I have a command button that opens frmTracking with a filter applied, showing only those records with a matching SurveyID.

My options group on frmTracking has two settings: 'All' or 'Chase'.

Chase selects only those records that have their 'to be chased' tickbox ticked. When I click on All, the form displays all records with matching SurveyID.

Here is my code:

Private Sub FilterOptions_AfterUpdate()

If FilterOptions = 1 Then
Me.Filter = "SurveyID = Forms!frmSurvey.SurveyID"
Me.FilterOn = True
ElseIf FilterOptions = 2 Then
Me.Filter = "Chase = Yes"
Me.FilterOn = True
End If

End Sub

If I open Tracking by itself, the form displays all records independent of the survey type. When I select 'Chase' from the options group, the form limits the records correctly.

PROBLEM:
When I try and select 'All' from the options group, Access returns the following error:

Enter Parameter Value Forms!frmSurvey.SurveyID

I realise the problem and have can get the options group to work when frmTracking is opened by itself:

Private Sub FilterOptions_AfterUpdate()

If FilterOptions = 1 Then
Me.FilterOn = False
ElseIf FilterOptions = 2 Then
Me.Filter = "Chase = Yes"
Me.FilterOn = True 'Apply the filter
End If

But I do not know how to COMBINE the TWO??? I want to be able to click the 'All' option and either limit the records to those that have a matching survey type or display all the records independent of survey type depending on how I opened frmTracking.

Any help would be greatly appreciated!!!

 

[cascot]

Hi FuzzyBear9,

In a code module (so you can use it elsewhere in your application), or in the frmTracking form if you do not, put the following code:

Public Function IsFormLoaded(frmName As String) As Boolean

Dim i As Integer
For i = 0 To Forms.Count - 1
If (Forms(i).FormName = frmName) Then
IsFormLoaded = True
Exit Function
End If
Next i

End Function

Then your code should look something like this:

If FilterOptions = 1 Then
If IsFormLoaded("frmSurvey&quot Then
Me.Filter = "SurveyID = Forms!frmSurvey.SurveyID"
Me.FilterOn = True
Else
Me.FilterOn = False
End If
ElseIf FilterOptions = 2 Then
Me.Filter = "Chase = Yes"
Me.FilterOn = True 'Apply the filter
End If

Though if my understanding of what you are attempting to do is correct, you should also be including the SurveyId as part of the filter when FilterOptions = 2 (with the same check for frmSurvey form being open).

 

[FuzzyBear9]

Hi cascot,

It seems I have a LOT to learn! You are correct! I DO need to include SurveyID as part of the filter.

Do you know how I can do that? I tried using AND with Me.Filter, but Access returns a type 'mismatch error'.

Your code worked perfectly in identifying from where I was accessing frmTracking.

Thank you!!!

 

[cascot]

Hi FuzzyBear9,

Personally I would favour passing the SurveyId through from the frmSurvey form on the command button you use to open frmTracking, using OpenArgs, so something like:

DoCmd.OpenForm "frmTracking", acNormal,,,acFormEdit,acDialog, Me!SurveyId

[though you may want to change some of the above options, depending on your wishes]

Then in the General Declarations section of frmTracking, something like:

Dim SurvId as Long

In FormOpen, something like:

If not IsNull(me.OpenArgs) then SurvId = Me.OpenArgs

Then in FormLoad, something like:

FilterOptions_AfterUpdate

Then in the FilterOptions_AfterUpdate sub procedure, something like:

If FilterOptions = 1 Then
If SurId > 0 Then
Me.Filter = "SurveyID = " & SurvId
Me.FilterOn = True
Else
Me.FilterOn = False
End If
ElseIf FilterOptions = 2 Then
If SurId > 0 Then
Me.Filter = "Chase = Yes AND SurveyID = " & SurvId
Else
Me.Filter = "Chase = Yes"
End If
Me.FilterOn = True
End If