Access form won't allow edits - it would yesterday, no changes to application

I have an access form that uses the following query as its basis:
SELECT tblCourses.CourseNumber, tblOrderDetail.Semester, tblOrderDetail.Section, tblOrderDetail.Year, tblCustomer.LastName, tblCustomer.FirstName, tblUniversities.UniversityCode, tblOrderDetail.RegistrationFormRcvd, tblOrderDetail.Grade, tblOrderDetail.GradeDate, tblUniversities.Audit, tblCustomer.CustomerPK, tblCustomer.EmailAddress, tblUniversities.UniversitiesPK, tblUniversities.UniverisityName, tblCourses.CourseTitle FROM tblCourses INNER JOIN ((tblCustomer INNER JOIN tblOrders ON tblCustomer.CustomerPK=tblOrders.CustomerPK) INNER JOIN (tblUniversities INNER JOIN tblOrderDetail ON tblUniversities.UniversitiesPK=tblOrderDetail.UniversitiesPK) ON tblOrders.OrdersPK=tblOrderDetail.OrdersPK) ON tblCourses.CoursePK=tblOrderDetail.CoursePK WHERE (((tblCourses.CourseNumber)=forms!frmGradeEntry!cboCourse) And ((tblOrderDetail.Semester)=forms!frmGradeEntry!cboSemester) And ((tblOrderDetail.Year)=forms!frmGradeEntry!txtYear) And ((tblUniversities.UniversityCode)=forms!frmGradeEntry!cboUniversity) And ((tblOrderDetail.Cancelled)=False)) ORDER BY tblCourses.CourseNumber, tblCustomer.LastName, tblCustomer.FirstName;

yesterday, I was able to edit the records in the 'order detail' based on this form.. today I cannot.  I went to last week's version of the database (both front & backend), no changes have taken place except data updates due to normal use,  in last weeks version of the database/app, I can update the records from the table, on the production version of the application, I cannot.  Any clues?
superthumperAsked:
Who is Participating?
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
aHA ...

A **DeCompile** may help here ...

But first, if you have not already:
Check for any **Missing References via the VBA Editor>>Tools>>References ....

Then, follow this procedure:

0) **Backup your MDB**
1) Compact and Repair the MDB, as follows:
Hold down the Shift key and open the MDB, then from the menu >>Tools>>Database Utilities>>Compact and Repair ...
2) Execute the Decompile (See example syntax below) >> after which, your database will reopen.
3) Close the mdb
4) Open the mdb and do a Compact and Repair (#1 above).
5) Open the mdb:
    a) Right click over a 'blank' area of the database window (container) and select Visual Basic Editor. A new window will open with the title 'Microsoft Visual Basic' ... followed by then name of your MDB.
    b) From the VBA Editor Menu at the top of the window:
       >>Debug>>Compile
        Note ... after the word Compile ...you will see the name of your 'Project' - just an fyi.

6) Close the mdb
7) Compact and Repair one more time.

*** Executing the DeCompile **EXAMPLE**:
Here is an example of the command line syntax  (be SURE to adjust your path and file name accordingly) for executing the decompile:

Run this from Start>>Run, enter the following command line - all on one line:

"C:\Program Files\Microsoft Office\Office\Msaccess.exe" /decompile "C:\Access2003Clients\AzDoc\Pgrm\AzDocPgrm2K3.mdb"

For more detail on the Decompile subject ... visit the Master on the subject (and other great stuff) Michael Kaplan:

http://www.trigeminal.com/usenet/usenet004.asp?1033

mx
0
 
superthumperAuthor Commented:
"I can update the records from the table,"  should read "I can update the records from the FORM,",  sorry:|
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
You are joining more than 2 additional tables, so this query will *not* be updatable.

Why do you have so many tables to drive one form?

mx
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
superthumperAuthor Commented:
it works on my test database - just not in the live environment - nothing has changed, at least not intentionally
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Paste the query into the SQL view of a new query ... and then Run the query ... and see if you can directly update it directly.

mx
0
 
superthumperAuthor Commented:
I did that - I can update in my test environment, cannot in the live environment
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
ok ... your live environment ... possibly a folder permissions problem ?

mx
0
 
superthumperAuthor Commented:
that's not it either- I just replaced the live backend with my development backend and i was able to update from the form - it seems like the backend is corrupt somehow....
0
 
superthumperAuthor Commented:
by using this method I found the problem which was a orphaned child record in the 'order detail' table - referential integrity with a cascading delete should not have allowed this - I think I'll upsize this backend to SQL server while I'm at it....
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Excellent.  Keep the Decompile handy.  Unfortunately, you will need it again in the future.

mx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.