not in list

[slimjen]

All, using access 2003. I have created an update form for a mgr to update tables used as combo boxes on data entry form. Five of the comb boxes have an autonumber id field, main field and description field. I have code to add one field to the combo box and it is working.

Private Sub cboAcctCodes_NotInList(NewData As String, Response As Integer)
   Dim strSQL As String
   Dim i As Integer
   Dim Msg As String
   'Exit this sub if the combo box is cleared
   If NewData = "" Then Exit Sub
   Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
   Msg = Msg & "Do you want to add it?"
   i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Employee...")
   If i = vbYes Then
      strSQL = "Insert Into tbl_ACCOUNTINGCODES ([TXT_ACCOUNTINGCODE]) " & _
               "values ('" & NewData & "');"
      CurrentDb.Execute strSQL, dbFailOnError
      Response = acDataErrAdded
   Else: Response = acDataErrContinue
   End If
End Sub

But I want to also have the mgr be able to add the description field. I found code to add multiple fields to a combo box but I don't know how to make it work for my situation. I have been trying to replace my values but keep getting all kinds of errors. Here is the original code without my modifications:

Private Sub MovieGenre_NotInList(NewData As String, Response As Integer)
   Dim strSQL As String
   Dim i As Integer
   Dim Msg As String
   Dim NewGenreID As String
   'Exit this sub if the combo box is cleared
   If NewData = "" Then Exit Sub
   ' Create a New Genre ID (MovieGenre) from the data entered
   ' by taking the first three characters of the string
   ' converting to uppercase to store in the table
   NewGenreID = UCase(Trim(Left(NewData, 3)))
   Msg = "'" & NewData & "' is not currently in the list of Genres." & vbCr & vbCr
   Msg = Msg & "Do you want create a new genre record?"
   i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Movie Genre...")
   If i = vbYes Then
      strSQL = "Insert Into MovieGenre ([MovieGenre],[MovieGenreDescription]) values ('" & NewGenreID & "','" & NewData & "')"
      CurrentDb.Execute strSQL, dbFailOnError
      Response = acDataErrAdded
   Else
      Response = acDataErrContinue
   End If
End Sub

It looks like it would add the main field and description like I want for my situation but it looks like it also adds the id field whick I already have in my autonumber.
Can anyone help modify my first code with this. Thanks

[RuralGuy]

Here's how your posts would look if you use the Code tags! The code tags are added when you go to advanced and press the "#" button. Quite a bit easier to read. Do not mix single line and multiline If...EndIf statements in the same procedure as you have done in both code sections. Access can get lost and give you unexpected results.

[slimjen]

Sorry; I did use code tags#. I wasn't aware that it didn't take.
Ok. I am trying to modify the code to place two fields into my combo box; main and description. Still getting errors. Don't know what I'm doing wrong. I will keep working at it.
Thanks

[RuralGuy]

You also have no Error Handler for the dbFailOnError. Have you single stepped the code to see what it is doing?

[slimjen]

ok. I modified the code with your suggestions and I got it to work but when the data appends to the fields, it puts the same data in both fields. ie entered 1234, test. Appends 1234, test in the first field and 1234, test in the other field. I need it to append the first part 1234 and 2nd part test in the other field. Here's my code:

 
Private Sub cboAcctCodes_NotInList(NewData As String, Response As Integer)
    Dim strSQL As String
    Dim i As Integer
    Dim Msg As String
    'Exit this sub if the combo box is cleared
    If NewData = "" Then Exit Sub
    Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
    Msg = Msg & "Do you want to add it?"
    i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Employee...")
    If i = vbYes Then
        strSQL = "Insert Into tbl_ACCOUNTINGCODES ([TXT_ACCOUNTINGCODE],[TXT_ACCOUNTING CODE DESCRIPTION]) " & _
                 "values ('" & NewData & "','" & NewData & "');"
        CurrentDb.Execute strSQL, dbFailOnError
        Response = acDataErrAdded
    Else
        Response = acDataErrContinue
    End If
End Sub

Also, How do I get it to clear the combo box for the next entry instead of having to backspace in the box.
I hope my code tags are coming thru.
Thanks

[RuralGuy]

Private Sub cboAcctCodes_NotInList(NewData As String, Response As Integer)
    Dim strSQL As String
    Dim i As Integer
    Dim Msg As String
    'Exit this sub if the combo box is cleared
    If NewData = "" Then Exit Sub
    Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
    Msg = Msg & "Do you want to add it?"
    i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Employee...")
    If i = vbYes Then
        strSQL = "Insert Into tbl_ACCOUNTINGCODES ([TXT_ACCOUNTINGCODE],[TXT_ACCOUNTING CODE DESCRIPTION]) " & _
                 "values ('" & NewData & "','" & NewData & "');"
        CurrentDb.Execute strSQL, dbFailOnError
        Response = acDataErrAdded
    Else
        Response = acDataErrContinue
    End If
End Sub

This code is single line If...Then
This code is MultiLine If...Then code.
They should not be in the same procedure.

[RuralGuy]

How do I get it to clear the combo box for the next entry instead of having to backspace in the box.

I take it this is *not* a bound ComboBox (no ControlSource)? Have you tried: Me.cboAcctCodes = "" ?

[slimjen]

It's appending to the fields it's just not slitting it. Here's my code modified:

 
Private Sub cboAcctCodes_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_cboAcctCodes_NotInList
    Dim strSQL As String
    Dim i As Integer
    Dim Msg As String
    Dim NData As Variant
    'Exit this sub if the combo box is cleared
'    If NewData = "" Then Exit Sub
    NData = Split(NewData, ",", 2)
    Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
    Msg = Msg & "Do you want to add it?"
    i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Employee...")
    If i = vbYes Then
        strSQL = "Insert Into tbl_ACCOUNTINGCODES ([TXT_ACCOUNTINGCODE], [TXT_ACCOUNTING CODE DESCRIPTION]) " & _
                 "values ('" & NData(0) & "','" & NData(1) & "');"
        CurrentDb.Execute strSQL
        Response = acDataErrAdded
    Else
        Response = acDataErrContinue
    End If
Exit_cboAcctCodes_NotInList:
    Exit Sub
Err_cboAcctCodes_NotInList:
    MsgBox Err.Number & ": " & Err.Description
    Resume Exit_cboAcctCodes_NotInList
End Sub

[slimjen]

The combo box is unbound

[slimjen]

I got this to work. Thanks for your suggestiions!

[RuralGuy]

Great! Thanks for posting back with your success.