VBA Question - find closest next number

B

Bonnie

Hi,

I have a form with a combobox control that list some sizes. They are:

12
18
20
22

I have another text box for keying a custom size, such as 12'2". What I
would like to do is have the combobox default to the next size (in this case
18). Here's the code I have used so far (it does a little more than what I
am asking for but to give an idea):

Dim MCUST As String

MCUST = Me.custwidth

If Str(Me.widthck) <> Me.custwidth Or Len(Me.custwidth) <> Len(Me.widthck)
Then

Me.widthck.Value = Int(Val(MCUST)) + 1

If InStr(Int(Me.widthck) / 2, ".") > 0 Then
Me.widthck = Me.widthck + 1
End If

If Me.widthck < 12 Then
Me.widthck = 12
End If

End If

If Len(Me.custwidth) = Len(Me.widthck) And Val(Me.custwidth) Mod 2 = 0 Then
Me.widthck = Me.custwidth
End If

Any help is appreciated.

Thanks,

Bonnie
 
K

Ken Warthen

Bonnie,

You might consider a function using a select case statement like as follows>

Public Function NextSize(dblActualSize As Double) As Integer
Select Case dblActualSize
Case Is <= 12
NextSize = 12
Case 12 To 18
NextSize = 18
Case 18 To 20
NextSize = 20
Case 20 To 22
NextSize = 22
Case Is > 22
NextSize = 22
End Select
Debug.Print "Next size is " & NextSize
End Function

Ken Warthen
[email protected]
 
B

Bonnie

Maybe I'll have to do that but I was hoping that I missed a function that
might locate the next highest number. There are just so many.

Thanks Ken.
 
A

Albert D. Kallal

The approach to this problem is to simply put a little bit of code in the
after update event of that control box (custom size), and have the code set
the value in the combo box.

You're also correct and your assumption to not use a hard coded case
statement as the other poster suggested.

I would try somthing like the following code in the after update event of
that text box. The following code simply selects the topmost or closest next
higher value from your given table.

Dim strSql As String
Dim rst As DAO.Recordset

If IsNull(Me.CustSize) = False Then

strSql = "select top 1 id from tblSizes where Size <= " & Me.CustSize
& _
" order by Size DESC"
Set rst = CurrentDb.OpenRecordset(strSql)
If rst.RecordCount > 0 Then
Me.Combobox = rst(0)
End If
rst.Close
Set rst = Nothing
End If

The above sample assumes that your combo box is actually a two collum combo
box in which you're displaying a value, but actually storing the primary key
or ID, this is a very typical setup in MS access .

If you only have one column for the combo boxes data source, then you can
simply modify the above a bit it should work quite fine.
 
Top