Query that rolls up to next highest value

[randommetalguy]

Hey guys,

I will be coding something over the next few days and I'm not so sure how to go about doing it.

The user can select a number from a drop down and the tension will be calculated in another box. But, if the user inputs the tension, then the program needs to pick the appropriate value from the drop down box.

So here's my examples:
Input 5.0 into a textbox and 1000 is selected from a drop down. 200 is the tension and is stored into a text box.
You could also input 10.0 into a textbox and 2000 from the drop down and 200 will be the tension.

So understanding that information let's say my one input is 10.0 and I input 300 as the tension. So my program will want to look up 3,000 in my drop down. How do I code that.

Also let's say that 3,000 is not in the dropdown and the only values are 2500, 2800, and 3200 how I can I code to tell the program to go to the next highest break, if the calculated value is not there?

 

[randommetalguy]

I came up with a semi solution, can you guys let me know what you think? In one standard I do increments by 10 and the other I can do increments by 100 which will be a little faster. Do you think this method is too slow? The code below is for AS standard and I have to do increments of 10 because some of the STNo's are 2240, 1120, etc. The other standards I have to do only have values such as 5400, 4600, etc so I can do jumps of 100.

If (Me.cboStandardSTNO.Value = "" Or IsNull(Me.cboStandardSTNO)) And Me.txtStandardSafetyFactor.Value <> "" And Me.txtStandardWorkingTension.Value <> "" Then
calculatedStandardNumber = Round((((Me.txtStandardSafetyFactor * Me.txtStandardWorkingTension) + 0.5) / 10), 0) * 10
Dim found As Boolean
Me.cboStandardSTNO.RowSource = ""

Do Until found = True Or calculatedStandardNumber > 6300

STNO = "ST-" & CStr(calculatedStandardNumber)


If Me.cboStandard.Value = "AS" Then
strSTNOSQL = "SELECT DISTINCT ASStandardInputQ.ASSTNo FROM ASStandardInputQ "
strSTNOSQL = strSTNOSQL & " WHERE ASStandardInputQ.ASSTNo = '" & STNO & "';"

Me.cboStandardSTNO.RowSource = strSTNOSQL

If (IsNull(Me.cboStandardSTNO.ItemData(0))) Then
calculatedStandardNumber = calculatedStandardNumber + 10
Else
found = True
Me.cboStandardSTNO = Me.cboStandardSTNO.ItemData(0)
Call cboStandardSTNO_AfterUpdate
End If

End If
Loop
End If