add to a dynamic list

[L Scholes]

I have a user form that I have adapted to add information to a
data base. One of the fields (dealer) uses a combobox to select a
dealer name from a dynamic list. Right now, if the user tries to enter
a dealer name that is not in the dynamic list "DealerList" I get an
error message.
I want a message box that asks if the user wants to add the new
name to "DealerList," and if the user selects "Yes," the name is added
to "DealerList" (so the new name is available next time) and the form
continues adding the information to the database; if the user selects
"No," the name is not added to "DealerList" but the information is
still added to the database.
The question is: What code do I need to add and where. Let me know
if I need to paste my code here for you to evaluate.

Thanks in advance

 

[Ardus Petus]

Please post your existing code. That will help and save time.

TIA

 

[L Scholes]

Private Sub cmdAdd_Click()
Dim lRow As Long
Dim lDealer As Long
Dim ws As Worksheet
Set ws = Worksheets("DealerData")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

lDealer = Me.cboDealer.ListIndex

'check for a Dealer name
If Trim(Me.cboDealer.Value) = "" Then
Me.cboDealer.SetFocus
MsgBox "Please enter a Dealer Name."
Exit Sub
End If
If Trim(Me.txtPO.Value) = "" Then
Me.txtPO.SetFocus
MsgBox "Please enter a Purchase Order Number."
Exit Sub
End If

'copy the data to the database
'ws.Cells(iRow, 1).Value = Me.txtbadge.Value
ws.Cells(iRow, 1).Value = Me.cboDealer.Value
ws.Cells(iRow, 2).Value = Me.cboDealer.List(lDealer, 1)
ws.Cells(iRow, 2).Value = Me.cboSignature.Value
ws.Cells(iRow, 3).Value = Me.txtPO.Value
ws.Cells(iRow, 4).Value = Me.txtInvoice.Value
ws.Cells(iRow, 5).Value = Me.txtDate.Value
ws.Cells(iRow, 6).Value = Me.txtDue.Value
'End With

'clear the data
Me.cboDealer.Value = ""
Me.cboSignature.Value = ""
Me.txtDate.Value = "" 'Format(Date, "Medium Date")
Me.txtPO.Value = ""
Me.txtDue.Value = ""
Me.txtInvoice.Value = ""
Me.cboDealer.SetFocus

End Sub
'cboPart = cboDealer A1
'cboLocation = cboSignature B2
'add txtPO & txtInvoice C3 & D4
'txtDate = txtDate E5
'txtQty = txtDue F6
Private Sub cmdClose_Click()
Unload Me
End Sub



Private Sub UserForm_Initialize()
Dim cDealer As Range
Dim cSig As Range
Dim ws As Worksheet
Set ws = Worksheets("LookupLists")

For Each cDealer In ws.Range("DealerList")
With Me.cboDealer
.AddItem cDealer.Value
.List(.ListCount - 1, 1) = cDealer.Offset(0, 1).Value
End With
Next cDealer

For Each cSig In ws.Range("Signature")
With Me.cboSignature
.AddItem cSig.Value
.List(.ListCount - 1, 1) = cSig.Offset(0, 1).Value
End With
Next cSig

Me.txtDate.Value = ""
Me.txtDue.Value = ""
Me.txtPO.Value = ""
Me.txtInvoice = ""
Me.cboDealer.SetFocus

End Sub

 

[AnExpertNovice]

I have yet to use this but if I remember correctly the error is normal. I
think there is an "On Not in List" event handler that can be triggered. In
the event handling the record is added and the combobox is either reloaded
or the entry inserted into the combo box list.

In other words, the entry is not automatic.

Now, listen to others first!

 

[L Scholes]

I want a "new name" entry to ask if I want to:
#1 enter the name into the database BUT NOT to the source dynamic
list (DealerList);
#2 enter the name into the database AND into the source list;
#3 cancel and do nothing.
I'm not sure how to use an "On Not in List" event handler, I've never
heard of it???
Thanks

 

[AnExpertNovice]

Look at the properties of the Combo Box. Select the events tab. Look in
the list you will find it.

What you want probably can't be done without writing VBA code for the event
handler. Check out the Northwinds database and see if it has a combo box
that allows entering data and use their code if it does.

Another option would be to search for the event name and look for sample
code. As I stated before, I have yet to need that feature.

 

[Tom Ogilvy]

Because there is no such event.

 

[L Scholes]

Tom, does that mean there is no solution?
TIA

 

[AnExpertNovice]

Oops. Did I confuse an Access Combo box with one in Excel.

<exits thread with very red face!>

Tom, thanks.

L Scholes, I do apologized for such a stupid error.

 

[L Scholes]

I finally solved this one!!!!
TFAYH