Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Posts
    25

    Unanswered: Error code 94 in Access

    hello users !

    i have a problem if any body can solve it or suggest me i would be happy the problem is i got a form with a button on it and on pressing the button it transfers the contents from a text file to a temporary table and then on divides the name into First name and lastname which are seperated by comma and sends to other table along with the remaining fields the problem is the data is being transfered to the temporary table but from there it is not going to the other table can anyone help me out i am putting the code below. the error i get is Error code '94' - Invalid Use of Null. and when i say to debug it points to the line string1 = allrecs!Name ( the first one in the code), any help would be appreciated.

    Function Updatetable()
    Dim allrecs As Recordset
    Dim newrecs As Recordset
    Dim thisdb As Database
    Dim numrecords As Integer
    Dim string1 As String
    Dim stringllen As Integer
    Dim FNAME As String
    Dim LNAME As String
    Dim i As Integer
    Dim commapos
    Set thisdb = CurrentDb
    With thisdb
    Set newrecs = .OpenRecordset("tblstudents", dbOpenTable)
    Set allrecs = .OpenRecordset("tbltemp", dbOpenTable)
    Debug.Print "number of records is"; allrecs.RecordCount
    string1 = allrecs!Name
    With allrecs
    Do Until .EOF
    string1 = allrecs!Name
    stringllen = Len(string1)
    commapos = InStr(1, string1, ",")
    LNAME = Mid(string1, 1, commapos - 1)
    FNAME = Mid(string1, commapos + 1, stringllen)
    newrecs.AddNew
    newrecs!LNAME = LNAME
    newrecs!FNAME = FNAME
    newrecs!ID = allrecs!ID
    newrecs!DOB = allrecs!DOB
    newrecs!SEX = allrecs!SEX
    newrecs!STREET = allrecs!STREET
    newrecs!CITY = allrecs!CITY
    newrecs!STATE = allrecs!STATE
    newrecs!ZIP = allrecs!ZIP
    newrecs!PHONE = allrecs!PHONE
    newrecs!HSTREET = allrecs!HSTREET
    newrecs!HCITY = allrecs!HCITY
    newrecs!HSTATE = allrecs!HSTATE
    newrecs!HZIP = allrecs!HZIP
    newrecs!CITIZEN = allrecs!CITIZEN
    newrecs!INS_NO = allrecs!INS_NO
    newrecs!VISA = allrecs!VISA
    newrecs!CLASS = allrecs!CLASS
    newrecs!MAJOR = allrecs!MAJOR
    newrecs!ENROLL_HRS = allrecs!ENROLL_HRS
    newrecs!PASSED_HRS = allrecs!PASSED_HRS
    newrecs!GPA = allrecs!GPA
    newrecs!GRADSTAT = allrecs!GRADSTAT
    newrecs!MSG_CODE = allrecs!MSG_CODE
    newrecs!HOLD = allrecs!HOLD
    newrecs!PC_CODE = allrecs!PC_CODE
    newrecs!TASP = allrecs!TASP
    If allrecs!CLASS < 5 Then
    newrecs!Level = "U"
    Else
    If allrecs!CLASS > 5 Then
    newrecs!Level = "G"
    End If
    End If
    newrecs.Update
    .MoveNext
    Loop
    End With
    End With
    thisdb.Close
    End Function

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    and what happens if you

    replace:
    string1 = allrecs!Name

    with:
    string1 = nz(allrecs!Name,"access was trying to tell you !Name is null")
    msgbox string1

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Mar 2004
    Posts
    25
    i get an error saying "iNVALID Procedure call or argument pointing to the code line LNAME = Mid(string1, 1, commapos - 1) what might be the reason,

    thanks for the initial response i really appreciate your help

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    but you didn't tell me if you got my msgbox:
    "access was trying to tell you !Name is null"


    my guess is you have a problem with your data: the recordset either does not conform to the format you are expecting ( "lastName, firstName" or similar), or the recordset is simply empty.


    what happens if you insert a line after:
    commapos = InStr(1, string1, ",")

    so it reads:
    commapos = InStr(1, string1, ",")
    if nz(commapos, 0) < 1 then msgbox "no commas in string 1! is string1 empty??", vbcritical, "wild guess"

    do you see the msgbox???





    ALSO...
    Set allrecs = .OpenRecordset("tbltemp", dbOpenTable)
    Debug.Print "number of records is"; allrecs.RecordCount
    will not work!

    so replace with:
    Set allrecs = .OpenRecordset("tbltemp", dbOpenTable)
    allrecs.movelast 'populate the recordset
    allrecs.movefirst 'go back to the first record for your loop thru
    Debug.Print "number of records is"; allrecs.RecordCount

    AND ALSO a minor detail:
    you have already done a
    with allrecs

    so you dont need "allrecs" in
    newrecs!DOB = allrecs!DOB
    and all similar lines: this is enough
    newrecs!DOB = !DOB
    ...but this is not the cause of your error


    FINALLY (and a wild guess), maybe your "first record" is actually the field names instead of the data. use a few more
    msgbox !sex & " " !someMoreFieldNames


    izy
    Last edited by izyrider; 03-30-04 at 13:30.
    currently using SS 2008R2

  5. #5
    Join Date
    Mar 2004
    Posts
    25
    thanks fro the replies i will have a look at all your suggestions and hopefully should solve my problem , once again i thank all of you for your responses.

Posting Permissions

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