NotInList for Cascading combos

[SaviorSix]

Hi, I've searched quite a bit on these forums already to no avail, so I decided to post the question:

I have two cascading combo boxes. They work fine. I added a NotInList function for the second combo box.

Private Sub cboMethodName_NotInList(NewData As String, Response As Integer)
Dim strSql As String
If MsgBox(NewData & " is not currently in the list for [" & Me.cboMethodType.Value & "] recruitment." & vbCrLf & "Would you like to add this now?", vbYesNo + vbQuestion) = vbYes Then
strSql = "INSERT INTO lkptblMethod([MethodName], [B][MethodType][/B]) " & "VALUES ('" & NewData & "', "[B] & Me.cboMethodType &[/B] ");"
Me.cboMethodType.Requery
DoCmd.RunSQL strSql
MsgBox "The new information has been added"
 
Response = acDataErrAdded
 
Else
MsgBox "Please enter a valid item from the drop down box."
        Response = acDataErrContinue
       Me.cboMethodName = ""
End If
Exit Sub
End Sub

Ive bolded where I'm having a problem. I need the NotInList to do both:
insert the entered value in the second combo box into the appropriate table column (which works fine)
AND
insert the value from the first combo box into the appropriate table column

When I run the code above, it asks me to manually enter a Parameter for the value of the first combobox, ive tried me.cboMethodType.Value and me.cboMethodType.Column(0) also.

How do I get it to automatically insert the value selected from the first combobox into the appropriate column?>

 

[boblarson]

If you are doing this then I fear that you don't have your combo boxes set up properly. Each of them would normally be based on different information, not the same information. The BINDING of the data can be to the same table, but usually the values for the lookups will be in separate tables.

For example -

If I have a combo box that selects a DEPARTMENT and the second combo box that selects an Employee from that department, I might have both combo boxes bound to respective fields within a table like tblEmployeeLeave but the ROWSOURCE of the combo that selects Department will be
"SELECT DepartmentID, Department From tblDepartments"

and the ROWSOURCE of the combo based on the Employee table will be
"SELECT EmployeeID, EmployeeLastName & ", " & EmployeeFirstName As EmployeeName From tblEmployee"

So, you see I have lookups coming from two tables. I would not be updating the combo one value when doing a not in list for an employee. I would just be adding an employee to the employee table.

 

[SaviorSix]

If you are doing this then I fear that you don't have your combo boxes set up properly. Each of them would normally be based on different information, not the same information. The BINDING of the data can be to the same table, but usually the values for the lookups will be in separate tables.

For example -

If I have a combo box that selects a DEPARTMENT and the second combo box that selects an Employee from that department, I might have both combo boxes bound to respective fields within a table like tblEmployeeLeave but the ROWSOURCE of the combo that selects Department will be
"SELECT DepartmentID, Department From tblDepartments"

and the ROWSOURCE of the combo based on the Employee table will be
"SELECT EmployeeID, EmployeeLastName & ", " & EmployeeFirstName As EmployeeName From tblEmployee"

So, you see I have lookups coming from two tables. I would not be updating the combo one value when doing a not in list for an employee. I would just be adding an employee to the employee table.

Alright thanks Bob this must be it, I was trying to cut down on the amount of tables, because I only have 3 fields: the key (autonum), MethodType (rowsource for 1st combo), and MethodName (rowsource for 2nd combo),
but since splitting them up into separate tables is going to make it work, id rather do that

(ps I still need to spread some reputation around )