Option buttons tested in VB for Access

The following examples use the Muffin Table:

There are two examples, in both the form looks like this:

I decided to leave the frame name as frame3 to illustrate the fact that this is a frame containing the three option buttons. The option buttons have values of 1, 2, 3. Once it is know which button was clicked, the price is multiplied by the number to give the total due. Notice that the totaldue is on the form.

The Visual Basic that is executed when the CalculateTotalDue button is clicked is shown below. Note that that the first button on the form is named Corn, the second button is named Bran and the third button is named Pumpkin. I decided to use MouseDown as the event that would trigger activity. The activity is to assign the price to wkPrice which was defined in the general area. Remember, defining in the general area means it is available to all modules. The information in parenthesis after the MouseDown even was all automatically coded by Access VB. The only thing that I coded was the assignment to wkPrice. I assigned the appropriate price to wkPrice and then when the cmdCalc was clicked the wkPrice is multiplied by the value in NumOrder and the result is stored in totaldue. Totaldue is a field on the form that is bound to a field on the file.

Option Compare Database   
  Dim wkPrice As Single   

  Private Sub Bran_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)   
      wkPrice = 0.95   
  End Sub   

  Private Sub cmdCalc_Click()   
      totaldue = wkPrice * Val(NumOrder)   
  End Sub   

  Private Sub Corn_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)   
     wkPrice = 0.9   
  End Sub   

  Private Sub Pumpkin_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)   
     wkPrice = 1   
  End Sub

A second way of doing this processing involves using an array. The VB code is shown below. In the general area I defined the array as MuffinPrice(3) and I defined two variables that are used in the processing. The Form_Load() event establishes the values in the array. The MouseDown event assigns the value 1 for the first button, the value 2 for the second button and the value 3 for the third button. The price is taken from the array using the value assigned to MuffinSelection as the Index to the array. The price is them multiplied by the MuffinQty to give the total due. Notice that the value in the NumOrder is assigned to MuffinQty when the field looses focus. I could actually have used NumOrder in the calculation, but I could not have .Text. The .Text is only valid when the field has focus. I did it the Lost_Focus way simply to use another event.

Option Compare Database   
   Dim MuffinPrice(3) As Single   
   Dim MuffinSelection As Integer   
   Dim MuffinQty As Integer   

   Private Sub cmdCalc_Click()   
       totaldue = MuffinPrice(MuffinSelection) * MuffinQty   
   End Sub   

   Private Sub Form_Load()   
       MuffinPrice(1) = 0.9   
       MuffinPrice(2) = 0.95   
       MuffinPrice(3) = 1   
          
   End Sub   

   Private Sub Muffin_1__MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)   
       MuffinSelection = 1   
   End Sub   

   Private Sub Muffin_2__MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)   
       MuffinSelection = 2   
   End Sub   

   Private Sub Muffin_3__MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)   
       MuffinSelection = 3   
   End Sub   

   Private Sub NumOrder_LostFocus()   
       MuffinQty = Val(NumOrder.Text)   
   End Sub