Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Jul 2007
    Posts
    72

    Unanswered: Combobox now "Invalid Use of Null"

    I had Combobox's on several forms that used a NotInList Event to add new items to the list. This was working until now. When a new item is added

    it uses this code that goes with the Module PubFuncAddNewToList

    Code:
    Private Sub cboLang_NotInList(NewData As String, Response As Integer)
    
    Dim intAns As Integer
    
    DoCmd.RunCommand acCmdUndo
    DoCmd.RunCommand acCmdUndo
    
    intAns = AddNewToList(NewData, "tblProf_exp", "PROFEXPID", "Prof_exp", "Skills of this type")
    Response = acDataErrContinue
    End Sub
    I am getting "Invalid use of Null" and then "Search key was not found in any record" This was previously working.

    It is only occurring on 2 of the comboboxes even though there are 4 based on the same query with only a different skilltypeid selected.

    e.g. SELECT tblProf_exp.PROFEXPID, tblProf_exp.Prof_exp, tblProf_exp.skilltypeid
    FROM tblProf_exp
    WHERE (((tblProf_exp.skilltypeid)=1))
    ORDER BY tblProf_exp.skilltypeid;

    Any ideas?

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Any reason why there are two undo commands?
    Also, if you could tell us which line was throwing the error, that'd be nice too
    George
    Home | Blog

  3. #3
    Join Date
    Jul 2007
    Posts
    72
    Well, I used the example :here
    and it has the two Undo commands.
    All error checking is just what is included in the code and I'm not sure how to see what line is causing the error. I'm not getting the debug window opening up as with other coding I have used. Is there a way to make this happen?

    I recently got hold of "Learn Access 2003 BVA with The Smart Method" mainly because I can't understand any of the error handling I see in the bits & pieces of code I pick up all over the place By recently I mean this afternoon and the problem is causing a bit of stress right now and I don't have time to calmly read a book. Although I hope to get into it soon.

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Sometimes two undos are needed. First undo for the current field and the second for the current record.

    Perhaps we need to see the AddNewToList procedure too.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Jul 2007
    Posts
    72
    This is the function to add a new item to the list.

    Code:
    Public Function AddNewToList(NewData As String, stTable As String, stTableID As String, stFieldName As String, strPlural As String)
    
    On Error GoTo Err_AddNewToList
    
        'Adds a new record to a drop down box list and
    
        'selects that records in the source drop down box that called this code
       
    addData:
    
        'Declare variables and set source objects
    
        Dim strSql As String, intStoreNewData As Long, objSource As Control
    
        Dim strMessage As String, intNewItem As Long
        
    
    
        Set objSource = Screen.ActiveControl
    
       
        strMessage = "'" & NewData & "' is not in the current list of " & strPlural & ". " & Chr(13) & Chr(13) & "Do you want to add it to the list of " & strPlural & "? " & Chr(13) & "(Please check the new entry is correct before proceeding)."
    
       
            strSql = "INSERT INTO [" & stTable & "] (" & stFieldName & ") SELECT '" & NewData & "';"
    
        
        ' Display message box asking if user wants to add a new item
    
        intNewItem = MsgBox(strMessage, vbYesNo + vbQuestion + vbDefaultButton2)
    
        'if yes
    
            '1. Run SQL statement to add to item to table
    
            '2. Recover new items record ID
    
            '3. Requery source combo box to refresh the combo box drop down list
    
            '4. Write the new items recordID to the combo box to display new record in box
    
    
        If intNewItem = vbYes Then
    
            CurrentDb.Execute strSql
    
            intStoreNewData = DMax(stTableID, stTable, "[" & stFieldName & "] = '" & NewData & "'")
    
            objSource.Requery
    
            objSource = intStoreNewData
    
    
        End If
    
        'move cursor out of and then back to the combo box to close the combo box list, but retain focus on combo box
    
        SendKeys "+{TAB}{TAB}"
    
    
    Exit_AddNewToList:
    
        Exit Function
    
    
    Err_AddNewToList:
    
        MsgBox Err.Description
    
        Resume Exit_AddNewToList
    
    End Function

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Well I can't see anything wrong there. Have you tried stepping through the code to see which line is throwing the error?

    Is there anything unusual about the "new data" that is being used or doesn't it matter?

    You say it was working before, now it's not. What changed?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    Jul 2007
    Posts
    72
    I made these forms a while back and used them to enter a sample of about 15 people (making about 300 records in the main table).

    This was fine and then I worked on forms and reports for display. It is now going back to enter the remaining staff members (about 70) that it is giving this error.
    I haven't made any changes since then to those forms and I haven't made any changes to tables or queries that they are based on. I got around one of them by just adding every language I could think of into the underlying table so there should be no need to add new ones. The other one unfortunately requires new items to be added so I can't really avoid fixing it.

    I'm not sure how to step through the code to see what is happening?

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    To step through your code, place the cursor in the first line of code and presss F9 to set a breakpoint. Then go and use your form, enter something into the combo box that isn't in the list. The code will stop at the breakpoint. Use F8 to step through the code one line at a time. Point to variables to see what they contain. You should then at least be able to identify the offending line. When finished you can remove your breakpoint by again using F9 with the cursor on the breakpoint.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I've never used these for adding this stuff:
    Code:
    DoCmd.RunCommand acCmdUndo
    DoCmd.RunCommand acCmdUndo
    Anyone know why they are there? (I know what they do but there must be some special requirement here because they are not necessary for handling the notinlist event)
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I'm also going off memory now but doesn't acDataErrContinue mean "ok - I've not handled the not in list event - throw an error". Shouldn't you use acDataErrAdded if data added successfully?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I've read and reread that code - I would abandon it - it does not look right to me. Things like
    Code:
    intAns = AddNewToList(NewData, "tblProf_exp", "PROFEXPID", "Prof_exp", "Skills of this type")
    worry me. Where is intAns declared? When is the value of intAns checked? AddNewToList is a function but never returns a value - why make it a function?

    That's in addition to the other problems I see.

    Something like this looks much more correct to my eyes:
    http://www.databasedev.co.uk/not_in_list.html
    Get that working and then you can split off some of the logic into a generic function.


    I can post my code when I get home if you like.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Jul 2007
    Posts
    72

    Smile

    Hi thanks for your input. The forum seemed to be down for a little while but if you are able to post your code that would be great and I will read the link you gave.

    Have read it and it is much simpler and with sensible error messages too. I thought of this before but that code just clarified that I did change my table
    This means that my NotinList needs to also insert a record into a field called Skilltype - on this combo box it will always be 2. I've tried to change it it but
    I always have 50 false starts before I get my sql syntax right! can anyone tell me?
    Code:
    Private Sub cboskillcentral_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 Skill...")
        If i = vbYes Then
            strSQL = "Insert Into tblProf_exp ([Prof_exp], ([Skilltype]=2)) " & _
                     "values ('" & NewData & "');"
            CurrentDb.Execute strSQL, dbFailOnError
            Response = acDataErrAdded
        Else
            Response = acDataErrContinue
        End If
    End Sub
    Last edited by bronisaurus; 11-14-08 at 10:43.

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I'll try to get on it this weekend. I forgot I have a lot on but I'll try to remember.....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Jul 2007
    Posts
    72
    Okay I'm this far and realise my =2 needs to go somewhere after the values but how do I put it in?

    The idea is that the Insert does a new skill i.e. NewData and always inserts a Skilltype of 2.

    Code:
    strSQL = "Insert Into tblProf_exp ([Prof_exp], [Skilltype]) " & _
                     "values ('" & NewData & "'), ;"
            CurrentDb.Execute strSQL, dbFailOnError

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Print out the strSQL rather than execute - it makes debugging easier.

    Code:
    strSQL = "Insert Into tblProf_exp ([Prof_exp], [Skilltype]) " & _
                     "values ('" & NewData & "', 2);"
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •