Code: write values from controls to unbound form fields


If you have an unbound form, and need a quick and convenient way to write values from its controls to the appropriate fields, instead of listing every field by name, you can use this procedure, based on the assumption that each control that contains a value to be written to a field has a name that consists of the appropriate 3-letter LNC prefix plus the field name, so that (for example) txtCustomerID’s value will be written to the CustomerID field, cboOffice’s value will be written to the Office field, and so on.  Here is the procedure, which can be called from a Save Record button on the unbound form:

1
Public Sub AddRecord(frm As Access.Form)
1
 
1
On Error GoTo ErrorHandler
1
 
1
   Set rstData = CurrentDb.OpenRecordset("tblData", dbOpenDynaset)
1
 
1
   With rstData
1
      .AddNew
1
 
1
      For Each ctl In frm.Controls
1
         If ctl.ControlType = acTextBox Or ctl.ControlType = _
1
            acComboBox Or ctl.ControlType = acCheckBox Then
1
            varValue = ctl.Value
1
            strFieldName = Mid(ctl.Name, 4)
1
            'Debug.Print "Field name: " & strFieldName
1
            'Debug.Print "Field value: " & varValue
1
         End If
1
      Next ctl
1
 
1
      .Update
1
      .Close
1
   End With
1
 
1
   strTitle = "Information"
1
   strPrompt = "A new record has been added."
1
   MsgBox prompt:=strPrompt, _
1
      buttons:=vbInformation + vbOKOnly, _
1
      TITLE:=strTitle
1
 
1
ErrorHandlerExit:
1
   Set rstData = Nothing
1
   Exit Sub
1
 
1
ErrorHandler:
1
   MsgBox "Error No: " & Err.Number _
1
      & " in AddRecord procedure; " _
1
      & "Description: " & Err.Description
1
   Resume ErrorHandlerExit
1
 
1
End Sub

 

The Debug.Print statements can be used (just remove the apostrophe) for help in figuring out problems; sometimes it is necessary to exclude a specific control, such as a text box used for a label.


Want More?

Office Watch has the latest news and tips about Microsoft Office.  Delivered once a week.