Programming Samples

Click here to go to:



Excel VBA

Word VBA

MS Access

T-SQL

SSIS

SSRS

Power BI

Crystal Reports

SSAS

SQL Replication

C# Code

ASP .NET Code

Oracle PL/SQL

Database Diagramming


Back to Home Page


MS Access Append Data to the Last Available Field in a Table Using a Function

Using MS Access - Function to Append Data to the Last Available Field in a Table

This tutorial will show how to use a function in a module to modify data in a table. Assuming the table has fields for Name and address lines 1 through 5, we will append a line of text to the last one that is "open" or null by iterating through the rows using a loop and SQL commands.

MS Access Appending Table Data Background

Starting with a table of names and addresses, it is apparent that there is enough room to append the extra text that needs to be added. They are not uniform, so a single SQL Update cannot be applied. Rather than performing the task manually by inspecting the number of rows that have data in Address1 through Address5, a Function in a Module can be constructed to do this task.

Address Table

Adding a Yes/No (boolean) field called "Done" to the table will help determine which rows have been updated and do not require and additional review once the update has been performed.

Design View of Address Table

Create the Function in an MS Access Module

Go to the Create Tab and on the Macro Icon, select Module

Insert Module Main

Or alternately, click ALT + F11 to get to the VBA screen and Right Click Modules and select Insert, then Module.

Insert Module Backend

Write the function to append the data to the first available field that is Null. The function arguments accept the string table name, the name of the address field (should be uniform, i.e., Addr1, Addr2, etc. or Address1, Address2, etc.), and the string text to add to the empty field.

The For Loop will iterate through each address field. A SQL statement will be built using a string variable which will detect if the length of the field is 0 and that the boolean field is not yet set to 1 and update the field with the text and set the Done field to 1 when the criteria is met.

To run the SQL statement, additional lines of code to set Access warnings off and then back on have been added. There is also some error trapping code, in case there are any issues in executing the code.

Public Function appendData(sTableName As String, sAddr As String, sTextToAppend As String) As Boolean
 Dim i As Integer
 Dim sSql As String

  On Error GoTo errTrap

  For i = 1 To 5

  'iterate through the rows detecting the first available field to append the data

   sSql = "UPDATE " + sTableName + " SET Done=1," & sAddr & i & "='" & sTextToAppend & _
   "' WHERE Len(Nz(" & sAddr & i & ",'')) = 0 and Done = 0"
   DoCmd.SetWarnings False
   DoCmd.RunSQL sSql
   DoCmd.SetWarnings True

  Next i

   appendData = True

  exitFunction:
   Exit Function

  errTrap:
   appendData = False
   MsgBox "Error: " & Err.Description
   Resume exitFunction

End Function

Save the function and write a procedure to execute the code. This code will pass in the following variables:
- Table Name = tblSalesLabels
- Address field name = Address
- Text to Add = Immediate Attention Required

Public Sub test()
 Dim b As Boolean
  b = appendData("tblSalesLabels", "Address", "Immediate Attention Required")

 If b = True Then
   Debug.Print "Success"
  Else
   Debug.Print "Failed"
  End If
End Sub

Go to the Immediate Window in the VBA section of MS Access. If it is not visible, click CTL + G or go to View, Immediate Window

Immediate Window

In the Immediate window, type in the name of the Sub, "Test" and click Enter.

Immediate Window Output

Verify that the table has been correctly populated with the fields.

Final Table

Using the VBA Modules can accomplish many time consuming procedures much more quickly with code.