Link to home
Start Free TrialLog in
Avatar of Sandra Smith
Sandra SmithFlag for United States of America

asked on

Use a variable for a field name in ACCESS 2010 vba procedure

This is more of can it be done than anything else.  I have a form and all the text fields are the underlying name of the column in thetable with the txt prefix.   So, I want to loop through all the text fields, get rid of the txt and use the result as the name of the field,  It pulls the correct information, but when it gets to the actual field name, no matter what I do, it will not recogznie the strField.  I know it is creating the correct name of the field (debug prints!), but the value is not being used as the field name in the loop.  I have tried quotes, but that did not work either.  Or can this even be done?

Private Sub SaveData()
On Error GoTo ErrorHandler
Dim rst As DAO.Recordset
Dim ctl As Control
Dim strField As String
Dim strValue As String
Set rst = CurrentDb.OpenRecordset("tblCustomers", dbOpenTable)
With rst
    .AddNew

For Each ctl In Me.Controls
    If Left(ctl.Name, 3) = "txt" Then
        Debug.Print "Control Name: " & ctl.Name   -THIS WORKS
        strField = Mid(ctl.Name, 4, 25)
        Debug.Print "FieldName: " & strField  -THIS WORKS
        strValue = ctl
        Debug.Print "Value: " & strValue   -THIS WORKS
        ![& strField & ] = strValue  DOES NOT RECOGNIZE FIELD NAME
    End If
 Next ctl
   
    .Update
End With
rst.Close
Set rst = Nothing

Exit_ErrorHandler:
    Exit Sub
ErrorHandler:
    MsgBox "Error Number: " & Err.Number & " Description: " & Err.Description
    Resume Exit_ErrorHandler
End Sub
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

try

![""""&strField &"""" ] = strValue
ASKER CERTIFIED SOLUTION
Avatar of Rgonzo1971
Rgonzo1971

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Sandra Smith

ASKER

That works.  Anything with quotes just errored out.  This saves me a LOT of typing!  Thank you.