Solved

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

Posted on 2011-09-06
5
626 Views
Last Modified: 2012-05-12
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!
0
Comment
Question by:jrogersok
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 36491038
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
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 36491075
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
 
LVL 2

Author Comment

by:jrogersok
ID: 36491301
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36491412
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36904759
ok
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question