Droplist/Combo box

[susan]

i am using access 2003 - this is my first Form...

i want to have a drop list for a field in my form called "Suppliers" which
will reference to a Supplier table. there is no drop list option so i used a
combo box instead.

the purpose of the drop list is to prevent typos of supplier names. but i
also want my users to be able to enter a new supplier name if it's not
already on the list.

i get the droplist from the combo box but it doesn't allow anything that's
not on the list - how can i change that?

also how can i "reference/link" the combo box to the "supplier" field in my
form so it wil populate the correct field in my source table?

please help!
thanks,
susan

 

[Dennis]

The control source property of the combo box needs to be set to the supplier
field name in the table so that it updates when data is selected. Also the
Limit to List property needs to be set to Yes. When data that is not in the
list is entered it will trigger the On Not in List Event. Put an Event
procedure on this property and add something like the following code.
(Assuming the combo box looks up to a suppliers table)

Private Sub Supplier_NotInList(NewData As String, Response As Integer)
Dim nAnswer As Integer

nAnswer = MsgBox("Do You wish to add " & NewData & " to the Supplier
drop down list ?", vbQuestion + vbYesNo, "Add New Data")
If nAnswer = vbNo Then
Response = acDataErrContinue
Me.Supplier = Me.Supplier.OldValue
Me.Supplier.SetFocus
Else
Response = acDataErrAdded
DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO [Suppliers] ([Supplier]) VALUES ('" &
NewData & "')"
DoCmd.SetWarnings True
End If
End Sub

 

[susan]

i set the control source property to the supplier field name...but the "limit
to list" i set to No and it allow me to enter values not on list.
does the code you have below actually updates my source supplier table when
a new value is added? if so, where do i add the code?
thanks so much for your help dennis!!!!!

The control source property of the combo box needs to be set to the supplier
field name in the table so that it updates when data is selected. Also the
Limit to List property needs to be set to Yes. When data that is not in the
list is entered it will trigger the On Not in List Event. Put an Event
procedure on this property and add something like the following code.
(Assuming the combo box looks up to a suppliers table)

Private Sub Supplier_NotInList(NewData As String, Response As Integer)
Dim nAnswer As Integer

nAnswer = MsgBox("Do You wish to add " & NewData & " to the Supplier
drop down list ?", vbQuestion + vbYesNo, "Add New Data")
If nAnswer = vbNo Then
Response = acDataErrContinue
Me.Supplier = Me.Supplier.OldValue
Me.Supplier.SetFocus
Else
Response = acDataErrAdded
DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO [Suppliers] ([Supplier]) VALUES ('" &
NewData & "')"
DoCmd.SetWarnings True
End If
End Sub

i am using access 2003 - this is my first Form...

i want to have a drop list for a field in my form called "Suppliers" which
will reference to a Supplier table. there is no drop list option so i used a
combo box instead.

the purpose of the drop list is to prevent typos of supplier names. but i
also want my users to be able to enter a new supplier name if it's not
already on the list.

i get the droplist from the combo box but it doesn't allow anything that's
not on the list - how can i change that?

also how can i "reference/link" the combo box to the "supplier" field in my
form so it wil populate the correct field in my source table?

please help!
thanks,
susan

 

[Dennis]

Set to No, will not update your suppliers table. Set to Yes, will trigger the
Not in List Event. Open the properties dialog box for your combo box and
scroll to the On Not in List property. Set this to [Event Procedure] and then
click on the elipses button which will take you into the code editor. This is
where my code below goes. It is only sample code, you do not need to prompt
to add the data and the structure of your suppliers table may be different.

i set the control source property to the supplier field name...but the "limit
to list" i set to No and it allow me to enter values not on list.
does the code you have below actually updates my source supplier table when
a new value is added? if so, where do i add the code?
thanks so much for your help dennis!!!!!

The control source property of the combo box needs to be set to the supplier
field name in the table so that it updates when data is selected. Also the
Limit to List property needs to be set to Yes. When data that is not in the
list is entered it will trigger the On Not in List Event. Put an Event
procedure on this property and add something like the following code.
(Assuming the combo box looks up to a suppliers table)

Private Sub Supplier_NotInList(NewData As String, Response As Integer)
Dim nAnswer As Integer

nAnswer = MsgBox("Do You wish to add " & NewData & " to the Supplier
drop down list ?", vbQuestion + vbYesNo, "Add New Data")
If nAnswer = vbNo Then
Response = acDataErrContinue
Me.Supplier = Me.Supplier.OldValue
Me.Supplier.SetFocus
Else
Response = acDataErrAdded
DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO [Suppliers] ([Supplier]) VALUES ('" &
NewData & "')"
DoCmd.SetWarnings True
End If
End Sub

i am using access 2003 - this is my first Form...

i want to have a drop list for a field in my form called "Suppliers" which
will reference to a Supplier table. there is no drop list option so i used a
combo box instead.

the purpose of the drop list is to prevent typos of supplier names. but i
also want my users to be able to enter a new supplier name if it's not
already on the list.

i get the droplist from the combo box but it doesn't allow anything that's
not on the list - how can i change that?

also how can i "reference/link" the combo box to the "supplier" field in my
form so it wil populate the correct field in my source table?

please help!
thanks,
susan

 

[RainbowTVP]

This so ALMOST helped me! I am trying to create a combo box that will allow
the user to add items not on the list. This worked, except... it goes to a
look up table. This code enters only the description field, but is there a
way I can add a line that will also update the primary key field (which is
autonumber)?

The control source property of the combo box needs to be set to the supplier
field name in the table so that it updates when data is selected. Also the
Limit to List property needs to be set to Yes. When data that is not in the
list is entered it will trigger the On Not in List Event. Put an Event
procedure on this property and add something like the following code.
(Assuming the combo box looks up to a suppliers table)

Private Sub Supplier_NotInList(NewData As String, Response As Integer)
Dim nAnswer As Integer

nAnswer = MsgBox("Do You wish to add " & NewData & " to the Supplier
drop down list ?", vbQuestion + vbYesNo, "Add New Data")
If nAnswer = vbNo Then
Response = acDataErrContinue
Me.Supplier = Me.Supplier.OldValue
Me.Supplier.SetFocus
Else
Response = acDataErrAdded
DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO [Suppliers] ([Supplier]) VALUES ('" &
NewData & "')"
DoCmd.SetWarnings True
End If
End Sub

i am using access 2003 - this is my first Form...

i want to have a drop list for a field in my form called "Suppliers" which
will reference to a Supplier table. there is no drop list option so i used a
combo box instead.

the purpose of the drop list is to prevent typos of supplier names. but i
also want my users to be able to enter a new supplier name if it's not
already on the list.

i get the droplist from the combo box but it doesn't allow anything that's
not on the list - how can i change that?

also how can i "reference/link" the combo box to the "supplier" field in my
form so it wil populate the correct field in my source table?

please help!
thanks,
susan