INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Forms

Combobox - Allow a user to add values to the underlying table by elizabeth
Posted: 8 Jun 00

This procedure allows a user to add new items to a lookup table underlying a combo box. When a user keys in a value that is not in the lookup table, a message will pop up asking if they'd like to edit the underlying table. If they answer Yes, a form will pop up to allow them to enter the new data.

1. Create a form for the underlying table. Let's call it Form1.
2. Open the combo's Properties dialog box.
3. Set the LimitToList (Data Tab) property to Yes.
4. Right click in the On Not in List (Event Tab) property and select Build.
    Two lines of code will appear, with your control's name substituted for the X.

Private Sub X_NotInList(NewData As String, Response As Integer)
End Sub


5. Paste the code below between the two lines of code that appear.
6. Substitute your control's name for  the X in the code in all 4 places.
7. Substitute the name of the form you created in step 1 for Form1.
8. Test! Test! Test!


On Error GoTo Err_X_NotInList

Dim intAnswer As Integer

intAnswer = MsgBox("Value not in lookup table. Edit table?", vbYesNo, vbQuestion)
    If intAnswer = vbYes Then
        DoCmd.RunCommand acCmdUndo
        DoCmd.OpenForm "Form1", acNormal, , , acFormEdit, acDialog
        Response = acDataErrAdded
    Else
        Response = acDataErrContinue
    End If

Exit_X_NotInList:
    Exit Sub

Err_X_NotInList:
    MsgBox Err.Description
    Resume Exit_X_NotInList

Back to Microsoft: Access Other topics FAQ Index
Back to Microsoft: Access Other topics Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close