Taking in specific records and doing a calculation in Visual Basic
In this example, I developed a form to display a name and the total amount due but I wanted to do it differently depending on the type code. I have two buttons, one that takes in the type code and then retrieves the appropriate records and one that does the calculation. The purpose of the retrieval by type code is to the records being processed to those that have the appropriate code.
The form is shown below.
The Visual Basic that goes with the buttons is shown below. Note that in the module cmdCalc, I have defined a work area to hold the result of the addition. I then format the work area as currency before putting it on the form.
When coding cmdFind, I first defined wkTypeCode as a string field that is going to receive the code entered by the user in the Input Box. When the code comes in, I display it in a message box for no reason except for debugging purposes. I technique that I use a lot when I am writing the code. Additional comments will appear below the code.
Option Compare Database Private Sub cmdCalc_Click() Dim wkTotDue As Single wkTotDue = PastDue + Curr txtAmtDue = Format(wkTotDue, "Currency") End Sub Private Sub cmdFind_Click() Dim wkTypeCode As String wkTypeCode = InputBox("Enter Type Code") MsgBox (wkTypeCode) If wkTypeCode = "A" Then DoCmd.OpenForm "RetrieveForm", , , "[TypeCode]='A'" Else If wkTypeCode = "B" Then DoCmd.OpenForm "RetrieveForm", , , "TypeCode='B'" Else DoCmd.OpenForm "RetrieveForm", , , "TypeCode='C'" End If End If End Sub
After the user enters the code, I test it. I then use the DoCmd.OpenForm to retrieve records that meet the criteria. Note that the format calls for the name of the form that I am using - in this case RetrieveForm. This is followed by two parameters that I am not using. Finally I use the TypeCode name which is the name of the field on the file/table and retrieve records that match the code that was typed in. Notice that in the first example, I used the brackets [TypeCode] and in the others I simply used the name. The example below shows the records filtered to 2 because the code of A was used. Calculate Due has not been clicked at this point, the number here was left over from a previous retrieval.