By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,527 Members | 1,192 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,527 IT Pros & Developers. It's quick & easy.

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

P: 260
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.
{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!!!
Mar 27 '14 #1
Share this Question
Share on Google+
10 Replies

Seth Schrock
Expert 2.5K+
P: 2,951
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.
Mar 27 '14 #2

Expert Mod 15k+
P: 31,768
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.) :
Expand|Select|Wrap|Line Numbers
  1. Private Function NumToText(ByVal lngNum As Long) As String
  2.     NumToText = Choose(lngNum, "First" _
  3.                              , "Second" _
  4.                              , "Third" _
  5.                              , "Fourth" _
  6.                              , "Fifth" _
  7.                              , "Sixth")
  8. End Function
Let me know if you have any difficulty getting this to work for you.
Mar 27 '14 #3

P: 260
Would you be able to tell me exactly what to type in my code?
Here is what I have:
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Private Sub FrameOption1_AfterUpdate()
  4.  Select Case Me![FrameOption1]
  5.     Case 1
  6.       Me![txtValues] = "4.5x8.5"
  7.     Case 2
  8.       Me![txtValues] = "4.5x15.5"
  9.     Case 3
  10.       Me![txtValues] = "6x6"
  11.     Case 4
  12.       Me![txtValues] = "9.5x12"
  13.     Case 5
  14.       Me![txtValues] = "7 round"
  15.     Case 6
  16.       Me![txtValues] = "Unsure"
  17.   End Select
  18.   End Sub
  20. Private Sub FrameOption2_AfterUpdate()
  22.  Select Case Me![FrameOption2]
  23.     Case 1
  24.       Me![txtValues] = "4.5x8.5"
  25.     Case 2
  26.       Me![txtValues] = "4.5x15.5"
  27.     Case 3
  28.       Me![txtValues] = "6x6"
  29.     Case 4
  30.       Me![txtValues] = "9.5x12"
  31.     Case 5
  32.       Me![txtValues] = "7 round"
  33.     Case 6
  34.       Me![txtValues] = "Unsure"
  35.   End Select
  37. End Sub
  39. Private Sub FrameOption3_AfterUpdate()
  41.  Select Case Me![FrameOption3]
  42.     Case 1
  43.       Me![txtValues] = "4.5x8.5"
  44.     Case 2
  45.       Me![txtValues] = "4.5x15.5"
  46.     Case 3
  47.       Me![txtValues] = "6x6"
  48.     Case 4
  49.       Me![txtValues] = "9.5x12"
  50.     Case 5
  51.       Me![txtValues] = "7 round"
  52.     Case 6
  53.       Me![txtValues] = "Unsure"
  54.   End Select
  55. End Sub
Thank you Seth!!
Mar 27 '14 #4

P: 260
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!!**
Mar 27 '14 #5

Expert Mod 5K+
P: 5,397
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
Mar 27 '14 #6

P: 260
Ooohh, ok. Trying it now. Thank you.
That should have been obvious actually. haha
I crack myself up sometimes.
Mar 27 '14 #7

P: 260
Worked just like magic!!


Thanks for a quick solution guys. You are wonderful!!
Mar 27 '14 #8

Expert Mod 15k+
P: 31,768
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 :
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  4. Private Sub FrameOption1_AfterUpdate()
  5.     Me.txtValue1 = NumToText(Me.FrameOption1)
  6. End Sub
  8. Private Sub FrameOption2_AfterUpdate()
  9.     Me.txtValue2 = NumToText(Me.FrameOption2)
  10. End Sub
  12. Private Sub FrameOption3_AfterUpdate()
  13.     Me.txtValue3 = NumToText(Me.FrameOption3)
  14. End Sub
  16. Private Function NumToText(ByVal lngNum As Long) As String
  17.     NumToText = Choose(lngNum, "4.5x8.5" _
  18.                              , "4.5x15.5" _
  19.                              , "6x6" _
  20.                              , "9.5x12" _
  21.                              , "7 round" _
  22.                              , "Unsure")
  23. 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.
Mar 27 '14 #9

P: 260
Thank you NeoPa. Studying now. :-)
You always keep me straight...which really IS a difficult job. :-)
Mar 27 '14 #10

Expert Mod 15k+
P: 31,768
It's always a pleasure Danica. I've always enjoyed our interactions and hope to hear from you again soon :-)
Mar 28 '14 #11

Post your reply

Sign in to post your reply or Sign up for a free account.