Some VBA coding errors:
In the body of the function clm_thpe_dc = Empty; emple_id = 0; Basic = ""; Elected = ""
Imagine: When you "Dimmed" emple_id, you abrogated the use of empl_id from the Parameters section of the Function declaration. By not setting its value explicitly, it value was defaulted to zero.
The same Situation holds: The Default value of Basic and Elected is an empty string, or "". Variants default to Null
That makes your code "Translate" as
If rs1![0] = rs2![0] Then '<----------------
If rs2![] Like "BA*" Or "*ADD*'" Then '<----------------
rs1.Edit
rs1![] = "Basic" '<---------------------
rs1.Update
Else
If rs2![] Like "*opt*" Or "*vo*" Then '<----------------
rs1.Edit
rs1![] = "Optional" '<----------------
rs1.Update
Else
End If
End If
I don't do Access, but I suspect this (partial) Code would work better
Dim db As Database
Dim rs1 As Recordset
Dim rs2 As Recordset
Dim empleFirst 'Declared as variant. Will become same Type as rs1![emple_id]: String or Long, depending on Database
Set db = CurrentDb
Set rs1 = db.OpenRecordset("tbl02_test", dbOpenDynaset, dbSeeChanges)
Set rs2 = db.OpenRecordset("dbo_life_benefit_data_fact_t")
rs1.MoveFirst
empleFirst = rs1![emple_id]
rs2.MoveFirst
Do Until rs2.EOF
If rs2![emple_id] = empleFirst Then
If rs2![clm_typ_cd] Like "BA*" Or "*ADD*'" Then
rs1.Edit
rs1![Basic] = "Basic"
rs1.Update
ElseIf rs2![clm_typ_cd] Like "*opt*" Or "*vo*" Then
rs1.Edit
rs1![Elected] = "Optional"
rs1.Update
End If
End If
' select next record in res2 '<-----------------