Unable to Save Changes and Other Problems

[rjgriffin46]

The Word attachment describes recent problems I have encountered, most significantly, the inability to save changes on certain forms. As always, help is appreciated. Also, please advise if this is a suitable way to post.

The questions, listed at the end of the document, are:

a) How can I identify which objects are corrupt other than assuming it is only those that won’t save changes?

b) What is the best way to overcome the inability to make form changes on certain forms and reports?

c) How or can I continue to use the Students Extended query with different criteria for different forms, other queries or reports. Can I create (advanced) filter queries and use as a record source for each as needed?

d) Do the messages about the PC making changes or placing the database in states have any bearing and why is that happening?

e) What causes and how can I prevent messages that fields could refer to more than table (see 2, D, vi)?



Database v 0.62 Problems.zip

[rjgriffin46]

I see that no one has posted a reply....

At this point, I am seeing that things put in place months ago are becoming dysfunctional as I attempt to put new functions in place. In particular, I have a Master-Child form (Tests and Grades) where the Master is bound to a Test Parameters table delineating the Class Number and Test number for which student results are to be entered. The child is a Test Results sub form bound to the Test and Grades table with a combo box lookup to the Students Extended query which limits the students displayed on the drop down list to those with the same Class as in the control in the loaded Test and Grades Master form.

In attempting to use the same technique for an Evaluations Parameters (Master) and Evaluations Results (Child), the reference in the lookup query (again based on the Students Extended query) does not result in limiting the drop down list - all the students are listed. If I put a literal value for the Class to equal (eg., = "2015-1") for the class, it works OK. When I went back to look at the Test Parameters and Grades it now does not limit the drop down list either. In fact you will get a prompt before the form loads to enter the Class number referring to the field name that was put in the criteria in the lookup query.

I've read, among other things, that using the Auto Name Correct function can create problems. As indicated in the document attached to my original post on this thread, I have been experiencing field names being changed to expressions (followed by a colon then the table and field name). However, when that option is de-selected, the Object Dependencies function does not work. I use this to identify delete objects (usually forms) that were various versions I was working on.

What I am planning to do is start with a blank database, turn off the Field Name Auto Correct option, link to the back end tables, and copy all the needed objects from a previously properly functioning version.

I am also wondering if there is a software tool, such as FMS'

Total Access Analyzer, that would be helpful.

Any advice would be greatly appreciated.

[orange]

What exactly have you done to attempt correcting the issue? You've had this for 15 days or so, right?
It sounds like "corruption". Have you tried compact and repair?
Have you tried creating a new empty database and then copy everything from the old db to new?

[rjgriffin46]

I compact and repair regularly. I am about to embark on starting with an empty database as you suggest.

Do you have any experience with Access database analyzer software such as FMS' Total Access Analyzer?

Any other thoughts?

Thanks for your interest.

[June7]

a) when something doesn't work the way it should and the misbehavior is just 'weird', corruption is often the issue and the only way to confirm is to rebuild something (control, form, db) with the same properties, if the new something works then the old was corrupted

b) if corruption is the issue the only way to 'overcome' is to rebuild and corruption is the only reason I know that would cause an object to be non-editable

c) I never use dynamic parameters in query - apply filter criteria to form or report object

d) Bearing on what - editing objects? Message 2.d.v is odd, no idea why this happened - does it happen every time?

e) include the table/query name as prefix to fieldname if the query includes multiple tables/queries that have same field names or don't use same field name in more than one table - select the field from the dropdown in the query design grid - let Access fix the SQL statement, then switch to SQL View if you want to see the result

[rjgriffin46]

Thanks for your responses. Below are questions on them:

a & b) What is the best way to move objects into the blank database? I started by turning off the Field Auto Name option, linking to a copy of the back end then opening the recent production front end and copying and pasting objects one at a time. Is this the best or an OK method, or should I import or export from the other database to the new blank one?

c) Do I use the Form Property "Filter"? I'm not clear on how one uses this as there isn't a builder for it. Does it get loaded from the underlying query. Help on this would be appreciated.

d) Referring to: "You attempted to open a database that is already opened by user ‘Admin’ on machine ‘DELL-PC’. Try again when the database is available.“ This occurred on Dell laptop when attempting Compact and Repair after working several hours on the database. I had to exit and re-open the database in order to do the Compact and repair. It happens frequently along with messages that the PC has put the database in a state where you can't do anything with it. Also happens on PC at FoodBank.

Thanks again for your interest

[June7]

If you import, the corruption issue might just propagate.

I use VBA to construct filter string. Example code: http://www.allenbrowne.com/ser-62code.html

DoCmd.OpenReport "report name", , , strWHERE

[rjgriffin46]

In light of the above issues I've encountered, I am interested in having my database evaluated and analyzed for performance and size optimization as well for determination if any objects are corrupted.

A redacted copy of the database has been placed on box.com along with related files, one of which, FSTA SAAS Redacted Copy Notes.docx, has guidance information about the database. The link below is to the folder with the database and related files which total about 10.8 MB.

https://app.box.com/s/9f0id6zokin0298tgcj2

It is worth noting, I think, that the database works fine in production although some forms, particularly Student Details, can be slow. All of the problems described in this thread occur during development.

I have begun re-creating the front end database starting with a blank Access file and copying (drag and dropping) objects one at a time.

Any thoughts, questions, recommendations etc. are welcome.

Thanks!