Cascading Combo & NotInList

[damonc]

I have 2 Cascading Combo boxes setup and working correctly.

cbo1 lists all agents and cbo2 lists the branches that relate to each agent.

tbl_agents and tbl_branches are setup with a 1 to many relationship.

I have used the code (explained here) that allows the user to add a new agent if they are not in the list. This works correctly. Now I want to let the user add a new branch for the agent they have just added. The code appears to work correctly from the front end, but when you check tbl_branch, there is a new entry but it is missing the appropriate agentID foreign key (ie, it's not related to the new agent).

Is there something that I'm missing? I assume this is possible, but I must be doing something wrong.

Maybe I need to create a little pop-up form for data entry?

any help appreciated.

 

[RuralGuy]

Martin Green's example does not cover every possible situation, nor could it. In your case you will need to insert the ForeignKey yourself. You may wish to double check with the user to make sure they have left the Agent ComboBox on the correct agent for the Branch they are about to add. The AgentID is probably available as Me.cbo1.Column(0). What SQL do you use to add a Branch?

 

[damonc]

RG,
Thanks for your response.

the code to add a branch is:

Private Sub cbobranchID_NotInList(NewData As String, Response As Integer)
intAnswer = MsgBox("The Branch " & Chr(34) & NewData & _
    Chr(34) & " is not currently listed." & vbCrLf & _
    "Would you like to add it to the list now?" _
    , vbQuestion + vbYesNo, "Branch List")
    
    If intAnswer = vbYes Then
    strSQL = "INSERT INTO tbl_branch([branch]) " & _
             "VALUES ('" & NewData & "');"
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True
    MsgBox "The new branch has been added to the list." _
        , vbInformation, "Country List"
    Response = acDataErrAdded
    
    Else
    MsgBox "Please choose a branch from the list." _
        , vbInformation, "Branch List"
    Response = acDataErrContinue
End If
End Sub

The Agent combo box is on the correct agent, I think you are right in saying:

AgentID is probably available as Me.cbo1.Column(0)

but I don't know how I should do this.

 

[RuralGuy]

Here's what I would probably do. I've put names I guessed at in BOLD. You will need to adjust them to what they should be. [agentID] is the field with the AgentID in it. I guessed that Column(1) had the Agent's name in it.

Private Sub cbobranchID_NotInList(NewData As String, Response As Integer)
   On Error GoTo Err_cbobranchID_NotInList
   Dim strSQL As String
   If MsgBox("The " & Chr(34) & NewData & _
      Chr(34) & " Branch is not currently listed for the agent [" & _
      [b]Me.cbo1.Column(1)[/b] & "]." & vbCrLf & _
      "Would you like to add it to the list for [" & [b]Me.cbo1.Column(1)[/b] & "] now?" _
      , vbQuestion + vbYesNo, "Branch List") = vbYes Then
    
         strSQL = "INSERT INTO tbl_branch([branch], [b][agentID][/b]) " & _
                  "VALUES ('" & NewData & "', " & [b]Me.cbo1.Column(0)[/b] & ");"
         CurrentDb.Execute strSQL, dbFailOnError
         MsgBox "The new branch has been added to the list." _
             , vbInformation, "Country List"
         Response = acDataErrAdded
   Else
      MsgBox "Please choose a branch from the list." _
          , vbInformation, "Branch List"
      Response = acDataErrContinue
   End If
   
Exit_cbobranchID_NotInList:
   Exit Sub
   
Err_cbobranchID_NotInList:
   MsgBox "Error No:    " & Err.Number & vbCr & _
   "Description: " & Err.Description
   Resume Exit_cbobranchID_NotInList

End Sub

If you use CurrentDB.Execute then it is not necessary to turn off the Warnings which can hide other problems. I also put in error handling in case there is a problem.

 

[damonc]

RG,
I have inserted your code and changed the names you guessed:

Private Sub cbobranchID_NotInList(NewData As String, Response As Integer)
   On Error GoTo Err_cbobranchID_NotInList
   Dim strSQL As String
   If MsgBox("The " & Chr(34) & NewData & _
      Chr(34) & " Branch is not currently listed for the agent [" & _
      [B]Me.cboagentID.Column(1)[/B] & "]." & vbCrLf & _
      "Would you like to add it to the list for [" & [B]Me.cboagentID.Column(1)[/B] & "] now?" _
      , vbQuestion + vbYesNo, "Branch List") = vbYes Then
    
         strSQL = "INSERT INTO tbl_branch([branch], [agentID]) " & _
                  "VALUES ('" & NewData & "', [B]Me.cboagentID.Column(0)[/B]);"
         CurrentDb.Execute strSQL, dbFailOnError
         MsgBox "The new branch has been added to the list." _
             , vbInformation, "Branch List"
         Response = acDataErrAdded
   Else
      MsgBox "Please choose a branch from the list." _
          , vbInformation, "Branch List"
      Response = acDataErrContinue
   End If
   
Exit_cbobranchID_NotInList:
   Exit Sub
   
Err_cbobranchID_NotInList:
   MsgBox "Error No:    " & Err.Number & vbCr & _
   "Description: " & Err.description
   Resume Exit_cbobranchID_NotInList

End Sub

it has returned the following error:

Error No: 3085
Description: Undefined function 'Me.cboagentID.Column' in expression.

Why would this be happening?

This is the Row Source of the Agent Combo (cboagentID), if it helps.

SELECT tbl_agent.agentID, tbl_agent.agent FROM tbl_agent ORDER BY [agent];

Sorry to pester, but I think this is a bit beyond my non-existant coding skills

 

[RuralGuy]

I goofed in my AIR CODE! This line:

"VALUES ('" & NewData & "', Me.cboagentID.Column(0));"

...should be written like:

"VALUES ('" & NewData & "', [b]" & Me.cboagentID.Column(0)) & "[/b];"

 

[damonc]

RG,
you're a superstar! thanks you very much for the speedy help.

pasting this line gave a syntax error:

"VALUES ('" & NewData & "', " & Me.cboagentID.Column(0)[B])[/B] & ";"

But I changed it to:

"VALUES ('" & NewData & "', " & Me.cboagentID.Column(0) & "[B])[/B];"

Which is what you had originally in the 4th post. So somewhere along the line, I had buggered it up, but not to worry, everything is working perfectly now.

Thanks again!

 

[RuralGuy]

You are correct and you are very welcome. Glad I could help.