Solved

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

Posted on 2011-09-06
5
573 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
  • 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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access query expression 6 19
VBA name newly created sheet 4 20
Microsoft Access - Stopping Control - (minus) 4 27
Access check if a table is open 4 38
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

816 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now