Newbie to MS Access deleted something essential. HELP!
September 16, 2010 4:07 PM   Subscribe

[MSAccess Filter] HELP! I have been thrust into Access with no understanding of how it works beyond a 10-minute "use this" and "do that" session. Now I think I inadvertently deleted a couple of essential "forms." Can I get them back? Or rebuild them? I know the data must still be in there, but I don't know how to display it.

I'm using a 15-year-old database in MS Access 2010. I open a "form," which displays a large database of records through which I can click. This form has a couple of buttons one can press for more details.

The problem is: I try to click one of these buttons that I know was once associated with information, and I get the error message:

"The form name 'History' is misspelled or refers to a form that doesn't exist"

There has to be a logical solution. All that data for each record is still somewhere, right? It's just no longer displaying. Can someone guide me through the steps to show it?
posted by AnOrigamiLife to Computers & Internet (7 answers total)
Have you moved or renamed any files? Access is a PITA with file names and locations.

And can you take screenshots of what you have without sharing sensitive data?
posted by filthy light thief at 4:39 PM on September 16, 2010

Before testing or reconfiguring, always mount a scratch monkey

Are you certain the button ever worked? Maybe you didn't do anything?

Assuming you in fact did inadvertently delete only the forms, the data is likely still in the underlying tables. The thing is, to delete the forms, you'd have had to break out of the internal application to get to the "design view". If you did that, then you should be able to see the list of tables. From there you can view the data in a spreadsheet style grid. If you can't see the design view, I don't believe it's possible for you to have deleted the forms.

Assuming that the forms are really gone, you have a couple of options. If you haven't closed Access or restarted the computer, perhaps you can still use undo? If not, you'll have to restore from a backup. Once you've done that, be sure to make a "scratch monkey" copy for you to inspect the inner workings so that the production version remains inviolate.

(Please insert the standard lecture about the evils of Access here.)
posted by ob1quixote at 5:21 PM on September 16, 2010

Best answer: Is it possible the database files are backed up regularly? If they are on a regular backup schedule, they could be recovered. You could even possibly pull the missing forms from the backup, and import them (or recreate them) in the existing database, thereby not losing any data at all.

*crosses fingers*
posted by AltReality at 5:33 PM on September 16, 2010

Response by poster: @filthy: I didn't remove or rename any. But I did delete some that were more than 10 years old. Big mistake, apparently. I could take a screenshot, yes, but wouldn't know how to share it...

@ob1quixote (awesome name): I saw them work, and fortunately I know what they're supposed to look like. I also assume the data for the forms I deleted are still in there somewhere. I didn't have to go anyplace special to do this dirty deed. I have a window on my left 1/3 screen that lists them all. Indeed, I can also switch that window to display a list of tables. I see one that may be the place to start, as it shows a column name that matches my broken button, and checked boxes for all the records down that column. But no data. Just checked boxes. I guess this is because it's a form within a form?

I have closed Access many times since. And pretty sure I've restarted. The backup may be my only hope, depending on the IT guy's ability to find it. Scratch monkey...what a novel idea...I will certainly look in to that in the future.

@AltReality: This is my hope. Pending. I'm told "pulling the missing forms from the backup" is an Export Object action? I, too, am crossing my fingers for that backup...thx! But I may have to re-create the forms altogether, about which I know nothing.
posted by AnOrigamiLife at 6:37 PM on September 16, 2010

It's easy to delete things from Access and there is no Recycle Bin =( I don't use forms much, but exporting anything is pretty easy. In the backup database, right click on the item, select export, navigate to the current db and confirm. It may warn you that you are overwriting an already existing item. That's okay in this case since you are overwriting a broken one with a hopefully not broken one.

Of course, make a backup from today's db before you do any of this so if that fix doesn't work you haven't broken things further.
posted by soelo at 6:47 PM on September 16, 2010

I forgot to mention, you may need to fix some joins, or relationships between tables. It would be good to have a copy of the broken version to be able to see those if they still exist, even if they are broken now. When I try to open a query using a deleted table, it warns me and gives me the old table's name and when opened, what fields I was using. I can usually figure out what was joined based on those fields.
posted by soelo at 6:49 PM on September 16, 2010

1) Is "History" the form your working with, a subform (that is, a second form inset into your original form), or a third-party form that opens when you hit the button (or a subform of a third party form)? Obviously that has big implications for where you would look for a problem.

2) Do you (or anyone else) have access to the database window.

One obvious place to look for re-names & deletions would be the forms tab of the database window. Some databases are set up to make that bloody hard to get to. So: When your forms, reports, tables, queries & Macros are all closed, is there something on the screen that lists the available forms, reports, queries, tables & macros?

