tfoss_78
07-13-2021, 01:33 PM
By way of introduction, I have no coding background and am as green as they come with Access VBA. My organization produces data in such a way that a single entry (shown below as "emple_id" below) will appear in multiple rows on the same table, with variable data ("clm_typ_cd") appearing in a separate row opposite the emple_id. In an effort to produce query results where emple_id and all corresponding clm_typ_cd information appears on the same row, I initially created a new table ("tbl102_test") where each iteration of emple_id appeared only once via the "group by" command. I then tried to create a query that would use the formula below to produce results (i.e. emple_id, clm_typ_cd, clm_typ_cd) all on one row:
Option Compare Database
Public Function basic_optional(ByRef emple_id As Long) As String
Dim db As Database
Dim rs1 As Recordset
Dim rs2 As Recordset
Dim clm_typ_cd As Variant
Dim emple_id As Long
Dim Basic As String
Dim Elected As String
Set db = CurrentDb
Set rs1 = db.OpenRecordset("tbl02_test", dbOpenDynaset, dbSeeChanges)
Set rs2 = db.OpenRecordset("dbo_life_benefit_data_fact_t")
rs1.MoveFirst
Do Until rs1.EOF
rs2.MoveFirst
Do Until rs2.EOF
If rs1![emple_id] = rs2![emple_id] Then
If rs2![clm_typ_cd] Like "BA*" Or "*ADD*'" Then
rs1.Edit
rs1![Basic] = "Basic"
rs1.Update
Else
If rs2![clm_typ_cd] Like "*opt*" Or "*vo*" Then
rs1.Edit
rs1![Elected] = "Optional"
rs1.Update
Else
End If
End If
End If
rs2.MoveNext
Loop
rs1.MoveNext
Loop
rs1.Close
rs2.Close
Set rs1 = Nothing
Set rs2 = Nothing
db.Close
End Function
At this point I'm getting the "This expression is typed incorrectly, or it is too complex to be evaluated..." error. Given how little I know about the subject matter at hand, I'm concerned that I'm just pouring my time into a hole with the hope of stumbling onto the right answer. I honestly have no idea whether any of what I've produced above will even work (it passed the debug test), or even whether I'm on anything resembling the right track. ANY help with this would be greatly appreciated, even if it's just some advice around how to start over.
Option Compare Database
Public Function basic_optional(ByRef emple_id As Long) As String
Dim db As Database
Dim rs1 As Recordset
Dim rs2 As Recordset
Dim clm_typ_cd As Variant
Dim emple_id As Long
Dim Basic As String
Dim Elected As String
Set db = CurrentDb
Set rs1 = db.OpenRecordset("tbl02_test", dbOpenDynaset, dbSeeChanges)
Set rs2 = db.OpenRecordset("dbo_life_benefit_data_fact_t")
rs1.MoveFirst
Do Until rs1.EOF
rs2.MoveFirst
Do Until rs2.EOF
If rs1![emple_id] = rs2![emple_id] Then
If rs2![clm_typ_cd] Like "BA*" Or "*ADD*'" Then
rs1.Edit
rs1![Basic] = "Basic"
rs1.Update
Else
If rs2![clm_typ_cd] Like "*opt*" Or "*vo*" Then
rs1.Edit
rs1![Elected] = "Optional"
rs1.Update
Else
End If
End If
End If
rs2.MoveNext
Loop
rs1.MoveNext
Loop
rs1.Close
rs2.Close
Set rs1 = Nothing
Set rs2 = Nothing
db.Close
End Function
At this point I'm getting the "This expression is typed incorrectly, or it is too complex to be evaluated..." error. Given how little I know about the subject matter at hand, I'm concerned that I'm just pouring my time into a hole with the hope of stumbling onto the right answer. I honestly have no idea whether any of what I've produced above will even work (it passed the debug test), or even whether I'm on anything resembling the right track. ANY help with this would be greatly appreciated, even if it's just some advice around how to start over.