Array's and select case statements

[Machiaveli]

Hello,

Could somebody provide me an example code about using an arry in combination with a select case statement.

What i would like to achieve is that when i'm using a lookup table, the array should be filled with those values. Then in the select statement, the array would be the case.
This way i don't have to hardcode any data-values in vba.
The purpose of al this is having cleaner code.

For example:
I have a form where i insert physicaldata like length, weight, etc. and examinationsubject.
When i use a combobox for looking up the examintionsubject, the validationrule for weight must be set.
The criteria for this are examinationsubject, gender and the range where the weightvalue is valid (between 5500 and 9000).

the should be like

array(1 month, 2 month, etc.) 'filled by the a lookup table

select case array(1 month) and array(male)

case some value in the array
weight.validationrule = between (some value in the lookuptable) and (some value in the lookup table)
length.enabled = false (depending of the case)

I hope i was clear about my explaination.

 

[tinymind]

Hi Machiaveli

Here is an example of an array with a select statement to get you started ...

Public Sub arr_slt()
Dim i As Integer
Dim location
Dim here_i_am

location = Array("MP", "RE", "TW", "YA", "WA&quot

For i = 0 To UBound(location)

here_i_am = location(i)

Select Case here_i_am
Case "MP"
MsgBox "I am here at MP"
Case "RE"
MsgBox "I am here at RE"
Case "TW"
MsgBox "I am here at TW"
Case "YA"
MsgBox "I am here at YA"
Case "WA"
MsgBox "I am here at WA"
End Select

Next i
End Sub

Tinymind

Perfection is Everything
If it worked first time we wont be here!

 

[Machiaveli]

Thanx for your help, but isn't it possible to not to hardcode the locations in your code?
For example if the array is extended with more values, then you also must update the code.

Or is this not possible?

 

[tinymind]

Hi Machiaveli ... sorry for the late reply

Are you storing the data in a table? If so, here is an example of a sql select and loop function so that you can test each variable as it is passes through until all the variables have been completed ...

Public Sub arr_slt()

Dim db As Database
Dim get_location As Recordset
Dim location
Dim here_i_am

Set db = CurrentDb

'you can use a select statement on a table
Set get_location = db.OpenRecordset("SELECT location_tbl.location_id, location_tbl.location_name FROM location_tbl;&quot

'check whether the sql has returned any results
If get_location.RecordCount > 0 Then

location = get_location!location_name
' loop arround all the values until the query has finished
Do
here_i_am = get_location!location_name

Select Case here_i_am
Case "MP"
MsgBox "I am here at MP"
Case "RE"
MsgBox "I am here at RE"
Case "TW"
MsgBox "I am here at TW"
Case "YA"
MsgBox "I am here at YA"
Case "WA"
MsgBox "I am here at WA"
End Select
get_location.MoveNext
Loop Until get_location.EOF
Else
'no records found
get_location.Close
db.Close
Exit Sub
End If
get_location.Close
db.Close
End Sub

Tiny

Perfection is Everything
If it worked first time we wont be here!