?
Solved

Can't change formatting in a form...

Posted on 2010-01-05
22
Medium Priority
?
361 Views
Last Modified: 2013-11-28
For some reason I suddenly can't change any formatting in any forms in my DB. Actually, I can change it, but I can't save the changes. It also appears to apply to group/sort as I had the same issue with a report earlier today, fixing it only by recreating the report. I do not want to have to do this to every form or report (or who knows what next) as it would be time consuming. I had completed most of the major cosmetic changes recently so this issue seems to have simply materialized out of no where. Earlier, when trying to copy/paste the report to see if that would get rid of whatever was causing the problem, I got the two erros during paste: "Name conflicts with existing module, project or object library" if pasting with "Copy of Form_FRM" and "The search key was not found in any record" if I changed the name. I have copied other forms, reports, etc without erros like these and no research turned up anything of significance. If my DB was corrupted somehow, how do I go about repairing it? I have used the diagnostic in Access Options > Resources and it didn't turn up any issues.

Thanks, in advance, for any suggestions.
0
Comment
Question by:Laura Sheldon
  • 13
  • 8
22 Comments
 
LVL 75
ID: 26183744
Try import ALL objects into a brand new ACCDB ....

mx
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 26183754
from access menu
tools > database utilities > compact and repair database


see if that will take care of the problem.

if not , you might have to recreate your form
0
 

Author Comment

by:Laura Sheldon
ID: 26183781
Where is "Compact and Repair Database" in access 2007?
0
Industry Leaders: 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!

 

Author Comment

by:Laura Sheldon
ID: 26183808
Nevermind! I found it. I'll try and let you know.
0
 
LVL 75
ID: 26183827
This may work also:

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

But first, if you have not already:
Open the VBA Editor and from the menu ...Tools>>References ....
If you see any listed as **Missing: <reference name>, including the asterisks and the word Missing, the
you need to fix that first.

Then, follow this procedure:

****
0) **Backup your MDB BEFORE running this procedure**
****
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 ...
Close the mdb after the Compact & 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) Close the mdb.
6) 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.

7) Close the mdb
8) 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) before executing the decompile:

Run this from Start>>Run, enter the following command line - **all on one line** - it may appear like two lines here in the post:
Also, the double quotes are required.

"C:\Program Files\Microsoft Office\Office\Msaccess.exe" /decompile "C:\Access2003Clients\YourMdbNameHERE.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:Laura Sheldon
ID: 26184032
I tried to decompile, changing the appropriate names but it wouldn't work. I looked where the command line indicated the compile switch might be and it wasn't there, allthough I may be looking for something that's not supposed to be there or is hidden (except I have all my hidden things showing). Is this for Access 2007 (I noticed the "Access2003Clients" reference in the CL)? If not, would you be able to provide guidance for Access 2007?
0
 
LVL 75
ID: 26184065
Decompile does in fact work in A2007.   You have to get the correct paths to the Access executable.

"I looked where the command line indicated the compile switch might be and it wasn't there"
Not quite sure what you mean ?

mx
0
 

Author Comment

by:Laura Sheldon
ID: 26184179
I guess I meant that I thought "decompile" was going to be in the folder the command line drilled down to, but I realize now it's meant to force the decompile to happen TO what's in the command line.

So I managed to do this, and it appears to be doing it, but my DB is on a server and I don't think it's affected by what I'm doing. Is it possible to decompile from a server or do I have to temporarily copy the DB to my hard drive?
0
 
LVL 75
ID: 26184202
Well ... yes it *can* be done on the server from your workstation, but ... definitely copy to your local work station ... Decompile, then copy back.

mx
0
 

Author Comment

by:Laura Sheldon
ID: 26184246
It's not letting me do it. I'm using the following:

"C:\Program Files\Microsoft Office\Office\Msaccess.exe" /decompile "C:\Access2007Clients\RTS Database.mdb"

Close to what you had above but with my own DB name. What next?
0
 
LVL 75
ID: 26184258
If this is A2007 then shouldn't MDB >>> ACCDB ?

What IS it doing ? Error ?

mx
0
 

Author Comment

by:Laura Sheldon
ID: 26184366
Yes, see attachment.
Access-error.gif
0
 

Author Comment

by:Laura Sheldon
ID: 26184413
OK, figured out (part of) the problem. My folder is Office12, but when it runs it says it can't find the file. I've checked and it's the correct path on the C drive. What am I missing?
0
 

Author Comment

by:Laura Sheldon
ID: 26184441
I think I must be getting tired. Forgot the underscore in the DB name. Will try again and let you know.
0
 

Author Comment

by:Laura Sheldon
ID: 26184505
Next issue:

Can't get here: 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. Do I need to open a module or is there another way to get where I need to be?

Access-error-1.gif
0
 
LVL 75
ID: 26184555
I'm not on my A2007 machine at the moment.  You need to get into the VBA editor.  You could start a new module ... do the Compile ... and not save the new module.

mx
0
 

Author Comment

by:Laura Sheldon
ID: 26184607
When I get to step 6.a., is opening Visual Basic from the "Database Tools" menu the same as going into "Visual Basic Editor"? and if so, in which part of the window do I perform the subsequent instructions? (See attachment)
Access-error-2.gif
0
 

Author Comment

by:Laura Sheldon
ID: 26184657
OK. I tried creating a new form and going into the Event property and debugging from the debug menu and completed (I think) the rest of the instructions and it is still not letting me save changes.
0
 
LVL 75
ID: 26184700
Can you:

1) Compact & Repair (*** to shrink the size),

2) Zip up the MDB (*** to further shrink the size)

3) Attach the file for upload here (using the 'Attach File function below) ... removing any sensitive data of course.

4**** And please give a clear explanation of exactly how to reproduce the problem or what you are trying to do.

NOTE:  If you have an A2007 ACCDB, please convert to A2003 MDB.
mx
0
 

Author Comment

by:Laura Sheldon
ID: 26184729
OMG! I'm not why it worked, but I went back to the original database (that I copied to my HD) and did a VB Editor debug in one of the forms I was having trouble with and it looks like it worked! I made a change and it let me save! I'll try on the others with issues (if that didn't do the universal trick) and let you know.
0
 

Author Comment

by:Laura Sheldon
ID: 26184782
:-)
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 2000 total points
ID: 26184820
ok.  you are welcome.

mx
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

850 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