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:

Public Sub AddRecord(frm As Access.Form)

On Error GoTo ErrorHandler

   Set rstData = CurrentDb.OpenRecordset("tblData", dbOpenDynaset)

   With rstData

      .AddNew

      For Each ctl In frm.Controls

         If ctl.ControlType = acTextBox Or ctl.ControlType = _

            acComboBox Or ctl.ControlType = acCheckBox Then

            varValue = ctl.Value

            strFieldName = Mid(ctl.Name, 4)

            'Debug.Print "Field name: " & strFieldName

            'Debug.Print "Field value: " & varValue

         End If

      Next ctl

      .Update

      .Close

   End With

Windows 10 Creators from people 'in the know'

A detailed and independent look at Windows 10, especially written for the many people who use Microsoft Office.

Fully up-to-date with coverage of the Creators edition 2016 major update of Windows 10.

This 720 page book shows you important features and details for all serious Windows 10 users.

   strTitle = "Information"

   strPrompt = "A new record has been added."

   MsgBox prompt:=strPrompt, _

      buttons:=vbInformation + vbOKOnly, _

      TITLE:=strTitle

ErrorHandlerExit:

   Set rstData = Nothing

   Exit Sub

ErrorHandler:

   MsgBox "Error No: " & Err.Number _

      & " in AddRecord procedure; " _

      & "Description: " & Err.Description

   Resume ErrorHandlerExit

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.