Combo Box Limit To List Problem

[RodShinall]

I have a single table database that I created for my own personal use to store computer related information. I categorize this data by Topic, Primary Sub Topic, and Secondary Sub Topic which appear as combo boxes (cboTopic, cboPrimarySubtopic, and cboSecondarySubTopic) on my form. Each one has its Limit to List property set to Yes. When I update cboTopic an after update event runs code which restricts the cboPrimarySubtopic list to those subtopics related to the selected topic. Secondary subtopics are restricted similarly.
In an effort to handle new data not in list I searched through a basic VBA book and found the following procedure and substituted my control names:

Dim mbxResponse As VbMsgBoxResult
Dim strSQL1 As String
Private Sub cboTopic_NotInList(NewData As String, Response As Integer)
mbxResponse = MsgBox("Add New Topic?: " & NewData & ,vbQuestion + vbYesNo, "Message to Me")
If mbxResponse = vbYes Then
strSQL1 = "INSERT INTO tblRodsCRNotebook([Topic]) " & "VALUES('" & NewData & "');"
Call DoCmd.SetWarnings(False)
Call DoCmd.RunSQL(strSQL1)
Call DoCmd.SetWarnings(True)
Response = acDataErrAdded
Else
Response = acDataErrContinue
cboTopic = Null
End If
End Sub

The code above works exactly the way I want. It displays a message box asking if I want to add the new topic I just typed in. If I click Yes it adds the new topic to the cboTopic list. If I click No it does not and deletes the text just typed. (The only quirk with this is that it adds a record to the database with the new topic name in the topic field, but I use a Delete Query to automatically eliminate these on form close so it is not a problem for my purposes.)

Figuring I could use this same procecure for cboPrimarySupTopic by substituting control names, I used the following code:

Dim mbxResponse As VbMsgBoxResult
Dim strSQL2 As String
Private Sub cboPrimarySubTopic_NotInList(NewData As String, Response As Integer)
mbxResponse = MsgBox("Add New Primary SubTopic?: " & NewData & , vbQuestion + vbYesNo, "Message to Me")
If mbxResponse = vbYes Then
strSQL2 = "INSERT INTO tblRodsCRNotebook([PrimarySubTopic]) " & "VALUES('" & NewData & "');"
Call DoCmd.SetWarnings(False)
Call DoCmd.RunSQL(strSQL2)
Call DoCmd.SetWarnings(True)
Response = acDataErrAdded
Else
Response = acDataErrContinue
cboPrimarySubTopic = Null
End If
End Sub

This code does NOT work. It will not add the new data to the cboPrimarySubTopic list. I can’t figure out why it works for one combo box and not the other. I know this code looks crude to most of you professionals out there but I am just a novice. Any help would be greatly appreciated.

 

[DCrake]

What line is it failing on and what is being returned by strSQL2

David

 

[RodShinall]

From what I have been reading my problem stems from having only one table so a re-design is in order. Thanks for replying, David.