Adding Part Number on demand that is not in table

[patrickwelch]

I am working with a Northwind Template from Office and I am trying to add a new part number on demand. I am getting the error:


*****
"The text you entered isn't an item in the list.
Select an item from the list, or enter text that matches one of the listed items"
*****
I would like to be able to add a part number on the fly instead of having to setup each part individually, since this will be used for quoting and invoicing and would be very inconvenient and time consuming.
Please let me know if anyone has any ideas. Thanks

This file is too large but this is the link to it. Thanks !
http://www.welchlive.com/billjohnson.accdb

[SteveF]

The response you're seeing is typical for a combo or list box with the "limit to list" property turned on. You'll need to turn it off to do live updates.

Better would be to leave "limit to list" turned on, and add some code to the "on not in list" event to properly add items (with integrity checks etc.) to the table that provides data for the combo/list box.

Holler if you'd like an example.

Steve

[patrickwelch]

Yeah that would be great if you could give me an example. I would like to have the code open up a form to enter a part number with a price. Thanks

[SteveF]

OK, here’s the as-advertised example of using the NotInList event to dynamically add an item to a combo-box row source. The same principles apply to list boxes.

Two sections of relevant code: The first is the NotInList event of the combo box. It posts a replacement to the default message, and if the message response is “yes”, opens the add-item form in dialog mode, so that the NotInList code is paused while the add-item form is open. The NewData parameter is loaded by VBA with the value that was keyed in (and not found), so I save that in a Public-level variable that is read into the add-item form. The value “acDataErrAdded” designates that a new value was added to the RowSource, so the combo is automagically re-queried once the add-item form closes.

Private Sub cboConductor_NotInList(NewData As String, Response As Integer)
'enable addition of new items to conductor list
Dim intAnswer As Integer, strMessage As String

strMessage = "This item is not in the list." & vbNewLine & "Do you want to add it?"

intAnswer = MsgBox(strMessage, vbYesNo + vbQuestion, "Not Found")
If intAnswer = vbYes Then

'push the new entries into public variables
glNewItem = NewData
glCategory = 3
'open New Item form
DoCmd.OpenForm "pfrmAddNewLookupItem", , , , , acDialog
'requery the combo box
Response = acDataErrAdded

Else

'ignore the new item
Response = acDataErrContinue

End If

End Sub

Here now is the code for events in the add-item form – First, the Form_Load event pulls in the NewData information saved in the Public variable and drops it in the proper control on the form: no need to re-type what was originally input into the combo box. Then, the “Add” button on the form checks that all required input is present and posts the new item to the source table before closing the form and returning to the combo’s NotInList event code:

Private Sub cmdAdd_Click()
'add new item to lookup table
Dim myset As DAO.Recordset
Dim strSQL As String, strMessage As String

strMessage = "Please enter a new item!"

'check for user entry
If txtDescription = "" Then

MsgBox strMessage, vbOKOnly + vbExclamation, "Input Needed"
Exit Sub

End If

strMessage = "The " & Chr(34) & "new" & Chr(34) & " item already exists." & vbNewLine
strMessage = strMessage & "Please re-check your entry and try again."

'define and open recordsets
strSQL = "SELECT * FROM tblLookupItems;"
Set myset = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

'add the new item to the recordset
'check that the new entry does not already exist

With myset

.FindFirst "CategoryID = " & txtCategoryID & " and Description = " & Chr(34) & txtDescription & Chr(34)
If .NoMatch = True Then

.AddNew
!CategoryID = txtCategoryID
!Description = txtDescription
.Update

Else

MsgBox strMessage, vbOKOnly + vbCritical, "Error"
GoTo CA_cleanup

End If

End With

'close and release recordsets
CA_cleanup:
myset.Close
Set myset = Nothing

'close the form
DoCmd.Close

End Sub

Obviously, you'll want to replace the control- and table-names used here with the ones you actually use, but the concept will be the same.

Steve

[patrickwelch]

Yup, definately out of my realm of computer skills. Thanks anyways for the help!