NotInList ComboBox Problem

[Duck]

I have a form with among others two combo boxes on of which displays a
list of Vendors and the other displays a list of Models and is
dependant upon the which vendor is chosen in the first combobox. Both
are bound to the Vendor and Model fields in the table tblLinked. The
'Limit to list' property is set to 'yes' for both.

I want the user to be able to add new Vendors, and Models as
necessary, but I want them to be prompted first (when a new item is
typed in the box) My problems is that eventhough I am using basically
the same code in the 'NotInList' event of both boxes, the Model
combobox keeps giving me the error "The text you entered isn't an Item
in the list". The error never occurs with the Vendor box but always
orrcurs is the Model box.....Please Help!!

Here is the code for both boxes (Vendor First)

*******************************************************
Private Sub Vendor_NotInList(NewData As String, Response As Integer)

Dim cnn As New ADODB.Connection
Dim strSQL As String
Dim bytResponse As Byte
Set cnn = CurrentProject.Connection
bytResponse = MsgBox(NewData & " Is a new vendor, Do you want to add
it " _
& "to the list?", vbYesNo + vbQuestion, "New Vendor Detected")
If bytResponse = vbYes Then
strSQL = "INSERT INTO tblLinked(Vendor) VALUES('" _
& NewData & "')"
'Debug.Print strSQL
cnn.Execute strSQL
Response = acDataErrAdded
ElseIf bytResponse = vbNo Then
Response = acDataErrContinue
Me!Vendor.Undo
End If

End Sub
************************************************************


*******************************************************
Private Sub Model_NotInList(NewData As String, Response As Integer)

Dim cnn As New ADODB.Connection
Dim strSQL As String
Dim bytResponse As Byte
Set cnn = CurrentProject.Connection
bytResponse = MsgBox(NewData & " Is a new model..Do you want to add
it " _
& "to the list?", vbYesNo + vbQuestion, "New Item Detected")
If bytResponse = vbYes Then
strSQL = "INSERT INTO tblLinked(Model) VALUES('" _
& NewData & "')"
Debug.Print strSQL
cnn.Execute strSQL
Response = acDataErrAdded
ElseIf bytResponse = vbNo Then
Response = acDataErrContinue
Me!Model.Undo
End If
End Sub
******************************************************

 

[kc-mass]

A guess on the problem:

I assume your model combo is dependent on your vendor combo and therefore
your model combo actually has both the vendor and model in its datasource,
so I don't think you can just plug in the model and expect it to work.

Regards

Kevin

 

[Duck]

A guess on the problem:

I assume your model combo is dependent on your vendor combo and therefore
your model combo actually has both the vendor and model in its datasource,
so I don't think you can just plug in the model and expect it to work.

Regards

Kevin













- Show quoted text -

Thanks for responding, but actually my [Model] record source is a SQL
statement:

SELECT DISTINCT [Model] FROM qryVendorModelListCost WHERE
qryVendorModelListCost.vendor=Forms!MyForm![Vendor]

it only has one column and works fine as long as it's not a new
entry. I'm still stumped!!

Charles

 

[BruceM]

Here is a somewhat different approach using a recordset rather than INSERT
SQL. I don't know if anything here will help, but it may be worth looking
from another angle:
http://www.mvps.org/access/forms/frm0015.htm

A guess on the problem:

I assume your model combo is dependent on your vendor combo and therefore
your model combo actually has both the vendor and model in its datasource,
so I don't think you can just plug in the model and expect it to work.

Regards

Kevin













- Show quoted text -

Thanks for responding, but actually my [Model] record source is a SQL
statement:

SELECT DISTINCT [Model] FROM qryVendorModelListCost WHERE
qryVendorModelListCost.vendor=Forms!MyForm![Vendor]

it only has one column and works fine as long as it's not a new
entry. I'm still stumped!!

Charles

 

[kc-mass]

Hi Charles,

You want to examine your "qryVendorModelListCost". It is definitely
contains the vendor.
Otherwise how could you evaluate
"qryVendorModelListCost.vendor=Forms!MyForm![Vendor]". So you have the
linkage between Vendor and model. That means you need the model and vendor
related. To do that you likely need a table with the model name, model ID
and a foreign key of vendor name or vendor ID.

Regards

Kevin

A guess on the problem:

I assume your model combo is dependent on your vendor combo and therefore
your model combo actually has both the vendor and model in its datasource,
so I don't think you can just plug in the model and expect it to work.

Regards

Kevin













- Show quoted text -

Thanks for responding, but actually my [Model] record source is a SQL
statement:

SELECT DISTINCT [Model] FROM qryVendorModelListCost WHERE
qryVendorModelListCost.vendor=Forms!MyForm![Vendor]

it only has one column and works fine as long as it's not a new
entry. I'm still stumped!!

Charles

 

[Dale Fye]

The problem with your Vendor_NotInList event is that you will insert a
vendor, without any associated models. I don't know what tblLinked is, or
what it's structure looks like, but I would probably have a Vendors table,
and would use that in the Vendors combo box. That way, when I insert a new
Vendor, I don't have to worry about having a matching model. I would
probably also popup the Vendors form so the user could enter more information
about the vendor than just the name.

The problem with your Model_NotInList code is, like kc-mass indicated, that
you are trying to insert the new model without the associated Vendor. Try:

strSQL = "INSERT INTO tblLinked(Vendor, Model) " _
& "VALUES('" & me.Vendor & "', '" & NewData & "')"

----
HTH
Dale

A guess on the problem:

I assume your model combo is dependent on your vendor combo and therefore
your model combo actually has both the vendor and model in its datasource,
so I don't think you can just plug in the model and expect it to work.

Regards

Kevin













- Show quoted text -

Thanks for responding, but actually my [Model] record source is a SQL
statement:

SELECT DISTINCT [Model] FROM qryVendorModelListCost WHERE
qryVendorModelListCost.vendor=Forms!MyForm![Vendor]

it only has one column and works fine as long as it's not a new
entry. I'm still stumped!!

Charles