Populating fields

[bjr001]

Hello all,



I have an old database build by some else. Recently when I opened the Access DB I received some errors ( sorry I didn’t write the error down) and the Switchboard form came up blank expect a for single button that didn’t work. The tables are fine as are the quires. However, all the forms except the Switchboard are intact but not working. I attempted to modify one of the forms but was unable to save any changes. When I clicked on the Save at the Save Dialog Window, the Save Dialog window would close but the form would not close. I had to click on “No” in order to close the old form. I assume this form is corrupt.


I created another form as a test and I was able to save the form. I duplicated a form that I use to find records by entering a record number and have it populate the other fields. I created a Combo box and in the “Row Source” I used SELECT DISTINCTROW command it does what I want to. This is how the old form is setup. I then added a field from the “Add Existing Fielding” menu button to the form.


When I tested the form by I entering the record number into the Combo field it displays the appropriate info from the correct table in the Combo field. However, it does not populate the other fields. I duplicated the fields as the other form.

What I’m missing?


Original Access database created in Access 2003 under Windows XP.
Update to Windows 7 Ent, new PC hardware and Access 2010.


Thanks for your consideration.

[Rawb]

The first thing you should do is make a copy of the database. This will keep you from having to start completely over if something else goes wrong.

Next, try running a Compact and Repair on the Database (if it's a Split DB, be sure to wait until nobody's using it before you try and Compact the backend).

If that doesn't solve the problem, then I'd try re-compiling the database. From a command prompt, go to the Folder where you have Access installed and run the following command:

MSAccess.exe MyDB.mdb /decompile

Make sure you enter the full path to your database or you'll just get an error.

If neither of the above methods work, then there's most likely a section of the VBA Code that broke somehow. You'll need to open the Form in Design View and step through the Code until you find the error.

[bjr001]

Thanks for the information. Yes, I tried compacting and rebuilding the database but with no luck. I also made a backup before working on the database. A correction, in my original remark I should have said all of the forms are corrupted. The Switchboard is so far gone nothing shows up on it yet with the Switchboard manger I can see all of the Switchboards menus.

The forms as so damaged I was unable to change the name. I looked at the code and compared to an old copy. All of the VB code from the corrupted forms is gone. I would rather not bother using the old forms and just start with new ones.

I was able to partially achieve what I was asking in my original question. I used the [comboboxname].[column](1) expression in the text box field. When I select a record number in the combo box it populates the text box with the description of the record. However, the record numbers repeat. So, when I select any number that appears more then once the system will always return the information from the first record of the number I select.

Example, the number 46 appears 20 times in the DB though each entry has a unique description. No matter which of the 20 number 46 I select, it always returns the description for the first number 46 record.

Still need help.

[Rawb]

Honestly, that sounds like someone tried to Import the Forms from another db. When importing Forms (or any db Object) with VB code in it, the code gets removed and the internal Object name that the db uses to identify the Form gets messed up: it turns into a "~tmp" name and cannot be opened, deleted, or edited.

At this point, I'd agree that the only real option is to start over from scratch. Don't work on repairing the database as you'll end up with a TON of "leftovers" that could cause corruption and will unnecessarily bloat the db.

If you need to copy items from this db to another one, you can use the following guidelines to make sure that things get copied over correctly:

1. Always Import from the old db to the new one. NEVER Export.
2. If you're copying a Form, Report, or Module that has VB code in it, do the following:
   
   1. Make an empty copy of the item in your new db, using the exact same name.
   2. Import the item from the old db into the new one (it will show up as ObjectName1).
   3. Delete the empty copy you created and then rename the item to it's original name (remove the 1). DO NOT CLOSE THE DATABASE BEFORE PERFORMING THIS STEP!
   
   
3. Any Local and Linked Tables, Queries, or other items WITHOUT VB code can be Imported directly from the old db.
4. Compact/Repair the new db.
5. Close and re-open the db.
6. Try using one of the items with VB code in it: If you get an error, you'll need to scrap the whole db and try again from step 1.

Note: Following steps 2.1 - 2.3 will retain the VB code and functionality of your database Objects. It's a little clunky, but generally still far better than having to rebuild the Form/etc. from scratch.

[bjr001]

Thanks for your assistance in this matter. I was unable to make this one Form do what I set out to using VB so I used another way and now the Form does what I need it to do. Later when I have time I’ll look at this again. No, there were no imports done to this DB. I’m the only person that works on it including data entry, maintenance and modification. There is another person that accesses the DB to view files only.

Thanks again