Variable assignments to DAO field names

[GraeagleBill]

I don't at all understand why you need to dynamically change the column names

I'm not trying to "change" column names, I'm trying to use the same code module to populate an existing table with fields already defined. E.g., like fields Txt1, Txt2, TxT3.... etc The input to the module are recordsets rsI that contain the data required to populate those fields.

[darkwind]

Gotcha. Sorry, the language used in OP had me quite confused on this.

So, to edit an existing record, simply:

rsPDF.Edit
rsPDF("txt1").Value = rsIn("txt1").value
rsPDF.Update

To add a new record:

rsPDF.Addnew
rsPDF("txt1").Value = rsIn("txt1").Value
rsPDF.Update

It sounds like you might be trying to dynamically match column names? I.e., update rsPDF so rsPDF("txt1") = rsIn("txt1"). If so, something like:

    Dim fld As DAO.Field, fld2 As DAO.field
    rsPDF.AddNew
    For Each fld In rsIn.Fields
        For Each fld2 In rsPDF.Fields
            If fld2.Name = fld.Name Then
                fld2.Value = fld.Value
                Exit For
            End If
        Next fld2
    Next fld
    rsPDF.Update

[Micron]

If it's declared you need to show or tell that in your first post.

dynamically change the field name during an ADDNEW field assignment

Before creating the rs, maybe. After - no - unless the field happens to be in the recordset to start with. In that case you're not arbitrarily trying to alter rs field names, you'd be dynamically choosing them, which is what I thought you were trying to do. You'd have to alter the query def sql or the table def field name and that just sounds crazy.

I think this is a case where it would help if we knew the what/why rather than trying to fix something that will never work. Or you go back one or two steps and take a new tack.

[GraeagleBill]

unless the field happens to be in the recordset to start with

Yes, as I posted earlier, the recordset rsPDF is based on an existing table with fields already defined.

I fear I've wasted everyone's time on this. In was trying to get a block of code to operate on the single record in the target recordset by modifying an assignment field name by appending an identifying value but none of the various expressions would work successfully.

The target recordset is based on a table with fields suffixed with numerical values, e.g., Txt1, Txt2, Txt3,..... Txtn. As data is gathered in the input recordset rsIN, I wanted the same block of code to be able to populate the fields by appending the suffix values much like we would do with form controls, e.g., Me.Controls("tbMyTB" & number).

[Micron]

Well I guess if we're going to figure out if that is possible, you'll have to post a sample db with instructions on what to run. I for one am watching World Series so I'm not going to be of any use tonight!

[darkwind]

I think I might finally be understanding what you want to do. You want to concatenate together a static field name with a dynamic column number to form the full name of the field in the recordset, correct? If so, this is the syntax you're after on the line of code that fails in your OP:

Set fldColor = rsPDF("txt" & ColNum)

You could also build the string into a variable:

Dim sFieldName as String
sFieldName = "txt" & ColNum
Set fldColor = rsPDF(sFieldName)

You can refer to fields in a recordset using !, but that won't let you concatenate strings together to form the field name the way you seem to want to.

I maintain that this is, in all probability, not the best way to accomplish what you're likely trying to accomplish. You might want to review if there's a better way to name your fields or a better way to pair your recordsets up. Shouldn't the two recordsets be related in some way - either having the same field names, having a primary key/foreign key relationship, or having static field relationships (e.g., rsIn("color") always pairs to rsPDF("hdrColor"), or similar)? In any of those cases there are very likely cleaner, more readable, and potentially faster ways of transferring the data between the two recordsets.

[Micron]

If it's about syntax or concatenation I'll be surprised. The object variable may have been declared but it is not Set and if it was, then it's another piece of the puzzle not shown. That's what the message means to me.

[GraeagleBill]

Set fldColor = rsPDF("txt" & ColNum)

Notice in the OP code and screenshot that trying to assign a concatenated string to a field object is missing the "Set".

Normally, table recordset situations such as the current one are best accomplished using the known relationships together with a union query. Were it not for the somewhat convoluted requirement of a multi-column report for which the current rsPDF becomes the reports RecordSource plus the added conditional formatting rules being applied, we might be having a totally different discussion. I'm just wanting to make the best of a somewhat difficult requirement.

I might come back and try to repair the OP code, but time dictates an alternative approach where I've duplicated some of the code.

Thanks to you both for your thoughts and help,
Bill

[Micron]

I'm referring to rsPDF not being Set, not fldColor. Anyway, since you don't want to go that route anymore, you might as well mark this one as solved so that others don't spend time reading through it all only to find out that you're done with it.

[GraeagleBill]

I have gone back to try out DarkWind's suggestion in post #21. Works like a champ.

(Snip)
Dim fldColor As DAO.Field
Dim fldDte As DAO.Field
Dim fldTxT As DAO.Field


Set fldColor = rsPDF("Color" & ColNum)
Set fldDte = rsPDF("Dte" & ColNum)
Set fldTxT = rsPDF("TxT" & ColNum)


Select Case Occasion
    Case "Birthdays"
        rsPDF.MoveFirst
       
        intColr = 1              'Non-Headers in blue
    
        rsPDF.Edit
        fldColor = 1
        fldDte = 40       'Header underline and blue
        fldTxT = "      " & UCase(Mth) & " BIRTHDAYS      "
        rsPDF.Update
        rsPDF.MoveNext


(Snip)