Post#10 won't work, so I devised this. It involves a public user defined type that will have universal scope.
You'll have to set up your 20 stages in fcnSetType, but only once. It doesn't use the public constants at all.
I wasn't able to test completely as I don't have the tables.
Put below in a module. The values will be available anywhere in your project
Code:
Option Compare Database
Option Explicit
Public Type usrComparator
Tmatch As String
Tvalue As String
End Type
Public myComp(20) As usrComparator
' execute below sub ONCE, via autoexec, or when opening the first form at BOJ
Public Sub subSetType()
myComp(0).Tmatch = "sample_stage_0"
myComp(0).Tvalue = "Sample Arrived"
myComp(1).Tmatch = "sample_stage_1"
myComp(1).Tvalue = "Ready for Extraction"
' and so on for all pairs
End Sub
Below is the code for the forms
Code:
'This will be the code behind any from needing it
Public Sub FindMatch()
Dim strSQL As String
Dim strSQL2 As String
Dim i As Integer
Dim rst As DAO.Recordset
Dim current_stage As String
strSQL = "SELECT * from current_status_tbl"
Set rst = CurrentDb.OpenRecordset(strSQL)
rst.MoveFirst
Do Until rst.EOF
'Count all official sample stages
For i = 0 To 19
If rst![sample_stage] = myComp(i).Tmatch Then
current_stage = myComp(i).Tvalue
Exit For
End If
Next i
strSQL2 = "SELECT * from animals_samples_current_qury where [sample_current_stage]='" & current_stage & "';"
Debug.Print strSQL2
rst.MoveNext
Loop
End Sub