469,590 Members | 2,500 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,590 developers. It's quick & easy.

Deleting Forms with VBA

I'm not exactly sure how to start this post. My question is pretty simple, but it will take a little bit of context before I can state it. (And thanks in advance for taking the time to read this!)

What I'm essentially trying to do is create a poor man's document imaging system in Access 2007. I have a database that contains forms and modules (but no data) that I'm going to distribute to 200+ users in my company that have limited Access knowledge.

The database will allow users to do the following:
  • View a list of 20 or so different Access databases/tables (each with a unique set of field names and types) that are maintained by various deparments in my company
  • Select a desired table
  • Search the selected table for a desired record
  • Lauch the appropriate application to view the document referenced by the selected record
The code that I've employed to accomplish this has not been very stable. When a user selects a table to work with, my code copies that table (from wherever it lives on my company's network) into the user's database. The code then makes a copy of a datasheet style form that I use as a template. The template form contains no controls but does have some code in the 'OnCurrent' event.

Once the template form is copied, textboxes are automatically added to the new form for each field in the newly copied table. The new form is then displayed as a subform on the 'main' form. The 'main' form contains various controls that facilitate searching the subform for a desired record.

Once the user is finished (and as the user's database closes) the copied table and subform are deleted.

What I've found is that Access is pretty good about creating and deleting tables via code. However, Access is NOT good about creating and deleting forms via code. In testing, after only a couple of uses, the line of code that copies the template form bombs out. It throughs a "2501 - The CopyObject action was canceled" error.

It appears that after creating and deleting the subform a couple times, even though the old subform no longer appears in the Naviagor Window, remnants of the form still exist somewhere in the database. When the code tries to copy the template form into a new subform, Access finds the remains of the old subform and cancels the copy action.

The offending line of code is:
Expand|Select|Wrap|Line Numbers
  1.  DoCmd.CopyObject , TemplateForm_Name, acForm, SubForm_Name
Can anyone think of a better way to go about this than creating and destorying subforms?
Bonus Question:
If not, is there a way to create datasheet style subforms without using templates?

I hope all this makes sense. Thanks for any suggestions you may have.

Feb 2 '09 #1
4 2045
1,287 Expert 1GB
Why not distribute the forms and subforms in a front-end, and create a link to a back-end db at runtime to the table they have chosen? Then you can just delete the table/link at the end and the data is gone.
Feb 2 '09 #2

ChipR - Thanks for the comment! Could you share your thoughts on how to link the back-end db to the subform. Are you thinking of creating and deleting controls on the subform (i.e. textboxes) when the user selects a back-end db to use?

Feb 2 '09 #3
1,287 Expert 1GB
I think I would make as many forms as necessary to cover all the display options, and display the appropriate one once the user makes a selection and the data has been linked. I have made some controls visible or not, but I never create or delete controls because I force the Runtime enviroment, so I don't think it's possible. Linking is pretty easy.

Expand|Select|Wrap|Line Numbers
  1. Dim db As Object
  2. Dim tdf As Object
  3.     Set db = CurrentDb
  4.     For Each tdf In db.TableDefs
  5.         If Len(tdf.Connect) > 0 Then
  6.             tdf.Connect = ";DATABASE=" & strPath
  7.             Err = 0
  8.             On Error Resume Next
  9.             tdf.RefreshLink ' Relink the table.
  10.             If Err <> 0 Then
  11.                 ReLink = False
  12.                 Exit Function
  13.             End If
  14.         End If
  15.     Next tdf
  16.     db.Close
  17.     Set db = Nothing
  18.     Relink = True
That code will try to link all your tables to the backend at the specified path, but you could just link the table that you need. Your form has that table as a recordsource, so your form doesn't need to change, just the path to the data.
Feb 2 '09 #4
Interesting. I'll give it a try. Thanks for the help!

Feb 2 '09 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Nathan Bloom | last post: by
3 posts views Thread by meh | last post: by
2 posts views Thread by Yuk Tang | last post: by
46 posts views Thread by DP | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.