1

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.

Improve this question

3 Answers 3

Reset to default
1

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)

enter image description here

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

enter image description here

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:

enter image description here

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:

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

13 Comments

and you not using any DAO recordsets either! (I think that was a attempt to tie your hands, and using dlookup() in place of a DAO reocrdset is most excellent on your part!
Hi Lee. Thank you for everything! I’m having a slight problem though. I typed everything in just like you said Yoda, but it isn’t working for some reason. I’m getting an error of #Name? Do you have any advice on how to fix it? Thank you again for all of your help. I really do appreciate it.
@Divingmickey You're welcome. I assume that the #Name appears in the result textbox? If so, are the other two textboxes named char1 and char2?
Yes, the #Name? Error is in the text box. I have char1 = StartNode and char2 = EndNode, both are strings. Time is double like the rest of my wonderful numbers and my table is named NODES. What am I doing wrong Yoda?
Are your fields named StartNode & EndNode or your textboxes? Or both? Have you changed all elements of the DLookup expression appropriately, including the Value field name?
|
0

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
Improve this answer

1 Comment

Thank you all for all of your assistance. This has been driving me batty and turning my hair grey. Unfortunately, I’m still having issues getting the numerical results. I guess I will be dying my hair this weekend
0

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

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.