Problems adding data to linked table with code (A2k)

[KMuhlestein]

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

[charlotte]

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.

[KMuhlestein]

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.

[KMuhlestein]

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.

[charlotte]

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?

[KMuhlestein]

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.

[WendellB]

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.

[KMuhlestein]

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

[charlotte]

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.

[WendellB]

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.

[rory]

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.

[KMuhlestein]

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.

[charlotte]

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.

[KMuhlestein]

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.