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

How do I Insert a Value in a Field in a table with a toggle button

P: 29
I am trying to dynamically change the value of a filed using a toggle button in a form it is not a yes or no question. I want it to change a value in a table based on it"s state. Toggled on="6 inches" Toggled off=<null>. I want to more then one toggle button on the form each one dynamically changing the value of the table in real time. How do I do this?
Oct 15 '07 #1
Share this Question
Share on Google+
6 Replies


ADezii
Expert 5K+
P: 8,597
I am trying to dynamically change the value of a filed using a toggle button in a form it is not a yes or no question. I want it to change a value in a table based on it"s state. Toggled on="6 inches" Toggled off=<null>. I want to more then one toggle button on the form each one dynamically changing the value of the table in real time. How do I do this?
In the AfterUpdate() Event of your Toggle Button, place code sililiar to this:
Expand|Select|Wrap|Line Numbers
  1. Private Sub tglButton_AfterUpdate()
  2. Select Case Me![tglButton]
  3.   Case True
  4.     Me![txtSomeField] = "6 inches"
  5.   Case Else
  6.     Me![txtSomeField] = Null
  7. End Select
  8. End Sub
Oct 15 '07 #2

P: 29
To make it simple I used the Options Group Wizzard. Now I have a form with toggle buttons that have a numerical value associated with them but now my problem is I need to associate a Text value for each numerical value. Example
if value of Size in table orders =1 then value of Size in table orders = "6 inches"
if value of Size in table orders=2 then value of Size in table orders= "12 inches"
What is the VBA script to do this?
Oct 15 '07 #3

ADezii
Expert 5K+
P: 8,597
To make it simple I used the Options Group Wizzard. Now I have a form with toggle buttons that have a numerical value associated with them but now my problem is I need to associate a Text value for each numerical value. Example
if value of Size in table orders =1 then value of Size in table orders = "6 inches"
if value of Size in table orders=2 then value of Size in table orders= "12 inches"
What is the VBA script to do this?
Expand|Select|Wrap|Line Numbers
  1. Private Sub tglButton_AfterUpdate()
  2. Select Case Me![tglButton]
  3.   Case 1
  4.     Me![txtSomeField] = "6 inches"
  5.   Case 2
  6.     Me![txtSomeField] = "12 inches"
  7.   Case Else
  8.     'fall through
  9. End Select
  10. End Sub
Oct 15 '07 #4

P: 29
I noticed two things about On Afterupdate....1 is the toggle button doesn't stay down when clicked and 2 my subform doesn't auto update unless I click on it. Is there anyway to fix this?

Thanks for the infomation
Oct 17 '07 #5

