variable used in DAO Recordset edit expression

[GraeagleBill]

Updating records in a DAO Recordset:

!["F" & strSunCol] = Format(DteValue, "dddd, mmmm, dd, yyyy")

The field names are of the form "F2", "F3",,,,,,etc. The numerical suffix isn't known until the code runs at entry. What's the syntax of such an assignment?

[Gicu]

Have you tried Me.Controls("F" & strSunCol)?
Cheers,

[GraeagleBill]

I'm updating a DAO Recortset, not a control. I had just found the answer when your post came in.

        .Edit
        strFieldName = "F" & strSunCol
        .Fields(strFieldName) = Format(DteValue, "dddd, mmmm, dd, yyyy")
        
        strFieldName = "F" & strSunCol + 1
        .Fields(strFieldName) = Format(DteValue + 1, "dddd, mmmm, dd, yyyy")
        
        strFieldName = "F" & strSunCol + 2
        .Fields(strFieldName) = Format(DteValue + 2, "dddd, mmmm, dd, yyyy")
        
        strFieldName = "F" & strSunCol + 3
        .Fields(strFieldName) = Format(DteValue + 3, "dddd, mmmm, dd, yyyy")
        
        strFieldName = "F" & strSunCol + 4
        .Fields(strFieldName) = Format(DteValue + 4, "dddd, mmmm, dd, yyyy")
        
        strFieldName = "F" & strSunCol + 5
        .Fields(strFieldName) = Format(DteValue + 5, "dddd, mmmm, dd, yyyy")
                
        strFieldName = "F" & strSunCol + 6
        .Fields(strFieldName) = Format(DteValue + 6, "dddd, mmmm, dd, yyyy")
        .Update

The "Fn" fields are populated with successive increasing dates. The primary issue is that the starting field name isn't known until runtime.

[Gicu]

Glad to hear you got it working! But wasn't your initial unedited post about a control on a form? Why edit the initial post, makes mine irrelevant.....

[GraeagleBill]

Sorry to confuse things. Perhaps if my OP would have read: Updating record fields in a DAO Recordset: might have steered one away from thinking controls? At the time I think I thought the use of "splat" would give a hint. Anyway, again, sorry for the mislead.

[Welshgasman]

So you could just as easy used .Fields("F" & strSunCol) as per Vlads suggestion?

Plus I would have used a loop for anything like that.

[GraeagleBill]

Plus I would have used a loop for anything like that.

Yes indeed. Once I got the looping values clear I re-coded that section of the sub.
This is better and much easier to follow:

Public Sub Set7Days(DteValue As Long)
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
'  We need to update the date fields in the date columns in table tblMenuSheet
'  tblMenuSheet columns AND rows can vary as found in tblSettings plus the
'  7 day columns found in the base Excel file.
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
Dim strSQL As String
Dim strFieldName As String
Dim rs As DAO.Recordset
Dim strMenuCols As String      'We need to save what we've already found


On Error GoTo ErrHandler


strSQL = "SELECT * From [tblMenuSheet] WHERE [MenuID] = " & intDateRow     'intDateRow from Settings, global to this module
Set rs = DBEngine(0)(0).OpenRecordset(strSQL)
strMenuCols = ""
strSunCol = strSunCol - 1   'Prime strSunCol (global to this module) so we can loop


With rs
    If IsNull(!F2) Then
        MsgBox "The Fn fields are null.  Date row taken from settings is " & intDateRow
    Else
        .Edit
        For I = 1 To 7
        strFieldName = "F" & strSunCol + I             'I global to this module
        strMenuCols = strMenuCols & ";" & strFieldName
        .Fields(strFieldName) = Format(DteValue + (I - 1), "dddd, mmmm, dd, yyyy")
        Next I
        strMenuCols = Right(strMenuCols, 20)    'three per token times 7 - 1 to delete leading ";"
        !F1 = strMenuCols                       'Okay, pair the columns to menu table
        .Update
    End If
    




End With


rs.Close
Set rs = Nothing


Exit_ErrHandler:
    Exit Sub
    
ErrHandler:
    MsgBox "Error " & Err.Number & " in Set7Days Sub : " & Err.Description, vbOKOnly + vbCritical
    MsgBox strSQL       'Let me see what the beef is?
    Resume Exit_ErrHandler
End Sub