I need multiple option groups on one form to populate corresponding control with text

[DanicaDear]

After a lot of searching and then even more trial and error, I made ADezii's very straightforward instructions in this post work for me.

http://bytes.com/topic/access/answers/676707-how-change-option-group-value-text

{Thank you so much ADezii. You have helped me so many times.}

However, I want to take this one step further.

I have an order form where people order plates in 6 different sizes. Those 6 sizes make my option group. However, many customers order two or three different sizes. So I have fields "Size1", "Size2", and "Size3" with 3 different options groups, but the options for the user to choose from on all three boxes are the same. My intent is to allow them to order 3 items on one form all at once. So I followed the directions here, but when I picked an option in OptionFrame2 Option Group, it only updated the Size1 box. (I understand why--because it's simply performing a match on the After_Update and the OptionFrameX is not really linked to SizeX in the code or properties.)

In case I'm not making sense, I want FrameOption1 to populate Size1 (with text, not a integer value), FrameOption2 to populate Size2, and FrameOption3 to populate Size3-----but remember, the six options are the same in all three option boxes.

Is it possible to extend ADezii's original train of thought here to fit my more complex needs? Or would we need to take a new approach? I'm a very basic user so simple is best. :-)

PS. If you are wondering why I would have an option group instead of a check box, it's because the order details for each size is different. A drop down would work as well but I think the option group is clearer for the user in my application.

Thanks in advance!!!

[Seth Schrock]

Each frame has its own set of events, so in the AfterUpdate event of FrameOption1, you would reference your Size1 field in each Case statement. FrameOption2 would reference your Size2 field in each Case statement and the same for Size3.

[NeoPa]

Hi Danica. Long time no see!

What I suggest you do for this is to create a function procedure that does the work for you and pass it a parameter for the Option Group value. The return value can then be stored in any of the string variables that you want.

Here is some example code to play with that illustrates what I'm explaining (For this I've replaced the Select Case code with a simpler call to Choose(), which is perfectly adequate for this requirement.) :

Private Function NumToText(ByVal lngNum As Long) As String
    NumToText = Choose(lngNum, "First" _
                             , "Second" _
                             , "Third" _
                             , "Fourth" _
                             , "Fifth" _
                             , "Sixth")
End Function

Let me know if you have any difficulty getting this to work for you.

[DanicaDear]

Would you be able to tell me exactly what to type in my code?
Here is what I have:

Option Compare Database
Private Sub FrameOption1_AfterUpdate()

 Select Case Me![FrameOption1]
    Case 1
      Me![txtValues] = "4.5x8.5"
    Case 2
      Me![txtValues] = "4.5x15.5"
    Case 3
      Me![txtValues] = "6x6"
    Case 4
      Me![txtValues] = "9.5x12"
    Case 5
      Me![txtValues] = "7 round"
    Case 6
      Me![txtValues] = "Unsure"
  End Select
  End Sub

Private Sub FrameOption2_AfterUpdate()

 Select Case Me![FrameOption2]
    Case 1
      Me![txtValues] = "4.5x8.5"
    Case 2
      Me![txtValues] = "4.5x15.5"
    Case 3
      Me![txtValues] = "6x6"
    Case 4
      Me![txtValues] = "9.5x12"
    Case 5
      Me![txtValues] = "7 round"
    Case 6
      Me![txtValues] = "Unsure"
  End Select

End Sub

Private Sub FrameOption3_AfterUpdate()

 Select Case Me![FrameOption3]
    Case 1
      Me![txtValues] = "4.5x8.5"
    Case 2
      Me![txtValues] = "4.5x15.5"
    Case 3
      Me![txtValues] = "6x6"
    Case 4
      Me![txtValues] = "9.5x12"
    Case 5
      Me![txtValues] = "7 round"
    Case 6
      Me![txtValues] = "Unsure"
  End Select
End Sub

Thank you Seth!!

[DanicaDear]

Would you be able to tell me exactly what to type in my code?
Here is what I have:

Option Compare Database
Private Sub FrameOption1_AfterUpdate()

 Select Case Me![FrameOption1]
    Case 1
      Me![txtValues] = "4.5x8.5"
    Case 2
      Me![txtValues] = "4.5x15.5"
    Case 3
      Me![txtValues] = "6x6"
    Case 4
      Me![txtValues] = "9.5x12"
    Case 5
      Me![txtValues] = "7 round"
    Case 6
      Me![txtValues] = "Unsure"
  End Select
  End Sub

Private Sub FrameOption2_AfterUpdate()

 Select Case Me![FrameOption2]
    Case 1
      Me![txtValues] = "4.5x8.5"
    Case 2
      Me![txtValues] = "4.5x15.5"
    Case 3
      Me![txtValues] = "6x6"
    Case 4
      Me![txtValues] = "9.5x12"
    Case 5
      Me![txtValues] = "7 round"
    Case 6
      Me![txtValues] = "Unsure"
  End Select

End Sub

Private Sub FrameOption3_AfterUpdate()

 Select Case Me![FrameOption3]
    Case 1
      Me![txtValues] = "4.5x8.5"
    Case 2
      Me![txtValues] = "4.5x15.5"
    Case 3
      Me![txtValues] = "6x6"
    Case 4
      Me![txtValues] = "9.5x12"
    Case 5
      Me![txtValues] = "7 round"
    Case 6
      Me![txtValues] = "Unsure"
  End Select
End Sub

Thank you Seth!!

Hi NeoPa!! I just saw your reply. If it wouldn't hurt your feelings too bad, and IF Seth's approach is simple as it sounds, I'd like to stick with it. When you start talking about passing stuff around I get all out of sorts. LOL!! I have already invested quite a few hours in getting my first option group to work, so the hard part (for me!) is already overcome.
**So happy to hear from you again!!**

[zmbd]

do you see this: Me![txtValues] = "4.5x8.5"

You need to change this so that it matches the name of your size fields:
Me![size1], Me![size2], Me![size3], etc...
The [txtValues] refers to the control of that name

[DanicaDear]

Ooohh, ok. Trying it now. Thank you.
That should have been obvious actually. haha
I crack myself up sometimes.

[DanicaDear]

Worked just like magic!!

Woohoooooo!!

Thanks for a quick solution guys. You are wonderful!!

[NeoPa]

It won't hurt my feelings at all Danica. I understand how you are with VBA.

Let me see if I can set your mind at rest on this one though. I'll post the code exactly as you need it so you can see how little there is to it :

Option Compare Database
Option Explicit

Private Sub FrameOption1_AfterUpdate()
    Me.txtValue1 = NumToText(Me.FrameOption1)
End Sub

Private Sub FrameOption2_AfterUpdate()
    Me.txtValue2 = NumToText(Me.FrameOption2)
End Sub

Private Sub FrameOption3_AfterUpdate()
    Me.txtValue3 = NumToText(Me.FrameOption3)
End Sub

Private Function NumToText(ByVal lngNum As Long) As String
    NumToText = Choose(lngNum, "4.5x8.5" _
                             , "4.5x15.5" _
                             , "6x6" _
                             , "9.5x12" _
                             , "7 round" _
                             , "Unsure")
End Function

I've assumed that the names of the TextBox controls are [txtValue1], [txtValue2] & [txtValue3], but you can change these easily if they are not correct.

NB. I very strongly advise you to follow the guidance found in Require Variable Declaration. It will make life a lot easier for you whenever you're working in code.

[DanicaDear]

Thank you NeoPa. Studying now. :-)
You always keep me straight...whic h really IS a difficult job. :-)

[NeoPa]

It's always a pleasure Danica. I've always enjoyed our interactions and hope to hear from you again soon :-)