P: 29
Here is my VB code so far
Expand|Select|Wrap|Line Numbers
  1. Private Sub EXTRA_Frame_AfterUpdate()
  2. Select Case Me![EXTRA]
  3.   Case 1
  4.     Me![EXTRA] = "CHICKEN"
  5.     Me![SUB TOTAL] = Me![SUB TOTAL] + 2.5
  6.   Case 2
  7.     Me![EXTRA] = "DONAIR MEAT"
  8.     Me![SUB TOTAL] = Me![SUB TOTAL] + 2.5
  9.   Case 3
  10.     Me![EXTRA] = "CHEESE"
  11.     IIf Me![SIZE] = 2, Me![SUB TOTAL] = Me![SUB TOTAL] + 1, Me![SUB TOTAL] = Me![SUB TOTAL] + 0.5
  12.   Case 4
  13.     Me![EXTRA] = "SAUCE"
  14.     Me![SUB TOTAL] = Me![SUB TOTAL] + 0.5
  15.   Case 5
  16.     Me![EXTRA] = "BACON BITS"
  17.   Case 6
  18.     Me![EXTRA] = "HOT PEPPERS"
  19.   Case 7
  20.     Me![EXTRA] = "SALAD"
  21.   Case 8
  22.     Me![EXTRA] = "BREAD"
  23.   Case 9
  24.     Me![EXTRA] = "RICE"
  25.   Case Else
  26.    'fall through
  27. End Select
  28. End Sub
  29. __________________________________________
  30. Private Sub Frame_Drinks_AfterUpdate()
  31. Select Case Me![DRINKS]
  32.   Case 100
  33.     Me![DRINKS] = "Coke"
  34.     Me![SUB TOTAL] = [SUB TOTAL] + 1.5
  35.   Case 101
  36.     Me![DRINKS] = "Diet Coke"
  37.     Me![SUB TOTAL] = [SUB TOTAL] + 1.5
  38.   Case 102
  39.     Me![DRINKS] = "Pepsi"
  40.     Me![SUB TOTAL] = [SUB TOTAL] + 1.5
  41.   Case 103
  42.     Me![DRINKS] = "Diet Pepsi"
  43.     Me![SUB TOTAL] = [SUB TOTAL] + 1.5
  44.   Case 104
  45.     Me![DRINKS] = "7-Up"
  46.     Me![SUB TOTAL] = [SUB TOTAL] + 1.5
  47.   Case 105
  48.     Me![DRINKS] = "Five Alive"
  49.     Me![SUB TOTAL] = [SUB TOTAL] + 1.5
  50.   Case 106
  51.     Me![DRINKS] = "Orage Crush"
  52.     Me![SUB TOTAL] = [SUB TOTAL] + 1.5
  53.   Case 107
  54.     Me![DRINKS] = "Root Beer"
  55.     Me![SUB TOTAL] = [SUB TOTAL] + 1.5
  56.   Case 108
  57.     Me![DRINKS] = "Ice Tea"
  58.     Me![SUB TOTAL] = [SUB TOTAL] + 1.5
  59.   Case 109
  60.     Me![DRINKS] = "Apple Juice"
  61.     Me![SUB TOTAL] = [SUB TOTAL] + 1.5
  62.   Case 110
  63.     Me![DRINKS] = "Water"
  64.     Me![SUB TOTAL] = [SUB TOTAL] + 1.5
  65.   Case 111
  66.     Me![DRINKS] = "Lg. Choco Milk"
  67.     Me![SUB TOTAL] = [SUB TOTAL] + 2
  68.   Case 112
  69.     Me![DRINKS] = "Lg. White Milk"
  70.     Me![SUB TOTAL] = [SUB TOTAL] + 2
  71.   Case 113
  72.     Me![DRINKS] = "Sm. White Milk"
  73.     Me![SUB TOTAL] = [SUB TOTAL] + 1.25
  74.   Case Else
  75.     'fall through
  76. End Select
  77. End Sub
  78. _______________________________________________
  79. Private Sub Frame_SUB_SIZE_AfterUpdate()
  80. Select Case Me![SIZE]
  81.   Case 1
  82.     Me![SIZE] = "6 inch"
  83.     Me![SUB TOTAL] = 5
  84.   Case 2
  85.     Me![SIZE] = "12 inch"
  86.     Me![SUB TOTAL] = 7.5
  87.   Case Else
  88.    'fall through
  89. End Select
  90. End Sub
  91. ________________________________________________
  92. Private Sub Frame_SUB_TYPE_AfterUpdate()
  93. Select Case Me![TYPE]
  94.   Case 3
  95.     Me![TYPE] = "CHICKEN"
  96.   Case 4
  97.     Me![TYPE] = "DONAIR"
  98.     Me![SAUCE] = "DONIAR"
  99.   Case 5
  100.     Me![TYPE] = "STEAK"
  101.   Case Else
  102.     'fall through
  103. End Select
  104. End Sub
  105. ________________________________________________
  106. Private Sub SALAD_SIZE_FRAME_AfterUpdate()
  107. Me![ITEM] = "SALAD"
  108. Me![BREAD] = Null
  109. Select Case Me![SIZE]
  110.  Case 1
  111.     Me![SIZE] = "SMALL"
  112.     Me![SUB TOTAL] = 4
  113.  Case 2
  114.     Me![SIZE] = "LARGE"
  115.     Me![SUB TOTAL] = 5.5
  116.  Case Else
  117.   'fall through
  118. End Select
  119. End Sub
  120. _________________________________________________________
  121. Private Sub SALAD_TYPE_Frame_AfterUpdate()
  122. Select Case Me![TYPE]
  123.   Case 1
  124.    Me![TYPE] = "GREEK"
  125.   Case 2
  126.    Me![TYPE] = "CEASAR"
  127.   Case 3
  128.     Me![TYPE] = "GARDEN"
  129.   Case Else
  130.    'fall through
  131. End Select
  132. End Sub
  133. _______________________________________________
  134. Private Sub Stir_Fry_Type_Frame_AfterUpdate()
  135. Me![ITEM] = "STIR FRY"
  136. Me![BREAD] = Null
  137. Me![SIZE] = Null
  138. Me![SUB TOTAL] = 8
  139. Select Case Me![TYPE]
  140.  Case 3
  141.   Me![TYPE] = "CHICKEN"
  142.  Case 4
  143.   Me![TYPE] = "DONAIR"
  144.  Case 5
  145.   Me![TYPE] = "STEAK"
  146.   Case Else
  147.     'fall through
  148. End Select
  149. End Sub
  150. ________________________________________________
  151. Private Sub SUBS_page_Click()
  152. Me![ITEM] = "SUB"
  153. End Sub
  154. __________________________________________________
  155. Private Sub WRAPS_Frame_AfterUpdate()
  156. Me![ITEM] = "WRAP"
  157. Me![SIZE] = Null
  158. Me![SUB TOTAL] = 5
  159. Select Case Me![TYPE]
  160.   Case 1
  161.     Me![TYPE] = "CHICKEN DONAIR"
  162.     Me![SAUCE] = "DONIAR"
  163.   Case 2
  164.     Me![TYPE] = "CHICKEN SOUVLAKI"
  165.     Me![SAUCE] = "SOUVLAKI"
  166.   Case 3
  167.     Me![TYPE] = "CHICKEN FAJITA"
  168.     Me![SAUCE] = "SALSA"
  169.   Case 4
  170.     Me![TYPE] = "CHICKEN CEASAR"
  171.     Me![SAUCE] = "CEASAR"
  172.   Case 5
  173.     Me![TYPE] = "CHICKEN BACON RANCH"
  174.     Me![SAUCE] = "BACON RANCH"
  175.   Case Else
  176.    'fall through
  177. End Select
  178. End Sub
