Results 1 to 14 of 14
  1. #1
    New Lounger
    Join Date
    Nov 2001
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Problems adding data to linked table with code (A2k)

    I just split a database that has been in use for several years. Other groups at my site are looking at using a copy of the database and I wanted an easy way to distribute modifications. I realized that several of modules caused errors because of the specified type in the openrecordset statement and fixed that. It appears that everything is working correctly except one module. It is used to programatically add data to one of the tables in the backend database (see code below). However, once it executes it appears to leave something open because I can't run any other code or even close the form from which the module below is called. I thought it might be because I hadn't set the type of database and recordsets. But changing the code to Dim dbs As DAO.Database etc. didn't help. I also added the lines to close the recordsets and the database and set their values to nothing without solving the problem. Any help or suggestions would be appreciated. I am having to open another database before the hung database will open again.

    Dim dbs As DAO.Database
    Dim frm As Form
    Dim rst As DAO.Recordset
    Dim rst2 As DAO.Recordset
    Dim X As Integer
    Dim Project As Integer
    Dim StartDate As Date
    Dim EndDate As Date
    Dim LastDate As Date
    Dim NextDate As Date
    Dim Unit As String
    Dim Freq As Integer
    Dim lngrecordnum As Integer

    On Error GoTo cmdAddDatesError

    'Pick up info from current Projects in form
    Set dbs = CurrentDb
    Project = Me![ProjectID]
    StartDate = Nz(Me![StartDate], Now())
    EndDate = Nz(Me![EndDate], DateAdd("YYYY", 1, Now()))
    Freq = Me![Frequency]
    Unit = Me![FrequencyUnits]
    LastDate = StartDate
    NextDate = DateAdd(Unit, Freq, LastDate)
    Set rst = dbs.OpenRecordset("tblDueDates")

    'Create Due Date from Initial Start Date
    With rst
    .AddNew
    !ProjectID = Project
    !DueDate = StartDate
    On Error Resume Next
    .Update
    End With

    'Create Due Dates until End Date based on repeat frequency

    Do Until NextDate > EndDate
    With rst
    .AddNew
    !ProjectID = Project
    !DueDate = DateAdd(Unit, Freq, LastDate)
    LastDate = !DueDate
    NextDate = DateAdd(Unit, Freq, LastDate)
    On Error Resume Next
    .Update
    End With
    Loop

    cmdAddDatesExit:
    Me.Refresh
    rst.Close
    rst2.Close
    dbs.Close
    Set rst2 = Nothing
    Set rst = Nothing
    Set dbs = Nothing
    Exit Sub

    cmdAddDatesError:
    MsgBox Err.Description
    Resume cmdAddDatesExit

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

    Re: Problems adding data to linked table with code (A2k)

    From your description, it sounds like you're going into an infinite loop, which will never return control to the user interface. I'd suggest you put a breakpoint into your code at the start of your do loop and follow the values for a few loops to see what's actually going on.
    Charlotte

  3. #3
    New Lounger
    Join Date
    Nov 2001
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problems adding data to linked table with code (A2k)

    I thought of the possibility of a loop after posting my question. The module is called from an event procedure (button click). Following the code indicates that the module runs, goes through the exit procedure and returns to the event procedure - which also completes. I can run most of the other event procedures or other modules. What appears not to run is any DoCmd function (such as DoCmd.Close, DoCmd.GotoControl, DoCmd.FindNext, etc.). Also, I can't close any form or change to design mode. Thank you for your help.

  4. #4
    New Lounger
    Join Date
    Nov 2001
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problems adding data to linked table with code (A2k)

    I have tried several ways of watching what is going on as the code progresses and can find no errors. Everything seems fine except when I try to run the DoCmd function I get an error that says I cannot perform that function at this time. What could prevent the DoCmd from running? Thanks.

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

    Re: Problems adding data to linked table with code (A2k)

    Are you only having trouble in the VBE window, when you're tyring to watch the code, or do you have problems when you just run the application? You can't always break into code in Access 2000, and there are instructions that will error in break mode but execute properly in runtime. You are using Access 2K front and back end, right? What version of Windows and what service releases/service packs of both?
    Charlotte

  6. #6
    New Lounger
    Join Date
    Nov 2001
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problems adding data to linked table with code (A2k)

    It seems to happen regardless of whether I've added a break and step through the code or if I just allow the code to run. I am using Access 2K for both (I converted the database over a year ago when we upgraded our computers) and it worked fine until I split the database (both parts of the database are running locally). I'm using Win 2K Pro Ver 5.0.2195 SP 1 and Access 2K 9.0.3821 SR-1. Although updates are pushed by our systems administrator, I can't think of any changes that have occurred over the time I split the database. One additional item is that I kept a non-split copy of the database and it still works fine. I just can't understand what would prevent the DoCmd functions from executing. Thanks for your help.

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Problems adding data to linked table with code (A2k)

    I've chewed this over with a couple of folks, and it sounds suspiciously like a references problem. I would suggest you compare the references in your front-end mdb with those in your non-split one and make sure that they are exactly the same and in the same sequence. BTW, how did you split the database? Did you use the Database Splitter wizard, or did you do it manually. If you did it manually, did you copy the tables into a new database, or did you copy everything but the tables into a new database.

    Two other more remote possibilities: the front-end database could be corrupt, and not repairable, or you may have a problem with one of the Access dlls. The latter is pretty remote as it sounds like other databases work just fine. To test the first case, simply import all the objects in the front-end into a new database, and then recreate the necessary references. You might also check and make sure that Name Autocorrect is turned off, as it has been indicted as a problem in lots of weird happenings. Hope this helps.
    Wendell

  8. #8
    New Lounger
    Join Date
    Nov 2001
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problems adding data to linked table with code (A2k)

    Well, I'm getting more irritated now. I was checking the references between the split and non-spil databases and found some differences (all due to my trying to solve this problem). I fixed those and made sure the front-end and back-end had the same references. Then for grins I decided to run the original database. It also hung. This means that it probably isn't the current database.

    I went ahead and ran the Microsoft Office repair option (under add/remove control panel) but it didn't help. I hate to think that I've got to re-install Access. It is strange that all functions work fine until I run the code that adds records to a table [The main task information is located in one table (each with an autoentered task number) and the specific repeating due dates reside in another table linked by the task ID - it is the addition of the due dates that is causing the problem.]

    When I run the code in break mode the error I'm getting is "Run Time Error 2486 - You can't carry out this action at the present time." When I try to move to design mode the error I get is "You can't switch to a different view at this time. Code was executing when you tried to switch views." But when I watch the code it looks like all of the functions and macros end properly. An On-Click Event calls the function (that exists in a module). Perhaps I've got a conflict between a private sub and a public function. Thanks for your help.

    Private Sub cmdAddDates_Click()

    Dim strFormName As String
    strFormName = Me.Name
    AddDates (strFormName)

    End Sub




    Public Function AddDates(strFormName As Variant)
    ..
    ..
    ..
    ..
    AddDatesExit:
    frm.Refresh
    rst.Close
    dbs.Close
    Set rst = Nothing
    Set dbs = Nothing
    Set frm = Nothing
    Exit Function

    AddDatesError:
    MsgBox Err.Description
    Resume AddDatesExit

    End Function

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

    Re: Problems adding data to linked table with code (A2k)

    Well, I hate to point out the obvious, but if everything runs OK until you run that code and then it blows up, there absolutely has to be something wrong with the code. Let me qualify that--there has to be something wrong with the code in that context. It's perfectly possible to write a line of code that compiles and doesn't trigger an error directly but that will wreak havoc with the rest of your application. I've managed to do it several times myself. A good example of that kind of thing is the result you may get when you call an ADO function from DAO code. You may not even get an error, but you may suddenly find yourself back at the Windows desktop without knowing how you got there. There isn't necessarily anything wrong with the code, but that doesn't mean it will work. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

    May I suggest an experiment? Try commenting out all but the outer shell of your routine (i.e. the Public Function AddDates and End Function lines, plus the declarations and error handling and exit portions). Make sure you put in some On Error Resume Next lines in where your code might otherwise blowup in this rudimentary form (like right after the AddDatesExit label). Then run your code with just that stub. If that doesn't blow up, reenable the next level of code where you set the database object and try it again. Keep adding back lines one at a time or in matched pairs (If ... End If, etc.) until you know exactly what line is causing the error. At that point, you'll be ready to make some choices on how to handle it.

    I know this sounds like a lot of work, but it's the only way I've found to deal with this kind of situation. Until you know exactly where it's breaking, you won't be able to fix it or at least to know that you've fixed the right problem.
    Charlotte

  10. #10
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Problems adding data to linked table with code (A2k)

    I think Charlotte is right here - in some of our early experimenting and development work we managed several times to get get Access into a mode where it appeared there was no code running, but when you tried to close Access it wouldn't let you.

    One other thing you might try - get rid of the ADO reference completely as it doesn't appear that you are using it anywhere in the code. You might also turn off the Office 9 reference if you have it. That might just magically fix things. If not, you're pretty much stuck with Charlotte's approach.
    Wendell

  11. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Problems adding data to linked table with code (A2k)

    Hi Kerry,
    I have two quick questions:
    1. Does it make any difference if you call the AddDates function without the parentheses around strFormName - i.e. AddDates strFormName.
    It's a bit of a long shot since you're not passing an object variable to it, but it can occasionally cause strange errors.
    2. Can you post the entire code for your function? The version you just posted seems different from what you had before, and it might be helpful to be able to look at the whole thing and perhaps even run some tests on it.
    Thanks.
    Regards,
    Rory

    Microsoft MVP - Excel

  12. #12
    New Lounger
    Join Date
    Nov 2001
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problems adding data to linked table with code (A2k)

    Well, I think I've narrowed down the problem. I envisioned my code possibly being called from several different forms so I wanted to be able to pass the name of the form the function was called from so that the fields on the form could be used (as in Project = frm![ProjectID]). The button that calls the module has the following code:

    Private Sub cmdAddDates_Click()
    Dim strFormName As String
    strFormName = Me.Name
    AddDates strFormName
    ' I tried both AddDates (strFormName) AND AddDates strFormName with the same results
    End Sub

    The AddDates code that causes the system to "hang" appears to be the following since I commented everything else out:

    Public Function AddDates(strFormName As Variant)

    Set frm = Forms(strFormName)

    AddDatesExit:
    frm.Refresh
    Set rst = Nothing
    Set dbs = Nothing
    Set frm = Nothing
    Exit Function

    AddDatesError:
    MsgBox Err.Description
    Resume AddDatesExit

    End Function

    I'm not sure why assigning the form this way would cause a problem. Any thoughts? Thanks for everyones help.

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

    Re: Problems adding data to linked table with code (A2k)

    Have you tried turning on the error trapping in the AddDates function? And why are you passing a string to a variant and then using it as a string? I'd start by making the function argument a string instead of a variant and see whether that has any effect. I would suggest breaking into code in your button click event to see exactly which line is throwing the error. Plus, I would turn on error trapping to that you can actually trap the error number being returned.

    It is usually better to pass the actual form object rather than its name when you want to act on it like this, and it would eliminate the need to set an object variable to anything, since the object will have have passed in and will be destroyed eventually in the routine that pass it. What are you trying to accomplish with the refresh? .The only good use I've found for it is to avoid locking yourself out of multiple subforms based on the same table.
    Charlotte

  14. #14
    New Lounger
    Join Date
    Nov 2001
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problems adding data to linked table with code (A2k)

    I think I've got it licked but I'm not sure what caused the problem. In answer to your questions:
    - the error trapping was not seeing anything.
    - I fixed the string to variant to string issue without any improvement.
    - passing the string with the form name verses passing an object also didn't seem to make any difference.
    - the form refresh was added because I am adding records to a table associated with a subform on the form and initially I would not see the new data when the module stopped.

    Changing the form refresh to a form requery seems to have eliminated the problem. I'll need to look into the difference between a refresh and a requery to see if I can find an explanation. Thanks for everyone's input - it helped direct my investigation.

Posting Permissions

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