?
Solved

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

Posted on 2007-10-16
10
Medium Priority
?
943 Views
Last Modified: 2013-11-28
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?
0
Comment
Question by:superthumper
  • 5
  • 5
10 Comments
 

Author Comment

by:superthumper
ID: 20089059
"I can update the records from the table,"  should read "I can update the records from the FORM,",  sorry:|
0
 
LVL 75
ID: 20089088
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
 

Author Comment

by:superthumper
ID: 20089103
it works on my test database - just not in the live environment - nothing has changed, at least not intentionally
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 75
ID: 20089136
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
 

Author Comment

by:superthumper
ID: 20089148
I did that - I can update in my test environment, cannot in the live environment
0
 
LVL 75
ID: 20089169
ok ... your live environment ... possibly a folder permissions problem ?

mx
0
 

Author Comment

by:superthumper
ID: 20089254
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
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 1500 total points
ID: 20089506
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
 

Author Comment

by:superthumper
ID: 20092973
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
 
LVL 75
ID: 20094542
Excellent.  Keep the Decompile handy.  Unfortunately, you will need it again in the future.

mx
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question