[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2011-09-06
5
Medium Priority
?
656 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 2000 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

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

Quickbooks hosting can do wonders to your enterprise but considering the points elaborated in the article which will help you to better analyze the outcomes. So scan your business, its needs and then move to the new world of limitless benefits.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

830 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