2003 DB shuts down in Vista

[Ruth]

I am having a problem with a database that I use annually to enter
survey results. It is in Access 2002-2003 format and I am working in
Vista. Since the change in OS is the only difference since last year,
I suspect that there’s some incompatibility there that is causing the
problem.

Here’s the setup: To enter survey results, I have a form with a
subform. The top part of the form is for respondent information. The
subform (empty when you open the form) is where the questions and
responses will be displayed. To start, I pick the survey I’m
completing (2008, 2009 or 2010 version) from a dropdown, then I enter
department, worker type, etc. When that is complete, I click an “Enter
Results” button that appends rows to the Responses table with the
appropriate linking information. The screen refreshes and the subform
displays the questions and response dropdowns for the survey
questions. When I've entered all the responses, I click a “New
Response” button and it gives me a new record.

The problem is occurring when I click the “Enter Results” button. I
get the message that Access has stopped working and is looking for a
solution. An autobackup is created. When I open the original database
again, the information for the top part of the form is saved and the
new records for responses have been appended to the Responses table.
I’ve compacted and repaired, I get no other error messages than the
one mentioned above, but it does this every time.

The code attached to the “Enter Results” button is this:
Private Sub cmdEnterResults_Click()
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
DoCmd.SetWarnings False
DoCmd.OpenQuery "qappNewResponses"
Me![sfrmResponses].Requery
DoCmd.SetWarnings True
End Sub

The SQL for the query that appends the response records is this:
INSERT INTO tblResponses ( QstnID, RspnsID, Rspns )
SELECT DISTINCTROW tblQuestions.QstnID, Forms!frmSurveyResponses!
RspnsID AS RspnsID, tblQuestions.RspnsDefault
FROM tblQuestions
WHERE (((tblQuestions.SrvID)=[Forms]![frmSurveyResponses]!
[cboSrvID]));

Can anyone suggest another way to write the button code or query that
would stop the error? Any other suggestions? With over a hundred
surveys to enter, I don’t think I can endure restarting the database
after every survey!

Thanks in advance,
Ruth

 

[Ruth]

OK. This is strange. I guess the fifth time was the charm. I went
about making some other updates in the reports, and then did a compact
and repair before I closed the DB. When I opened it and tried to enter
results again, it worked fine. However, I'm still open to suggestions
if anyone sees a better way to write the code and/or SQL above.

Thanks,
Ruth

 

[Arvin Meyer [MVP]]

You probably were experiencing a corruption. Compacting and repairing
multiple times usually fixes it.

 

[Ruth]

Thanks, Arvin. That's good to know!

Ruth

 

[Ruth]

I got a call from my client yesterday and the database was crashing
again just like before. I did a little more research and found this
resolution which worked perfectly:

http://www.access-programmers.co.uk/forums/archive/index.php/t-112575.html

Basically, it involved taking out the OnEnter event in the subform. It
now works like a charm. Per this article, don't use OnEnter, OnExit,
GotFocus or LostFocus events on subforms or controls on subforms. I
don't know why after two years this became a problem (the OnEnter
event has been there all along), but it was good to know how to fix
it.

Regards,
Ruth

 

[Jeanette Cunningham]

Glad you got it fixed.
However I use a great many GotFocus and LostFocus events for controls on
subforms and I don't have any problems .
The reason that I don't have problems with this is that I don't use these
events for any data operations - simply to set up helpful descriptive
messages and back color when the control has focus.
You can certainly get problems if you try to use these events for operations
on data.
I suspect a similar thing applies to the Enter and Exit events for controls.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia



I got a call from my client yesterday and the database was crashing
again just like before. I did a little more research and found this
resolution which worked perfectly:

http://www.access-programmers.co.uk/forums/archive/index.php/t-112575.html

Basically, it involved taking out the OnEnter event in the subform. It
now works like a charm. Per this article, don't use OnEnter, OnExit,
GotFocus or LostFocus events on subforms or controls on subforms. I
don't know why after two years this became a problem (the OnEnter
event has been there all along), but it was good to know how to fix
it.

Regards,
Ruth