I have a code which suppose to evaluate value in "eht_stage" column and based on it grab initials from different columns and display it on form.
For example if "eht_stage" = 3 then grab initials from column "eht_zoned". what I am getting is empty value although I know the initials are there.
Can't figure it out what's the problem is, I am not getting any errors, Case 0,1,2 works as it should.
HTML Code:
Option Compare Database
Option ExplicitDim db As DAO.database
'get initialsPublic Function funcEhtBy(EHT_Stage As Integer) As StringDim strEHT_By As String Dim RsEHT_By As DAO.Recordset 'get initials from table strEHT_By = "select EHT_Stage, EHT_Zoned_By, EHT_Designed_By, EHT_Drafted_By, EHT_Extracted_By, EHT_Modeled_By,EHT_Checked_By, EHT_Back_Drafted_By, EHT_Back_Checked_By, " & _ "EHT_Reviewed_By, EHT_IFC_By from tbl_Tracking"
Set RsEHT_By = CurrentDb.OpenRecordSet(strEHT_By) With RsEHT_By If Not .BOF And Not .EOF Then .MoveLast .MoveFirstWhile (Not .EOF) Select Case Nz(EHT_Stage, "")
Case 0, 1, 2funcEhtBy = "NotReq"
Case 3funcEhtBy = Nz(!EHT_Zoned_By, "0")
Case 4, 5funcEhtBy = Nz(!EHT_Designed_By, "0")
Case 6, 9funcEhtBy = Nz(!EHT_Drafted_By, "0")
Case 7funcEhtBy = Nz(!EHT_Extracted_By, "0")
Case 8funcEhtBy = Nz(!EHT_Modeled_By, "0")
Case 10, 11funcEhtBy = Nz(!EHT_Checked_By, "0")
Case 12funcEhtBy = Nz(!EHT_Back_Drafted_By, "0")
Case 13funcEhtBy = Nz(!EHT_Back_Checked_By, "0")
Case 14funcEhtBy = Nz(!EHT_Reviewed_By, "0")
Case 15funcEhtBy = Nz(!EHT_IFC_By, "0")Case ElsefuncEhtBy = "TestEmpty"
End Select .MoveNext Wend End IfEnd With RsEHT_By.Close Set RsEHT_By = Nothing End Function