help converting if to a case statement 4

[shelron]

I am a vba newbie,

I currently have an access 2000 database.

A form displays about 3000 records, in an effort to provide a user with smaller views of the data, I have a option group tied to a macro that applys filters. The filters are queries.

In an effort to make it faster and simpler, I would like to write code to do the filtering and eliminate the queries. I have about 15 possible criteria in the option group, but I am having trouble writing the syntax correctly. The following if statement works, but I believe a Case would be more appropriate. Can someone covert this to a case, If I can see it for two conditions, I am sure I can add the others.

Thanks for the help. Ron












Private Sub Frame138_AfterUpdate()

If Frame138.Value = 1 Then

DoCmd.ApplyFilter , "CONT_STYPE = 'county planning'"

End If


If Frame138.Value = 2 Then

DoCmd.ApplyFilter , "CONT_STYPE = 'financial'"



End If


End Sub

 

[essnrv]

Try this

Private Sub Frame138_AfterUpdate()
dim strFilter as string
Select case Frame138.Value
case 1
strFilter= "CONT_STYPE =county planning"
case 2
strFilter = "CONT_STYPE =financial"
end select
DoCmd.ApplyFilter ,strFilter


End Sub
Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need!

 

[jimmythegeek]

In this particular case, a select case will not speed it up that much since there are only 2 if statements. The difference is that the system will test every If statement it runs into (unless directed otherwise), and a Select Case will only read the matching case instructions, thus cutting down on the time it takes to process the code. This is definitely the case when you have several If statements (or more then several). It never hurts to do a select case when there are only a couple options however.

Here is the Select Case equivalent:

Note: You should name your objects appropriate names rather than leave them text11 or combo7 or such.


Select Case Me.Frame138
Case 1
DoCmd.ApplyFilter , "CONT_STYPE = 'county planning'"
Case 2
DoCmd.ApplyFilter , "CONT_STYPE = 'financial'"
End Select Jim Lunde
compugeeks@hotmail.com
We all agree your theory is crazy, but is it crazy enough?

 

[shelron]

Thank you both, this is what I came up with and it works great. This is my first case statement. I know, I know, but it's a big deal to me.

thanks for your valuable time, it's appreciated.

Ron




Private Sub Frame138_AfterUpdate()
Select Case Me.Frame138
Case 1
DoCmd.ApplyFilter , "CONT_STYPE = 'county planning'"
Case 2
DoCmd.ApplyFilter , "CONT_STYPE = 'financial'"

Case 3
DoCmd.ApplyFilter , "CONT_STYPE = 'c engineer'"

Case 4
DoCmd.ApplyFilter , "CONT_STYPE = 'a engineer'"

Case 5
DoCmd.ApplyFilter , "CONT_STYPE = 'counsel'"

Case 6
DoCmd.ApplyFilter , "CONT_STYPE = 'agency'"

Case 7
DoCmd.ApplyFilter , "CONT_STYPE = 'other'"

Case 8
DoCmd.ApplyFilter , "CONT_STYPE = 'environmental management council'"

Case 9
DoCmd.ApplyFilter , "CONT_STYPE = 'SOIL & WATER CONSERVATION DISTRICT'"

Case 10
DoCmd.ApplyFilter , "CONT_STYPE = 'nfp'"

Case 11
DoCmd.ShowAllRecords

Case 12
DoCmd.ApplyFilter , "CONT_TYPE = 'alternate'"

Case 13
DoCmd.ApplyFilter , "CONT_TYPE = 'interested party'"

Case 14
DoCmd.ApplyFilter , "CONT_TYPE is null"

Case 15
DoCmd.ApplyFilter , "CONT_STYPE = 'engineering firm'"

Case 16
DoCmd.ApplyFilter , "CONT_TYPE = 'primary'"

End Select




End Sub

 

[SBendBuckeye]

Excellent job. We all had to start at the beginning sometime. Don't lose the desire to learn. Just to extend your knowledge a little bit, you can also include a delimited list:

Case 1, 2, 4, 8, etc

a range:

Case 1 to 4, 5, 7 to 9, etc

algebraic test:

Case >= 4, etc

default:

Case Else

From a performance standpoint, put the most expected ones first as the rest of the code is skipped once a match is found.

Good Luck!


Please remember to give helpful posts the stars they deserve! This makes the post more visible to others in need!

 

[jimmythegeek]

Good points by Buckeye. One other thing, is if you are anal (like me) about saving space in code, but having to look appropriate, you can put your instruction after a colon on each select line. This will only work for single line instructions however.


Private Sub Frame138_AfterUpdate()
Select Case Me.Frame138
Case 1: DoCmd.ApplyFilter , "CONT_STYPE = 'county planning'"
Case 2: DoCmd.ApplyFilter , "CONT_STYPE = 'financial'"
Case 3: DoCmd.ApplyFilter , "CONT_STYPE = 'c engineer'"
Case 4: DoCmd.ApplyFilter , "CONT_STYPE = 'a engineer'"
Case 5: DoCmd.ApplyFilter , "CONT_STYPE = 'counsel'"
Case 6: DoCmd.ApplyFilter , "CONT_STYPE = 'agency'"
Case 7: DoCmd.ApplyFilter , "CONT_STYPE = 'other'"
Case 8: DoCmd.ApplyFilter , "CONT_STYPE = 'environmental management council'"
Case 9: DoCmd.ApplyFilter , "CONT_STYPE = 'SOIL & WATER CONSERVATION DISTRICT'"
Case 10: DoCmd.ApplyFilter , "CONT_STYPE = 'nfp'"
Case 11: DoCmd.ShowAllRecords
Case 12: DoCmd.ApplyFilter , "CONT_TYPE = 'alternate'"
Case 13: DoCmd.ApplyFilter , "CONT_TYPE = 'interested party'"
Case 14: DoCmd.ApplyFilter , "CONT_TYPE is null"
Case 15: DoCmd.ApplyFilter , "CONT_STYPE = 'engineering firm'"
Case 16: DoCmd.ApplyFilter , "CONT_TYPE = 'primary'"
End Select
End Sub
Jim Lunde
compugeeks@hotmail.com
We all agree your theory is crazy, but is it crazy enough?

 

[DaltonVB]

I know the horse is dead but here is one more kick.

Private Sub Frame138_AfterUpdate()
Dim szFilter As String
szFilter = Choose(Frame138.Value, _
"county planning", _
"financial", _
"more stuff&quot
DoCmd.ApplyFilter , "CONT_STYPE = '" & szFilter & "'"
End Sub

 

[shelron]

Okay jimmy the anal one , here is a new one I just wrote with my new found knowledge, thanks again,, ron

Private Sub optionmail_AfterUpdate()

'Apply Filter to list for which mailing contact is targeted.

Select Case Me.optionmail
Case 1: DoCmd.ApplyFilter , "update_form = true"
Case 2: DoCmd.ApplyFilter , "iupdraft = true"
Case 3: DoCmd.ApplyFilter , "iup = true"
Case 4: DoCmd.ApplyFilter , "iup1 = true"
Case 5: DoCmd.ApplyFilter , "iup2 = true"
Case 6: DoCmd.ApplyFilter , "special = true"
Case 7: DoCmd.ShowAllRecords

End Select

End Sub