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.
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.
Create the Function in an MS Access Module
Go to the Create Tab and on the Macro Icon, select Module
Or alternately, click ALT + F11 to get to the VBA screen and Right Click Modules and select Insert, then Module.
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
In the Immediate window, type in the name of the Sub, "Test" and click Enter.
Verify that the table has been correctly populated with the fields.
Using the VBA Modules can accomplish many time consuming procedures much more quickly with code.