I have a case where a sub needs to dynamically change the field name during an ADDNEW field assignment. I'm trying to use a Field object to make the name change then use that object in an assignment statement. This is my first time attempting such a case and obviously not understanding well enough.
I started by trying assignments like, "rsPDF![Color & ColNum] = 1", but such syntax is not valid, so I've been trying to resolve using a field object. Is that the method to use in a situation like this?
Also tried fldColor = "Color" & ColNum with an assignment like "rsPDF!fldColor = "but that fails as well.
The code:
Code:
Private Sub LoadBAtbl(rsIN As Recordset, ColNum As Integer, Mth As String, Occasion As String)
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
' PREPARING SPECIAL FORMATTING FOR TWO COLUMN REPORT RECORDSET:
' The input recordset rsIN contains all the data that is to be added to the table
' currently selected in the rsPDF recordset. Each single record in rsIN has multiple
' fields. Headers are added with dte values of 40 and 50 respectively, so that
' conditional formatting will respond as intended, as will the "Bs" and "As".
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
Dim intColr As Integer 'Color flag for conditional formatting
Dim fldColor As Field 'Need a field object to make assignments
Dim fldDte As Field 'Need a field object to make assignments
Dim fldTXT As Field 'Need a field object to make assignments
fldColor = "rsPDF!Color" & ColNum <================ Debug stops here
fldDte = "rsPDF!Dte" & ColNum
fldTXT = "rsPDF!Txt" & ColNum
rsPDF.AddNew 'blank line before header
rsPDF!fldTXT = " "
rsPDF.Update
Select Case Occasion
Case "Birthdays"
intColr = 1 'Non-Headers in blue
rsPDF.AddNew
rsPDF!fldColor = 1
rsPDF!fldDte = 40 'Header underline and blue
rsPDF!fldTXT = " " & Mth & " BIRTHDAYS "
rsPDF.Update
Case "Anniversaries"
<Snip>
The error: