Prev: saving and locking information in a form
Next: Making an Access 2007 app more professional looking.
From: Ruth on 28 Dec 2009 00:15
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
From: Ruth on 28 Dec 2009 00:57
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
From: Arvin Meyer [MVP] on 28 Dec 2009 09:51
You probably were experiencing a corruption. Compacting and repairing
multiple times usually fixes it.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


"Ruth" <ruthhays(a)gmail.com> wrote in message
news:9cef8b26-30b8-4e1b-90fe-aa42e70d83be(a)s31g2000yqs.googlegroups.com...
> 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


From: Ruth on 28 Dec 2009 19:13
Thanks, Arvin. That's good to know!

Ruth
From: Ruth on 27 Jan 2010 17:48
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

On Dec 28 2009, 6:13 pm, Ruth <ruthh...(a)gmail.com> wrote:
> Thanks, Arvin. That's good to know!
>
> Ruth

 |  Next  |  Last
Pages: 1 2
Prev: saving and locking information in a form
Next: Making an Access 2007 app more professional looking.