Not in List for Combo Box

[cg084]

Hello,

I have got a form with 2 Combo boxes. ComboA gives a company name. ComboB gives the account number. Each company can have more than 1 different account number. Upon selecting a company the list of account numbers associated with that company is displayed. The Company and Account No are stored in a ddlCompany table.

If i enter a new company in the [Company] (ComboA) field i have some vb set up to add that to the database and add its accounts number. You are then able to use the form as normal.

It is possible that a new account number for an existing company is set up. I need to simply enter this in the [AccountNo] (ComboB) and have it added to the ddlCompany table in the background. Nothing has changed other than the field names from the Not in list segments of code. For a reason i am not at present understanding receiving an error message saying the item is not in the list and to select one that is. The only thing i can think of is the source of the [AccountNo] field. I have included the source of the [AccountNo] field and the Notinlist code.

[AccountNo] Source----

SELECT ddlCompany.CompanyName, ddlCompany.VFCorporateId FROM ddlCompany WHERE (((ddlCompany.CompanyName)=[forms]![tblIssues]![company]));


NOTInList Code ----

Private Sub VF_Corp_ID_NotInList(NewData As String, Response As Integer)

If MsgBox("Do you really want to add this item?", vbQuestion + vbYesNo) = vbYes Then
Dim DB As Database
Dim sSQL As String

sSQL = "INSERT INTO ddlCompany ( CompanyName, VFCorporateId ) SELECT tblIssues2.Company, tblIssues2.VF_Corp_ID FROM tblIssues2 WHERE (((tblIssues2.Issue_No)=123));"

Set DB = CurrentDb
DB.Execute sSQL

DB.Close
Set DB = Nothing
' Continue without displaying default error message.
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

On Error GoTo Err_VF_Corp_ID_NotInList

Exit_VF_Corp_ID_NotInList:
Exit Sub

Err_VF_Corp_ID_NotInList:
MsgBox Err.Description
Resume Exit_VF_Corp_ID_NotInList
End Sub

I hope someone has some ideas.

Cheers for any help.
Craig.

 

[MinusM]

VF_Corp_ID will not have the data entered since the list property is set to LimitToList only.

it is for this purpose the NewData parameter in given in NotinList Event

So use this Insert statemnt
= "INSERT INTO ddlCompany ( CompanyName, VFCorporateId ) values(" & value1 & "," & Newdata & &quot"

Best of luck

 

[cg084]

MinusM,

Cheers for the reply. I amended the insert into line of the code. It did not really appear to work any better. Here is my code as it stands now.

Private Sub VF_Corp_ID_NotInList(NewData As String, Response As Integer) -----(1)

If MsgBox("Do you really want to add this item?", vbQuestion + vbYesNo) = vbYes Then
Dim DB As Database
Dim sSQL As String

sSQL = "INSERT INTO [ddlCompany] ([CompanyName],[VFCorporateId]) Values(" & [Company].Value & "," & ([VF_Corp_ID].Text) & &quot"

Set DB = CurrentDb
DB.Execute sSQL

DB.Close
Set DB = Nothing
' Continue without displaying default error message.
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

On Error GoTo Err_VF_Corp_ID_NotInList

Exit_VF_Corp_ID_NotInList:
Exit Sub

Err_VF_Corp_ID_NotInList:
MsgBox Err.Description
Resume Exit_VF_Corp_ID_NotInList
End Sub



I get the error Message ______

Runtime Error 3061
Too few Parameters Expected 1.

I can only assume that it is something that is missing in the line declaring the sub i have marked this (1).

 

[MinusM]

Am I too late in replying. Was out of station for a week

When you are inserting text fields, the value should be enclosed in quotes
So the SQL should be
INSERT INTO [ddlCompany] ([CompanyName],[VFCorporateId]) Values('" & [Company].Value & "'," & ([VF_Corp_ID].Text) & &quot"
Assuming ID is a number

best of luck

 

[adamroof]

Heres a sample of what i do, maybe see if you can use some of this.
******************
Private Sub PurchaseFrom_NotInList(NewData As String, Response As Integer)
On Error GoTo err_notlisted
Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("tblSites&quot
Dim strMsg As String

strMsg = "That site is not listed in the database yet." & vbCrLf & _
"Would you like to add to the list?"
If MsgBox(strMsg, vbInformation + vbYesNo, "Database&quot = vbNo Then
Me.PurchaseFrom.Value = Null
Exit Sub
Else
rst.AddNew
rst!site = NewData
rst.Update
Me.PurchaseFrom = NewData
Me.SaleID.SetFocus
End If

Response = acDataErrContinue
rst.Close
Set rst = Nothing
db.Close

err_notlisted:
Exit Sub
End Sub

 

[MinusM]

I am not sure what error you are getting now

Try this out

Private Sub PurchaseFrom_NotInList(NewData As String, Response As Integer)
On Error GoTo err_notlisted
Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("tblSites&quot
Dim strMsg As String

strMsg = "That site is not listed in the database yet." & vbCrLf & _
"Would you like to add to the list?"
If MsgBox(strMsg, vbInformation + vbYesNo, "Database&quot = vbNo Then
Me.PurchaseFrom.Value = Null

Response = acDataErrContinue

Else
rst.AddNew
rst!site = NewData
rst.Update
Me.PurchaseFrom = NewData
Response = acDataErrAdded

Me.SaleID.SetFocus
End If

'Remember to resume after an error so that Err is cleared
Exit_notlisted:
rst.Close
Set rst = Nothing
db.Close
Exit Sub
err_notlisted:
Resume Exit_notlisted
End Sub


Best of luck