Results 1 to 8 of 8
  1. #1
    Lounger
    Join Date
    Nov 2001
    Location
    Newark, New Jersey, USA
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Update Recordset (Access 2000)

    Trying to use the .AddNew in a recordset to add a new row to a table, but I must be missing something. Everytime it gets to the .Update command, it goes to the ErrorHandler. The general code is from the Access for Developers Book by Getz, Litwin & Gilbert (Chapter 3). I've attempted to modify it to meet my needs, but seem to have hit a road block. Any suggestions would be appreciated.

    Public Function Save() As Boolean
    Dim rst As ADODB.Recordset

    On Error GoTo HandleErrors

    Set rst = New ADODB.Recordset
    rst.Open "Estimate_Header", _
    Application.CurrentProject.Connection, adOpenKeyset, _
    adLockOptimistic

    rst.AddNew
    rst!ESTNO = mstrEstNo
    rst!VersionNumber = mstrVersionNumber
    rst!ESTNAME = mstrEstName
    rst!SKETCHNO = mstrSketchNo
    rst!DIVNAME = mstrDIVNAME
    rst!REQBY = mstrReqBy
    rst!PREPBY = mstrPrepBy
    rst!COR = mstrCOR
    rst!OVOM = mstrOVOM
    rst!UGOM = mstrUGOM
    rst!STRTDATE = mdatSTRTDATE
    rst!ENDDATE = mdatENDDATE
    rst!SurchargeRateDate = mdatSurchargeRateDate
    rst!Comments = mstrComments

    rst.Update

    Save = True

    ExitHere:
    If Not rst Is Nothing Then rst.Close
    Set rst = Nothing
    Exit Function

    HandleErrors:
    'Save = False
    'Resume ExitHere
    MsgBox ("Error!")
    End Function

    In the "HandleErrors" section, I commented out the two lines for save=false and resume Exithere because I was tossed into a infinite loop. The msgbox is just for troubleshooting.

    If anyone has any questions/suggestions, I'm all ears! Thanks!
    Rich

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Edmonton, Alberta, Canada
    Posts
    326
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update Recordset (Access 2000)

    I hope you're not planning on simply displaying "Error!" when error happen (as they will) in programs that anyone else is going to use! <img src=/S/puke.gif border=0 alt=puke width=60 height=15>

    Get your program to display the actual error; change your msgbox("Error!") to at least msgbox(err.description). I usually am a little more elaborate and use msgbox(err.number & " " & err.description) just in case I want to look up the error number.

    Or, if you really don't want to display the actual error in your code, in Visual Basic (i.e. with your code displayed) go to Tools | Options | General and set "Break on all errors" to disable your error handling temporarily.

    Then, let us know what the actual error is.

  3. #3
    Lounger
    Join Date
    Nov 2001
    Location
    Newark, New Jersey, USA
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update Recordset (Access 2000)

    Thanks... I hadn't used that err.description (learning more about this everday). I had a field with a zero length. Speaking of which... when I try and save the recordset with some fields left blank, I get a message that the field cannot contain a null value. Is there a way to save the recordset with null fields? Or should I just give all the fields default values of 0?

    I appreciate the help.
    Rich

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update Recordset (Access 2000)

    Look in the design of the table.
    In the properties of the fields, set Allow Zero Lenght to yes if you wish to enter a Null
    Francois

  5. #5
    Lounger
    Join Date
    Nov 2001
    Location
    Newark, New Jersey, USA
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update Recordset (Access 2000)

    Okay... looked there, and changed the attribute to allow zero length. I was a little off with the error msg... the exact message I'm receiving is "Invalid use of Null." Which takes me to the debug screen and the first expression in the code that contains the null value. Any thoughts?

    Thanks

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update Recordset (Access 2000)

    If your fields are numbers, to enter 0 use
    rst!ESTNO = Nz(mstrEstNo,0)
    The allow zero lenght is for text.
    Francois

  7. #7
    Lounger
    Join Date
    Nov 2001
    Location
    Newark, New Jersey, USA
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update Recordset (Access 2000)

    Hmmm...
    Actually all the fields are text, with exception of some date fields. Any field left empty gives the error 94 Invalid Use of Null. I'll paste the code where the error occurs below...

    Private Sub GatherFields(objEstimate As Estimate)

    With objEstimate
    .ESTNO = Me!txtEstimateNumber
    '.VersionNumber = Me!txtVersionNo
    .ESTNAME = Me!txtEstimateName
    .SKETCHNO = Me!txtSketchNo
    .DIVNAME = Me!txtDivision
    .REQBY = Me!txtRequestedBy
    .PREPBY = Me!txtUser
    .COR = Me!txtCOR
    .OVOM = Me!txtOHOM
    .UGOM = Me!txtUGOM
    .STRTDATE = Me!datStartDate
    .ENDDATE = Me!datEndDate
    .Comments = Me!txtComments
    End With
    End Sub

    Whichever is the first blank field, is line of code the debug window opens to.

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update Recordset (Access 2000)

    A can't reproduce the error in DAO.
    I think the error must come from ADO AND I have no experience with it <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Maybe someone else can help you.
    Francois

Posting Permissions

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