List Items Edit Form in Access 2007 not displaying Value entered into combo box on original form

I am trying to use the Allow Value List Edits option and have it display a form to enter new Part Numbers.  I typically don't allow this in databases, but the client is adding new part numbers all the time as adding new inventory items.

The issue is that the Value List Edit Form does open up properly when the user types in a new part number, however, the newly-entered part number does not automatically display on the value List Edit Form.  I've tried in several ways using VBA but can't seem to get it to work.  Any quick way to accomplis this without writing the whole routine using the Not On List property?

Thanks!
LVL 2
jrogersokAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
Can you post what you were using?

Typically this would involve a "requery" of the control (presuming the value has been inserted into the underlying table):

Me.YourControl.Requery
0
 
Helen FeddemaCommented:
One other possibility is to use a form for editing lookup tables.  This lets users easily enter new values for all the lookup tables used in a database.  See my Access Archon #111:
http://www.helenfeddema.com/Files/accarch198.zip
Here is the form from the sample database:
Lookup-Table-Edit-Form.jpg
0
 
jrogersokAuthor Commented:
Thanks - got it working another way (all through VBA as I'm in a time crunch).

Another question, though, after the form opens up to Add the New PN, Serial #, Vendor, etc, I am still receiving the Access message box  "The Text You Entered isn't an item in the list".

Is there any way to turn that off?

I've posted my code

Private Sub PartNumber_NotInList(NewData As String, Response As Integer)
   ' Add a new Part Number

    Dim intNewPN As Integer, intTruncateName As Integer, strTitle As String, intMsgDialog As Integer

    ' Display message box asking if user wants to add a new PN.
    strTitle = "Part Number Not In List"
    intMsgDialog = vbYesNo + vbQuestion + vbDefaultButton1
    intNewPN = MsgBox("Do you want to add " & NewData & " as a new Part Number?", intMsgDialog, strTitle)
    Response = acDataErrAdded

    If intNewPN = vbYes Then
        ' Remove new name from combo box so
        ' control can be requeried when user returns to form.
        DoCmd.RunCommand acCmdUndo
        'Add new Part Number data
        DoCmd.OpenForm "frmPartsAdd", acNormal, , , acAdd, , NewData
        Forms!frmPartsAdd![PartNumber] = NewData
        
        ' Continue without displaying default error message.
    End If
  '      Forms!frmAddNewInventory!PartNumber.Requery
        
        Response = acDataErrAdded

End Sub

Open in new window

0
 
Jeffrey CoachmanMIS LiasonCommented:
You only posted the code that triggers in the value is not in the list.

What wee need is the code that sets (or does not set) the value in the PartNumber control when the form is opened in this mode?
0
 
Jeffrey CoachmanMIS LiasonCommented:
ok
0
All Courses

From novice to tech pro — start learning today.