IF YES, go to the Forms tab or section of that display and look for the obvious: Is there a table called "xxxx". Is it spelled EXACTLY as it was spelled in your error message. (Be aware of spaces! An extra space at the beginning of a form name or slipped in at the end of a form's name in a line of code can cause this type of trouble.) If you find an obvious candidate, you can adjust the name to match the error message, but bear in mind that if other operations are done off this form name, you may be screwing yourself. Make notes on what things were & how you changed them. (Better yet, back up the damn database before touching ANYTHING.) When you are done mucking about, see if your button has gone back to working. Also check to see if you have new failure points. If you do, there's a good chance the form's actual name wasn't where the original oops happened; it was probably oopsed in the mechanics operation on the form & you should think about debugging your form functioning internally, rather than changing names in the DB window.

IF NO, your database may have been set up to protect the database structure by denying Access to the database window using any of a number of strategies. This makes it less likely that your problem will be found in the database window to begin with. That said, a lot of times the database window can be forced to open by closing the database completely, sticking your finger down on the shift key, and re-opening the database. Do not take your finger off the shift key until your computer stops muttering and the database is as open as it is going to get. With any luck, you will now be looking at the database window. Proceed to look for deletions & renames in the form area as described above.

3) If the form has been deleted, you are screwed unless you can (A) reconstruct it or (B) have a backup copy. Not even going to bother getting into A, since you either can or you can't. But (B) is a pretty easy fix, usually doesn't even require a recent backup, just a backup that contains a compatible example of the screwed up form, and is often easier than hunting down a wonky file name if you don't know what you're doing.

Basically, Access saves data completely separately from its forms and reports. Lose a form? Not the end of the world: The data is still safe and sound. Swapping in a replacement form has ZERO effect on the data itself; it's about like taking off the new sunglasses & dropping in yesterday's shades. You're still looking at the same data, just the window dressing has changed.

So: Go to the database window, isolate the offending form (forms? If it's got a subform, it's probably easier to swap out the whole kit & kaboodle) by re-naming them: On the forms tab, click the form once, type in a replacement name (repeat as needed for subforms, bearing in mind that a re-named subform will act like a broken form until the name is restored). If you don't re-name, the newly imported form will either overwrite it or be assigned another name, neither of which are my favorite. Then go to the file menu, and look for "Import," or "Get Data" or whatever terminology your version of Access uses. This will start a wizard that will walk you through pulling in the replacement form(s). Once they are in, take them for a test drive. If all looks good, you can dispose of the broken stuff at your leisure.

4) Assuming however that you do not have a backup copy to make fixing things simpler, and can't find an obvious problem in the forms list in the database window: Have you, or anyone else who has been working with the database, been messing around in Design View of the offending Form, Subform, or pop-up form?

In design view, it is possible to re-name a subform by typing over the name where it is displayed in the schematic, or in the properties box. When this happens, usually most of the form displays, with some associated whining about things not being found, but with the display area for the subform blanked out. Fix it by re-typing the name to match the subform's actual name.

If, however, the problem is in a separate form that opens/is displayed when the button is clicked, the area to check for accidental re-names would be in the coding that occurs when the button itself is clicked. This coding will either be in the form of a macro or vba code. To access it, open the form in design view, click the button that is evidencing the problem, then in the properties box, got to the EVENTS tab. (If the properties box is not open, right click on the offending button. One of your options will be "PROPERTIES".) From the events tab, find the line that triggers the event. Should be something like, "On click," and have some writing in the fill-in box next to it. When you select the line or the writing, three little dots will appear to the right. Click the three little dots, and this will bounce you into either a macro or a code screen. Look for a mispelling of the form name in what you find there. Correct it so that there is an exact match in spelling/punctuation/spacing to the item that is supposed to open.

Note that a mispelling can be something as simple as a " " when there should not be one (although capitals are irrelevant). Check the error message itself to see if there is something weird like that going on. The name specified in the macro or code must match the name of an existing form --in point of fact, the correct existing form-- in order for things to proceed as expected. If you make changes to code, those changes are saved when you save the form after you've exited the code screen. For macros, I believe you're prompted to save when exiting the macro, but do also save the form.

And for God's sake, Back UP, BACK UP, BACK UP!!!! Especially before poking around in the guts of a program you profess to know nothing about. You will thank yourself. There's several ways to do it, but I get very satisfactory results doing "copy" "paste" on the file itself from Windows explorer. Store the copy remotely (eg, server, if the main one is on your desktop, Desktop if the main one is on your server). Good luck. Access really is a great tool for us phillistines who never could be bothered to learn "real" programming. But troubleshooting the back end when all you know is the front end? Not fun at all.
posted by Ys at 7:26 PM on September 16, 2010

« Older Help me push my buttons.   |   How do I ask a girl to a school dance in Norwegian... Newer »
This thread is closed to new comments.