Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Adding records using .addnew (Access 97/SR2)

    I have the following which opens tblActivityReturns and adds a record based on the contents of a couple of text boxes. This works fine for the first record, but crashes when a second record is added. It doesnt return an Access error message, im using NT and a Dr Watson error appears telling me the program is going to close and it does just that.
    Im guessing that i've missed some sort of command to close the recordset after i've written to it, but I have no idea what if it is that.
    Any help would be much appreciated.

    Set writeRec = CurrentDb().OpenRecordset("tblActivityReturns", dbOpenDynaset)
    With writeRec
    .AddNew
    !ACA = Me.inACA
    !ActivityDate = Me.inDate
    !ActivityCode = Me.inActivity
    .Update
    End With

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Adding records using .addnew (Access 97/SR2)

    The Close method of the recordset closes it. You must also release the memory claimed by the recordset by setting the variable to Nothing.

    I have expereienced problems opening a recordset directly from CurrentDb. I usually create a variable of type Database and set it to CurrentDb.

    Dim dbs As Database
    Dim writeRec As RecordSet
    Set dbs = CurrentDb
    Set writeRec = dbs.OpenRecordset("tblActivityReturns", dbOpenDynaset)
    With writeRec
    .AddNew
    !ACA = Me.inACA
    !ActivityDate = Me.inDate
    !ActivityCode = Me.inActivity
    .Update
    .Close
    End With
    Set writeRec = Nothing
    Set dbs = Nothing

  3. #3
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding records using .addnew (Access 97/SR2)

    Thanks for your reply Hans.

    Unfortunately, after adding the .close bit and setting the recordset parts to nothing, the database still crashes, so my theory was incorrect. There are 16 fields that im trying to write to the table, so it may be something to do with that.
    Im going to attempt to make a temp table and write the data to that and then run an append query to add that data to the main table. The reason for this is that the method of adding data shown below seems to work without crashing, but only works for editing, not adding:

    rec.MoveFirst
    rec.Edit
    rec.Fields("ACAstring") = Me.inACA

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Adding records using .addnew (Access 97/SR2)

    Hi Phil,

    I hope your workaround will help.

    It still seems strange that your database crashes without displaying an Access error message. The number of fields you set shouldn't matter. If you're still willing to investigate, you might try some things:
    1. <LI>Decompile the database (do a search for decompile in this Forum if you don't know what that is), then compile and save all modules, finally compact and repair the database. This helps getting rid of all kinds of garbage your database accumulated over time.
      <LI>Single step through the code, noting carefully where you are at each step, and at what point Access crashes (if it does - sometimes code will run fine when you step through it, and cause problems when running automatically. I've seen the reverse too.)
    Regards,
    Hans

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Adding records using .addnew (Access 97/SR2)

    Stop trying to do it directly from the Currentdb object. Instantiate a database object and use that, then hold it open during your repeated efforts to add new records.
    Charlotte

  6. #6
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding records using .addnew (Access 97/SR2)

    Thanks for the tips HansV.
    Thanks for your reply Charlotte.

    Would anyone happen to have an example or a reference to look it up, of what Charlotte meant in the reply. I've only really used the one method for adding records to a table in code as I havent really needed to do it before now and despite trying to look it up, havent had much success in understanding what was said.

    Also, using my original method, I found that the database only quit on fields that got left blank. I attempted to enter the following line but got the error "object variable or with block variable not set", but only on the 2nd time I attempted to add data:

    If not isnull(me.inContact) or Len(Me.inContact) < 1 Then !SchoolContact = Me.inContact

    The idea was to check if the textbox was empty and skip the offending command if it was - back to the drawing board!

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Adding records using .addnew (Access 97/SR2)

    Just a wild guess: try replacing

    If not isnull(me.inContact) or Len(Me.inContact) < 1 Then ...

    with

    If Len("" & Me.InContact) > 0 Then ...

  8. #8
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding records using .addnew (Access 97/SR2)

    Thanks for your reply Hans.
    After posting my message I noticed I had the <1 wrong, and changed it without effect.

    However, I then tried your suggestion and that didnt work either.
    I added the line:
    me.inContact = null
    before the section of code that deals with the input and it worked fine. Its almost like it can't see the object unless I specifically assign something to it in the code.
    Im going to attempt to change the code to look at a variable and assign the contents of me.inContact to that variable at the top of the code - hopefully ill have some luck with that.

  9. #9
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding records using .addnew (Access 97/SR2)

    Woooooooooo hooooooo. Fixed it - and yes, it was a really stupid mistake that I should've seen earlier and I forgot to mention in the post:

    The last thing the code does after saving the record is blank the fields it took its input from, leaving the user to add another record.
    It did this by calling some code in a module. I made this part ages ago when I was still learning and didnt know the rule about Subs dont have to return things and functions do.
    I had a function that set the text boxes to null and thats all it did - I changed it to a sub and it all works now. Sorry for wasting you're time and thanks for helping. In future I shall make sure mistakes from the past dont hinder the current work!

Posting Permissions

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