Loops with VBAccess:

I decided I wanted information from both the Donor table and the Contribu table to do this loop. Therefore, I set up a query to get information from both. As you can see, the query contains Idno and Name from the Donor table and AmtCont from the Contribu table.

I then decided to create a new form with this query as the data.

You can see that Idno, Name and AmtCont all came from the query while the Goal is unbound and brought in from the tool bar. I named the field where the goal will be put txtGoal.

Now I am going to put on a button to calculate the amount to put into txtGoal. I have decided that people ought to give the AmtCont quarterly so that is the calculation that I am doing within the loop. The code for the command button is shown below:

In this code, I first checked that the contribution did not contain null. This is checked by comparing to "" (no space in between). If the contribution was not null, I then used wkCt which I dimensioned as an integer to go through a loop four times and increase wkGoal (a work area defined as single) by the amount of the contribution. Also within the loop, I needed to increment wkCt by 1 so that eventually it would not be less than 4 and the loop would end. Notice the structure of the loop: Do While followed by the condition to terminate the loop and the word Loop to signify the logical end of the loop.

The result of the loop is shown above. In VB you can also write the loop as Do Until. The code for this is shown below.

 

Private Sub cmdOther_Click()  
  Dim wkCt As Integer, wkGoal As Single  
  If txtAmtCont <> "" Then  
      Do Until wkCt = 4  
          wkGoal = wkGoal + txtAmtCont  
          wkCt = wkCt + 1  
      Loop  
  Else  
      wkGoal = 1000  
  End If  
  txtGoal = wkGoal  
  End Sub 

CALL:

 

 

In the example above, I have now set up a third button that will executed cmdCallCalc_Click(). The only thing inside this subroutine is a call to Calc . The code says Call Calc. Calc was coded below. I typed in the Sub Calc( ) and then copied the code from the original button. Now this code can be executed with a call.

FUNCTION:

Now I want to do this as a function. To do that, I need to set up a function as shown below.

Function CalcFunc() As Single  
  Dim wkCt As Integer, wkGoal As Single  
  If txtAmtCont <> "" Then  
      Do While wkCt < 4  
          wkGoal = wkGoal + txtAmtCont  
          wkCt = wkCt + 1  
      Loop  
  Else  
      wkGoal = 1000  
  End If  
  CalcFunc = wkGoal  
  End Function  

Notice that instead of having Sub and End Sub, I now have Function and End Function. There are a few other changes to note:

What we have done here is set up a user defined function that will perform like other functions. When I do the function Val for example, I am converting data to numeric. When I do the function CalcFunc I am doing a process to determine an amount for the goal.Now I need to show how to execute the function:

Private Sub cmdCalcFunc_Click()

txtGoal = CalcFunc()

End Sub

What I am doing here is telling it to run the function CalcFunc and store the result in txtGoal.

The entire code for the four command buttons is:

Option Compare Database  
  Option Explicit  

  Private Sub cmdCalcFunc_Click()  
     txtGoal = CalcFunc()  
  End Sub  

  Private Sub cmdCalcGoal_Click()  
  Dim wkCt As Integer, wkGoal As Single  
  If txtAmtCont <> "" Then  
      Do While wkCt < 4  
          wkGoal = wkGoal + txtAmtCont  
          wkCt = wkCt + 1  
      Loop  
  Else  
      wkGoal = 1000  
  End If  
  txtGoal = wkGoal  
  End Sub  

  Private Sub cmdCallCalc_Click()  
      Call Calc  
  End Sub  

  Private Sub cmdOther_Click()  
  Dim wkCt As Integer, wkGoal As Single  
  If txtAmtCont <> "" Then  
      Do Until wkCt = 4  
          wkGoal = wkGoal + txtAmtCont  
          wkCt = wkCt + 1  
      Loop  
  Else  
      wkGoal = 1000  
  End If  
  txtGoal = wkGoal  
  End Sub  

  Sub Calc()  
  Dim wkCt As Integer, wkGoal As Single  
  If txtAmtCont <> "" Then  
      Do While wkCt < 4  
          wkGoal = wkGoal + txtAmtCont  
          wkCt = wkCt + 1  
      Loop  
  Else  
      wkGoal = 1000  
  End If  
  txtGoal = wkGoal  
  End Sub  

  Function CalcFunc() As Single  
  Dim wkCt As Integer, wkGoal As Single  
  If txtAmtCont <> "" Then  
      Do While wkCt < 4  
          wkGoal = wkGoal + txtAmtCont  
          wkCt = wkCt + 1  
      Loop  
  Else  
      wkGoal = 1000  
  End If  
  CalcFunc = wkGoal  
  End Function  

The final running form looks like this:

(Note: all 4 command buttons result in the same answer displayed in goal).