Sandra Smith
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("t blCustomer s", 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
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("t
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That works. Anything with quotes just errored out. This saves me a LOT of typing! Thank you.
![""""&strField &"""" ] = strValue