MS Access

[lprice1024]

Here's my code:

Function Append1Table(cbo As ComboBox, NewData As Variant) As Integer
On Error GoTo Err_Append1Table
' Purpose: Append NotInList value to combo's recordset.
' Return: AcDataErrAdded if added, else acDataErrContinue
' Usage: Add this line to the combo's NotInList event procedure:
' Response = Append1Table(Me.Combo32, NewData)
Dim rst As Recordset
Dim sMsg As String
Dim vField As Variant ' Name of the field to append to.

Append1Table = acDataErrContinue
vField = "Apt"
If Not (IsNull(vField) Or IsNull(NewData)) Then
sMsg = "Do you wish to add the entry " & NewData & " for " & cbo.Name & "?"
If MsgBox(sMsg, vbOKCancel + vbQuestion, "Add new value?") = vbOK Then
Set rst = CurrentDb.OpenRecordset(cbo.RowSource)
rst.AddNew
rst(vField) = NewData
rst.Update
rst.Close
Append1Table = acDataErrAdded
End If
End If

Exit_Append1TAble:
Set rst = Nothing
Exit Function

Err_Append1Table:
MsgBox "Error " & Err.Number & ": " & Err.Description, vbInformation, "Append1Table()"
Resume Exit_Append1TAble
End Function

Question: Is it possible to alter this code so i can use it for various combo boxes within the same database....without having to save it as differently named modules over and over again.

[Cakkie]

You don't even have to change the code (maybe just add the public keyword), just place it in a module, and you can callit from everywhere. the first parameter is the combobox, the second is the value to be added. Simple as that.

Tom Cannaerts
[email protected]

Programming today is a race between software engineers striving to build bigger and better idot-proof programs, and the universe trying to produce bigger and better idiots. So far, the universe is winning -- Rich Cook