not in list

[slimjen1]

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

 

[r.harrison]

Hi,

I know this seems a little picky, but SQL requires spaces to be in the correct place. Try replacing

strSQL = "Insert Into MovieGenre ([MovieGenre],[MovieGenreDescription]) values ('" & NewGenreID & "','" & NewData & "')"

with

strSQL = "Insert Into MovieGenre ([MovieGenre], [MovieGenreDescription]) values ('" & NewGenreID & "', '" & NewData & "');"

Also you may need to requery the combobox to see the new items.


Hope this helps.

 

[slimjen1]

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.
Thanks

 

[r.harrison]

You'll need to split the string into 2. I'm guessing that the NewData variable would be as you posted '1234,Test' if this is the case then try this code:

Private Sub cboAcctCodes_NotInList(NewData As String, Response As Integer)

    Dim strSQL As String
    Dim i As Integer
    Dim Msg As String
    [COLOR="Red"]Dim NData as Variant[/COLOR]

    'Exit this sub if the combo box is cleared
    If NewData = "" Then Exit Sub
    [COLOR="red"]NData = Split(Newdata,",",2)[/COLOR]

    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 ('" & [COLOR="red"]NData(0)[/COLOR] & "','" & [COLOR="red"]NData(1)[/COLOR] & "');"
        CurrentDb.Execute strSQL, dbFailOnError
        Response = acDataErrAdded
    Else
        Response = acDataErrContinue
    End If

End Sub

 

[slimjen1]

I am getting: the text you entered isn't in the list Select an item in the list" after it ask me do I want to add the item to the list.

 

[r.harrison]

Hi, This isn't as easy as I first thought. The 'Item not in list' message fires automatically, so I've got a work around for you. Instead of using the [notinlist] event, use the [AfterUpdate]:

Private Sub cboAcctCodes_AfterUpdate()

    Dim ndata As Variant
    Dim InList As String
    
    ndata = Split(Me.cboAcctCodes, ",", 2)
    InList = Nz(DLookup("[TXT_ACCOUNTINGCODE]", "tbl_ACCOUNTINGCODES", "[TXT_ACCOUNTINGCODE] = '" & ndata(0) & "'"), "[BLANK]")
    If InList = "[BLANK]" Then
        CurrentDb.Execute ("INSERT INTO tbl_ACCOUNTINGCODES ([TXT_ACCOUNTINGCODE], [TXT_ACCOUNTING CODE DESCRIPTION]) VALUES ('" & ndata(0) & "', '" & ndata(1) & "');")
        Me.cboAcctCodes.Requery
        Me.cboAcctCodes = ndata(0)
    End If
    
End Sub

Hope this works for you!

 

[slimjen1]

OMG! It worked!
How do I get the combo box to go blank for the next entry if needed.
Thanks so much!

 

[r.harrison]

cboAcctCodes="" will clear the box for you.

Glad to be of service!