Access Crashes when Form Recordset is Changed through VBA

[hughesai]

Help !

I have a form which shows various levels of information depending on parameters selected by a user. A recordset is generated, form opened in design view, recordset and various other bits changed, form saved and opened in normal view - everything is fine.

I have another button on the opened form. This closes the form and runs the same piece of code again to generate a summary. However, when the code reaches the line to change the recordset, Access crashes. It does not give an error message - the whole application closes with no warning.

I have tied various things to isolate the problem. It is the piece of code:

Forms!frmCumCharts.RecordSource = ???????

which always causes the crash. ???? is usually a variable containing a SQL string, but even with a very simple SQL or plain table, the result is the same - it crashes.

I can't understand why the code works for the initial opening of the form, but crashes so catestrophically when trying to do it a second time.

Anyone have any ideas?

Aidan Hughes

 

[psemianonymous]

I am guessing you have tied this code to an event. When you switch the recordset, it triggers events (Current() for example) and if your event calls code that switches the recordset, which then calls the Current() event....you see the problem.


I'd recommend you check and ensure that you're not doing something like this.

To confirm that this sort of behavior isn't a problem with switching the recordset itself, you can open the immediate window (<CTRL>-G) and type in

Forms!frmCumCharts.RecordSource = "sometablename"

which will hopefully work. If it doesn't work, then your problem lies in some sort of database corruption. If it works, then somewhere in your code you've created a sort of infinite &quot;assign new recordsource&quot; loop.

--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm

 

[hughesai]

Thanks for the reply.
I do not have any other events firing which are assigning new recordsets etc.
I tried the immediate window - it crashed ! ! ! !
I have compacted and repaired the database, and even used the JetComp utility from M$, but this has not hepled.

I have concluded that the problem is something to do with assigning a recordset twice to a form, even though I have closed the form and reopened it in between changing it.
To this end I have created two identical forms, and the code now closes one, and opens the other, then closes the second, and opens the first, the next time a button is pressed. This seems to have cured the problem for now.

No doubt the saga is not over yet, but I'm over this hurdle!

Aidan

 

[hughesai]

Brief update in case anyone else has this problem.

My two form solution above also fails because it crashes when the first form is reopened (the third iteration).

However, I think have solved the problem.

I was opening the form in design view, changing the recordsource, closing and saving it, and reopening it in normal view. It would appear that it is the closing and reopening that causes the crash.

I am now opening the form in normal view imediately and changing the recordsource in that view. With no closing and reopening, the issue is avoided. I thought that this was not possible, and that design changes had to occur in design view - but I must have been thinking of something else.

Hope this helps anyone else in this position.

Aidan.