It is bad code I know but It's all I underestand so far and would appreciate any tips to reduce it"s size.
Oct 18 '07 #6

ADezii
Expert 5K+
P: 8,597
Because of the mutually exclusive nature of the choices, and the number of choices, Select Case...End Select is an excellent choice, Personally I may shorten and consolidate the code in this following manner. I may get some flack on this because placing multiple commands on the same line is usually not a good idea, but in this case I feel as though it may be appropriate. Let's wait and see what the rest of the gang has to say on the matter.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Frame_Drinks_AfterUpdate()
  2. Dim ctlDrk As Control, ctlSub As Control
  3.  
  4. Set ctlDrk = Me![txtSearch]
  5. Set ctlSub = Me![txtSearch2]
  6.  
  7. Select Case ctlDrk
  8.   Case 100
  9.     ctlDrk = "Coke": ctlSub = ctlSub + 1.5
  10.   Case 101
  11.     ctlDrk = "Diet Coke": ctlSub = ctlSub + 1.5
  12.   Case 102
  13.     ctlDrk = "Pepsi": ctlSub = ctlSub + 1.5
  14.   Case 103
  15.     ctlDrk = "Diet Pepsi": ctlSub = ctlSub + 1.5
  16.   Case 104
  17.     ctlDrk = "7-Up": ctlSub = ctlSub + 1.5
  18.   Case 105
  19.     ctlDrk = "Five Alive": ctlSub = ctlSub + 1.5
  20.   Case 106
  21.     ctlDrk = "Orage Crush": ctlSub = ctlSub + 1.5
  22.   Case 107
  23.     ctlDrk = "Root Beer": ctlSub = ctlSub + 1.5
  24.   Case 108
  25.     ctlDrk = "Ice Tea": ctlSub = ctlSub + 1.5
  26.   Case 109
  27.     ctlDrk = "Apple Juice": ctlSub = ctlSub + 1.5
  28.   Case 110
  29.     ctlDrk = "Water": ctlSub = ctlSub + 1.5
  30.   Case 111
  31.     ctlDrk = "Lg. Choco Milk": ctlSub = ctlSub + 2
  32.   Case 112
  33.     ctlDrk = "Lg. White Milk": ctlSub = ctlSub + 2
  34.   Case 113
  35.     ctlDrk = "Sm. White Milk": ctlSub = ctlSub + 1.25
  36.   Case Else
  37.     'fall through
  38. End Select
  39. End Sub
NOTE: Sorry for deleting your original code but my Reply would not display.
Oct 18 '07 #7

Post your reply

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