Variable assignments to DAO field names

[GraeagleBill]

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:

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:

[GraeagleBill]

Changing the Dim to DAO.Field didn't change anything either: (Same error)
Attachment 49015

[moke123]

Why the quotation marks?

"rsPDF!Color"

[Micron]

No idea if you can make this work, but you don't enclose rs.Field references in quotes as a rule. You might be OK if you use rsPDF.Fields("Dte" & ColNum)

[GraeagleBill]

rsPDF!Fields("Dte" & ColNum) = " " doesn't work, nor did rsPDF!Fields(fldTxT) = " ", where fldTxt is string set to "TxT2". Both failed "item not in collection". Even rsPDF!Fields("TxT2") = " " fails, and yes rsPDF!Txt2 = " " assigns correctly.

As you say, maybe I can't get this to work. The reason I was doing this to begin with is that I need to populate the new recordset with the "longest" of the rsIN recordsets so that all subsequent assignments with ".Edit" > ".Update" looping won't overflow. I can think of a few other approaches that won't require duplication of a lot of code, so I'll think on that for a bit.

[Micron]

your attachment failed, by the way, so can't look for you.

[GraeagleBill]

I don't know what happened there, but it was simply a screenshot of another failure so doesn't matter.

[darkwind]

I don't believe you can change the name of fields in a recordset object. I think you'd need a tabledef to be able to set field names (and dynamically change the number of fields present, which ? it sounds like you may need to do). Something like:

   Dim dbs As DAO.Database, tdf As DAO.TableDef, fld As DAO.field
   Set dbs = CurrentDb
   Set tdf = dbs.CreateTableDef("tblNew")
   
   For Each fld In rsIn.fields
      tdf.fields.Append tdf.CreateField(fld.name & ColNum, fld.Type, fld.size)
   Next fld
   
   dbs.TableDefs.Append tdf
   
   Dim rsPDF As DAO.recordset
   Set rsPDF = dbs.OpenRecordset(tdf.name)
   rsPDF.AddNew
   rsPDF("fldTXT") = ""
   rsPDF.Update
   
   Select Case Occasion
      Case "Birthdays"
         intColr = 1
         rsPDF.AddNew
         rsPDF("fldColor") = intColr
         rsPDF("fldDte") = 40
         rsPDF("fldTXT") = "      " & Mth & " BIRTHDAYS      "
         
      Case "Anniversaries"
         
   End Select

But there's a lot you're doing here that I don't quite understand the reason for, so maybe I'm missing something about your objectives/scenario.

[Micron]

Are you sure it shouldn't be rsIn? I see no declared variable for rsPDF in what you posted.

[darkwind]

The syntax to reference the field name is rsPDF.Fields(0).name, but it's read-only. Trying to set that will get an invalid operation error.

[GraeagleBill]

The table already exists and is refreshed as multiple incidents occur. It is the object of the rsPDF Set.

[GraeagleBill]

rsPDF is global to the module and is Dim rsPDF as DAO.Recordset.

[Micron]

The table already exists and is refreshed as multiple incidents occur. It is the object of the rsPDF Set.

I have no idea to whom you're replying. The error is object variable or with block variable not set. I'm saying you don't show that you declared it anywhere.

[GraeagleBill]

Sorry, you had posted:

Are you sure it shouldn't be rsIn? I see no declared variable for rsPDF in what you posted.

I was intending to reply to you that rsPDF is global to the module and is both Dim'd DAO.Recordset and is "Set" to an existing table that is being populated by the code in the OP.

[darkwind]

The table already exists and is refreshed as multiple incidents occur. It is the object of the rsPDF Set.

Right, but you can't just re-name recordset fields using a recordset object... If you want to do that, you need to directly refer to the table definition.

If you want to read/set the value of a field, sure:

rs.Edit
rs("fieldName").Value = "whatever"
rs.Update

If you want to find the name of a field, sure:

Debug.Print rs.Fields(index).Name

But the Field.Name property is read-only if it's part of a collection. Fields in a table are always part of a collection (the tabledef.fields collection). See: https://learn.microsoft.com/en-us/of...e-property-dao

The proper syntax to change a field's name would be:

rs.Fields(0).Name = "FieldName"

But that won't work, it will throw the invalid operation error I mentioned before... because it's a read-only property in this context.

Aside from all of that, if you already have a table with all of this data... then I don't at all understand why you need to dynamically change the column names and/or number of columns. Isn't the whole point of a table that the number of columns and the type of data that goes in each column is constant? If you've got variable amounts of data of a given type, that should go in related tables, not in the main table- no? I might be able to give better advice with a better understanding of what you're doing.