Add to drop down menu.

[Guest]

Hi,

I have drop down menu in a form. I programmed the high level drop down menu
(part number) to auto-populate other text fields in the form when the user
select a part number. How do I program the part number drop down menu to
recognize a new part number when the user type in the part number text field.
Basically, when the user type a part number and the system does not
recognized it's in the drop down menu, a pop up screen will say, "this part
number is not in the drop down menu, would you like to add the new part
number?" If yes, the new part number will automatically be added into the
drop down menu. thanks.

 

[Guest]

Are your Part Numbers in a table and the combo based on a query of that table?

If so, open a form on the PartNumbers table and add the record. When you
close the form you will return to the combo and the new item should appear in
your list.

Private Sub myCombo_NotInList(NewData As String, Response As Integer)
If MsgBox("This part number is not in the drop down menu, would you like to
add the new part number?",vbQuestion,"Add a new part number?") = vbYes Then
DoCmd.OpenForm "myForm", , , ,acFormAdd ,acDialog
End If
Me.myCombo.Requery
End Sub

If your table only has one column (doubtful for part numbers) you could just
use an INSERT query to insert the value entered, from NewData.

Private Sub myCombo_NotInList(NewData As String, Response As Integer)
Dim strSQL As String

If MsgBox("This part number is not in the drop down menu, would you like to
add the new part number?",vbQuestion,"Add a new part number?") = vbYes Then
strSQL = "INSERT INTO tblPartNumbers VALUES (NewData);"
CurrentDb.Execute strSQL, dbFailOnError
End If
Me.myCombo.Requery
End Sub

The second example could/would mean that new part numbers are added to a
table without any description or other information that I would have thought
any part number would need.

Anyway, hope that helps!

Steve

 

[Guest]

Hey Steve,

Wow...that's pretty awesome of you to give me two ideas and the codes. What
I have now is when user select a part number from the drop down menu it will
auto-populate the description text field. So if user want to add new part
number assoicated to the description the user will need to double click on
the part number text field and a form will open to allow user to add new part
# and description. I don't have a query. On the "Row Source" I used a
separate table with only part # and description. Where do I put the code
"What event" onclick, on update, etc.

 

[Guest]

It depends what you want to do...

If you want a user to type a value in the combo and present you with the
form if it is not currently in the list - use the NotInList event. I gave you
examples of the code for that.

If you want to double-click on a text field, put it in the control's
DoubleClick event.

Steve