General discussion


Macro to add a value to combo box table

By audit ·
I have created a form in Access that contains a combo box that is attached to a table of values. I would like users to be able to permanently add values to the table that were not in the original table. Help function says that I can create a macro that will add a new value to the combo box list if I set the OnNotInList property to the name of the macro or to [EventProcedure]. I don't know how to write a VBA macro or event procedure to do this. Can someone provide the coding for this?

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

by T "bananas" Bonanno In reply to Macro to add a value to c ...

Here is the code to accomplish what you are trying to do. You will need to create an input form for the value table. The code has two functions: it first prompts the user, so the user can check to make sure he/she is entering the correct data, and if a new entry is needed, allows the new entry. The code below goes behind the form- select [Event Procedure] for the NotInList property and paste the code for PrivateSub MyControl_NotInList (Change control names and form names to your names), then select [Event Procedure] for the double-click property and paste the PrivateSub MyControl_DblClick code in there (again change control and form names to yours).

Private Sub MyControl_NotInList(NewData As String, Response As Integer)
MsgBox "Double-click this field to add an entry to the list."
Response = acDataErrContinue
End Sub

Private Sub MyControl_DblClick(Cancel As Integer)
On Error GoTo Err_MyControl_DblClick
Dim lngMyControl As Long 'You may need to change the data type here'

If IsNull(Me![MyControl]) Then
Me![MyControl].Text = ""
lngMyControl = Me![MyControl]
Me![MyControl] = Null
End If
DoCmd.OpenForm "ValueTableForm", , , , , acDialog, "GotoNew"
If lngMyControl <> 0 Then Me![MyControl] = lngMyControl

Exit Sub

MsgBox Err.Description
Resume Exit_MyControl_DblClick
End Sub

Hope this helps!


Collapse -

by talentonloan In reply to Macro to add a value to c ...


One way to do it is first to create an Append Query that will add the value of the control on your form (Forms!Myform!mycontrol) to append that value to the appropriate field in your Lookup table. Then create a procedure in the 'On Not in List' event to call the Append Query. You might want to put a message box in front of the Append Query call "Are you sure you wish to add XXX to the control list?" This will prompt the user to check spelling or other reasons why they might not want to add that value to the table. After calling the query you can insert a Docmd.requery statement to refresh the control's contents. Also, after you have used it for a while you might want to add the Docmd.SetWarnings command to False before the query then DoCmd.SetWarnings to True after running the query to eliminate the standard Access prompt 'You are about to append x records...'

Be sure to add standard error handling - avoid using a macro as it does not allow such error handling.

Hope that helps


Collapse -

by rleavitt In reply to Macro to add a value to c ...

Create a form for the combo box field and add the
following vba code to the not on list properties.

Private Sub ename_NotInList(NewData As String, Response As Integer)
' MsgBox "Double-click Name field to add an employee."
'Response = acDataErrContinue
Dim Result
Dim Msg As String
Dim CR As String

CR = Chr$(13)

' Exit this subroutine if the combo box was cleared.
If NewData = "" Then Exit Sub

' Ask the user if he or she wishes to add the new employee.
Msg = "'" & NewData & "' is not in the list." & CR & CR
Msg = Msg & "Do you want to add it?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then
' If the user chose Yes, start the empname form in data entry
' mode as a dialog form, passing the new employee name in
' NewData to the OpenForm method's OpenArgs argument. The
' OpenArgs argument is used in empname form's Form_Load event
' procedure.
DoCmd.OpenForm "Empname", , , , acAdd, acDialog, NewData
'DoCmd.Save acDefault
' DoCmd.Close acForm, "empname"

End If
' Look for the empname the user created in the empname form.
Result = DLookup("[Empname]", "empname ", _
"[empname]='" & NewData & "'")
If IsNull(Result) Then
' If the Employee was not created, set the Response argument
' to suppress an error message and undo changes.
Response = acDataErrContinue
' Display a customized message.
MsgBox "Please try again!"
' If the Employee was created, set the Response argument to
' indicate that new data is being added.
Response = acDataErrAdded
DoCmd.Close acForm, "empname"
End If

End Sub

Collapse -

by rleavitt In reply to Macro to add a value to c ...

Sorry I forget to add the vba code for the combo box form. Add to the form open event
Private Sub Form_Load()
If Not IsNull(Me.OpenArgs) Then
' If form's OpenArgs property has a value, assign the contents
' of OpenArgs to the name field. OpenArgs will contain
' a name if this form is opened using the OpenForm
' method with an OpenArgs argument, as done in the pc inventory
' form's empname_NotInList event procedure.
Me![Empname] = Me.OpenArgs
End If
'Unload Me activeX error use DoCmd.Close to return to pc inventory with seeing the form open
DoCmd.Close acForm, "empname"
End Sub

Related Discussions

Related Forums