Combo Box Question

[hawkins]

I have a combo box in a subform that requeries a list of capacitor banks for a given feeder name. The feeder name is in a main form and the combo box relating the name is namescmb. In a subform called CapacitorBanksubform with a control name of ctrCapBank the combo box Capcmb requeries and I am able to add a new capacitor name using this code but it does not include the feeder ID in a Name3ID column in the table containing the Capacitor bank list. I was wondering how to include the feedername in the change of the name. The capacitor bank list is in CapacitorBank table and the Feeder name is in the static table and is related by NameID in Static and Name3ID in CapacitorBank. My database file in attached to make it easier to answer possibly.



Private Sub Capcmb_NotInList(NewData As String, Response As Integer)
On Error GoTo Capcmb_NotInList_Err
Dim intAnswer As Integer
Dim strSQL As String
intAnswer = MsgBox("The Capcitor Number " & Chr(34) & NewData & _
Chr(34) & " is not currently listed." & vbCrLf & _
"Would you like to add it to the list now?" _
, vbQuestion + vbYesNo, "Avista Utilities")
If intAnswer = vbYes Then
strSQL = "INSERT INTO CapacitorBank([CapacitorBank]) " & _
"VALUES ('" & NewData & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
MsgBox "The new Capacitor number has been added to the list." _
, vbInformation, "Avista Utilities"
Response = acDataErrAdded
Else
MsgBox "Please choose a Capacitor number from the list." _
, vbInformation, "Avista Utilities"
Response = acDataErrContinue
End If
Capcmb_NotInList_Exit:
Exit Sub
Capcmb_NotInList_Err:
MsgBox Err.Description, vbCritical, "Error"
Resume Capcmb_NotInList_Exit
End Sub

[Rod]

I was wondering how to include the feedername in the change of the name.

I hope this is just a slip; INSERT INTO appends a new row into the table it does not change an existing row. To effect a change use UPDATE.

strSQL = "INSERT INTO CapacitorBank([CapacitorBank]) " & _
"VALUES ('" & NewData & "');"

Before proceeding I think there is an error above. CapacitorBank!CapacitorBank is defined on the table as numeric yet you are inserting a string! I leave you to sort that one out.

strSQL = "INSERT INTO CapacitorBank ([Name3ID], [CapacitorBank]) " & _
"VALUES (" & [Forms]![Main]![Namescmb] & ", " & ??? & ");"

The above should work for you after you have resolved the ??? string or number concern.