Loops in Access VBA

Question

I'm kind of new to Access VBA and I’m having issues with my loop.

I have reviewed various books, websites, asked various friends. Currently I’m trying to have a user input two characters, and then if the two characters equal a certain combination then it is supposed to equal a numeric value, and then use the value in a distance calculation.

The user inputed values are strings and everything else is declared as double.

I have 200+ combinations that I am testing and have tried case statements, DLookup, do while, do until, if, and elseif loops with no success.

I'm also limited by IT and I’m unable to use DAO code.

Any assistance would be greatly appreciated.

Answer 1

You can use following type function in your result form button or after event on both textboxes- Dim resultValue as Integer If DCount("numeric_value", "tblCombinations", "[combn1] = '" & Forms!myForm!myUserInputField1 & "' and [combn2] = '" & Forms!myForm!myUserInputField2 & "'") > 0 then

  resultValue = Dlookup("numeric_value", "tblCombinations", "[combn1] = '" & Forms!myForm!myUserInputField1 & "' and [combn2] = '" & Forms!myForm!myUserInputField2 & "'")

   txtResult = Distance_Calculation(resultValue) 

Else Msgbox "No such combination exist. Kindly check combimation", vbinformation, "Information"

  txtResult = 0

End if

Answer 2

Consider using Access as a database and GUI application by storing your 200+ combinations in a table with corresponding numeric value. Then have an SQL query filter by user input and use resulting value for needed calculations. Below requires creating and using four Access objects: table, query, form, and module.

Table (tblCombinations)

 id  combn1  combn2  numeric_value
  1       A       B            150  
  2       C       D            225
  3       E       F            100 
  4       G       H             75
  5       I       J            200
...

SQL (no loops needed; using Access form control values)

SELECT c.combn1, c.combn2, c.numeric_value
FROM tblCombinations c
WHERE c.combn1 = Forms!myForm!myUserInputField1
  AND c.combn2 = Forms!myForm!myUserInputField2

And even pass needed numeric value in distance calculation formula which can be a VBA function in a standard module:

SELECT c.combn1, c.combn2, Distance_Calculation(c.numeric_value) As distance_result
FROM tblCombinations c
WHERE c.combn1 = Forms!myForm!myUserInputField1
  AND c.combn2 = Forms!myForm!myUserInputField2

VBA (place in standard module)

Public Function Distance_Calculation(num_value As Long) As Double
   ...
   Distance_Calculation = some_value 
End Function

Answer 3

I would first suggest creating a table to formally define the various character combinations and the corresponding value for each combination. This has the advantage that the combinations may be easily maintained going forward, rather than defining such combinations as part of queries or in VBA code.

Such a table could be as simple as containing three fields:

• Character 1 (Text)
• Character 2 (Text)
• Value (Number)

You could then populate such a table with your valid combinations:

With the combinations rigorously defined, you have many options regarding how to prompt the user to enter the two characters and obtain the correponding value.

A very simplistic approach might be to use a form with three textboxes:

Here, the first two textboxes would be unbound, and might be called char1 and char2, and the third textbox might be called result and have a Control Source property equal to:

=DLookup("Value", "LookupTable", "char1 = '" & [char1] & "' and char2 = '" & [char2] & "'")

This would yield the following behaviour: