Option Group - Access Developers - Computer Learning Zone


	Back to Access Developers Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Option Group

Dg Ewing

3 years ago

Using the option group works fine for the first 3 options, but when I click the 4th, 5th or 6th box nothing happends (event on after update is used for the group).  I have recreated the option group, verified that values are correct, and have a msg box for each value update. 1,2 and 3 work give msgbox and updates the forms filters.

Private Sub selectorBox_AfterUpdate()

If selectorBox = 1 Then
    passvalue = selectorBox - 1
    MsgBox passvalue
    DoCmd.Close acForm, "FamilyListF", acSaveYes
    DoCmd.OpenForm "FamilyListingF", , , "GrpID>" & passvalue

ElseIf selectorBox = 2 Then
     passvalue = selectorBox - 1
     MsgBox passvalue
     DoCmd.Close acForm, "FamilyListF", acSaveYes
     DoCmd.OpenForm "FamilyListingF", , , "GrpID=" & passvalue

ElseIf selectorBox = 3 Then
     passvalue = selectorBox - 1
     MsgBox passvalue
     DoCmd.Close acForm, "FamilyListF", acSaveYes
     DoCmd.OpenForm "FamilyListingF", , , "GrpID=" & passvalue

If selectorBox = 4 Then
   passvalue = selectorBox - 1
     MsgBox passvalue
     DoCmd.Close acForm, "FamilyListF", acSaveYes
     DoCmd.OpenForm "FamilyListingF", , , "GrpID=" & passvalue

ElseIf selectorBox = 5 Then
    passvalue = selectorBox - 1
     MsgBox passvalue
     DoCmd.Close acForm, "FamilyListF", acSaveYes
     DoCmd.OpenForm "FamilyListingF", , , "GrpID=" & passvalue

ElseIf selectorBox = 5 Then
    passvalue = selectorBox - 1
     MsgBox passvalue
     DoCmd.Close acForm, "FamilyListF", acSaveYes
     DoCmd.OpenForm "FamilyListingF", , , "GrpID>" & passvalue


   End If

End If
End Sub

	Dg Ewing OP @Reply 3 years ago
So, asking for help make me review from your standpoint, and there is a missing Else at selectorbox =4 and after fixing that , there is an extra End If. Thanks for being there....!!!

Scott Axton @Reply

3 years ago

First see this video:
Access Tip: Missing Option Value

Go back and review Access Expert 4 if you need to.

I don't use the option groups much. But I don't like using more than 3-4 choices.

Second:
Take a look at your code. You have an If in the middle of ElseIf.
AND two Option 5

Third:
You have a combination of "GrpID>" and "GrpID="
Be sure you are getting what you want. Are you sure you want the > (greater than) ?

	Scott Axton @Reply 3 years ago
I haven't tried this out because I don't have your db but... You really don't need all of the If... Else If. Obviously Back up your Database before trying anything. Try this out in a blank template or a copy of your form: `passvalue = selectorBox.value MsgBox passvalue ' DoCmd.Close acForm, "FamilyListF", acSaveYes ' DoCmd.OpenForm "FamilyListingF", , , "GrpID=" & passvalue`

Gregory Clancey @Reply

3 years ago

My mind is elsewhere, so sorry if I've missed the thrust of your issue; but, at initial glance, I'd say this is a good spot for a SELECT CASE construct. I know Rick has mentioned his preference for If. . .Else. . .IfElse. . .EndIf (whatever) and some instinct away from a SELECT CASE block, but it much simplifies your situation, here, and functions 100% the same way. I got to this ALWAYS, even for question resulting is simply "YES" or "NO." Am I off??

	Scott Axton @Reply 3 years ago
Greg and Dg - There isn't a need at all for Select Case or even the If Then. The whole block is the same except the value of the "GrpID=" & passvalue. Once you select a value for the Option Group it is set in the passvalue variable.

	Gregory Clancey @Reply 3 years ago
Great point -- well observed. No need to by new shoes. Sometimes the problem is simply that you've put the old pair on the wrong feet!

Dg Ewing OP @Reply

3 years ago

Thanks Scott and Gregory,

Scott I love your "one liner" without if else. but I was struggling with the logic for "all". and that is the reason on the first if that it grabs any value > 0, and on the last was just lazy coding showing any value > 4 which will be either 5 or 6 as that is all I have
GrpIDGroup
1Conifers,Cycades (Gymnosperms)
2Ferns (Pteridophytes)
3Flowering(Angiosperm)
4Moss and Liversort (Bryophytes
5Fungal Kingdom
6Algea

but none the less I can shorten to only one if or then.....pasvalue =selectorbox.value -1

	Dg Ewing OP @Reply 3 years ago

Scott Axton @Reply

3 years ago

Dg
I got bored and had some time to play.  As I mentioned above, I really haven't worked much with option groups.
This was a nice little Sunday distraction and gave me a chance to add to my tools in the tool box.

I'm not real sure the purpose of opening the FamilyListingF but I suspect it was to open a filtered down list of the "All List".

Any way this is what I came up with.  I just used Richard's blank template as a start.

Pretty basic tables but you should be able to follow along in the pictures and figure things out.  In a nut shell the option group is an unbound control on the main form.  The PlantListF is a continuous form that I just dropped into the MainMenuF.  The Code is put into the AfterUpate of the SelBox (The name of my option group.)

Take a look at the screen shots below and see if you can recreate it.  Then you should be able to put it into your db if you desire.

Any questions or comments let me know.

Scott

PS these were really useful for me:

Requery Subform
Value From a Form

	Scott Axton @Reply 3 years ago

	Scott Axton @Reply 3 years ago

	Scott Axton @Reply 3 years ago

	Scott Axton @Reply 3 years ago

	Scott Axton @Reply 3 years ago

Kevin Robertson @Reply

3 years ago

There is some duplicate code that can be eliminated in that Sub.
Here is how I would write this code:

Dim mySQL As String



If SelBox = 0 Then

   mySQL = "SELECT * FROM PlantListT;"

Else

   mySQL = "SELECT * FROM PlantListT " & _

         "WHERE GrpID = Forms!MainMenuF!SelBox;"

End If



Forms!MainMenuF!PlantListF.Form.RecordSource = mySQL

You don't need to set the Record Source for each condition and setting the Record Source requery's the form.

	Scott Axton @Reply 3 years ago
Kevin Thanks for chiming in. I either didn't know or forgot that setting the Record Source would automatically requery the subform. EDIT: Easy peasy! I did correct a typo in your code above. You had SeBox not SelBox. This is much more elegant code! Thanks!!!

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Developers.

Next Unseen

New Feature: Comment Live View