Solved

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

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

710 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