Combo Box- Add New Record

[hawkins]

Since figured out but have an error with what i got working farther down:I have a combo box called Yearcmb on the ctrYearlySubForm and the Yearcmb is recorded to a YearRecorded column in the Yearly table. The Yearcmb is dependent on a combo box on the Main form called Namescmb and is in the Static table. I have the code below to add a new year if the year typed in is not already a record for that name. A record is created with the correct entered year value but the name, in the Name2ID location in the Yearly table, is not included in the table with the year. I was wondering how do i manipulate the INSERT command to also include the feeder name. Once again, Name2ID comes from the Main form and is determined by the Namescmb..[Forms]![Main]![Namescmb]
When I exchanged:
strSQL = "INSERT INTO Yearly([YearRecorded]) " & _"VALUES ('" & NewData & "');"

with:
strSQL = "INSERT INTO Yearly([Name2ID]) " & _"VALUES ('" & [Forms]![Main]![Namescmb] & "');"

The name was recorded correctly. Any help will be greatly appreciated!


Private Sub Yearcmb_NotInList(NewData As String, Response As Integer)
On Error GoTo Yearcmb_NotInList_Err
Dim intAnswer As Integer


Dim strSQL As String
intAnswer = MsgBox("The Year " & 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 Yearly([YearRecorded]) " & _
"VALUES ('" & NewData & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
MsgBox "The new Year has been added to the list." _
, vbInformation, "Avista Utilities"
Response = acDataErrAdded
Else
MsgBox "Please choose a Year from the list." _
, vbInformation, "Avista Utilities"
Response = acDataErrContinue
End If
Yearcmb_NotInList_Exit:
Exit Sub
Yearcmb_NotInList_Err:
MsgBox Err.Description, vbCritical, "Error"
Resume Yearcmb_NotInList_Exit
End Sub

I have since changed the code to:
strSQL = "INSERT INTO Yearly([YearRecorded],[Name2ID]) " & _
"VALUES ('" & NewData & "','" & [Forms]![Main]![Namescmb] & "' );"

A new year is created with the year but on my sub form the other text boxes do not update and have to click refresh a couple times before the rest of the boxes will update with the year info, which is all 0's. I was wondering how I could update the rest of the boxes when the year is entered. Also a thing that may be a cause is when i enter a new year I get a message box saying "The text you entered isnt an item in the list. Select an item from the list, or enter text that matches one of the listed items." I have attached a file of the database which might make it easier to understand whats going on.

[June7]

strSQL = "INSERT INTO Yearly([YearRecorded], [Name2ID]) " & _
"VALUES ('" & NewData & "', '" & [Forms]![Main]![Namescmb] & "');"