Deleting Forms with VBA

[sphinney]

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!)

Context:
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:

 DoCmd.CopyObject , TemplateForm_Name, acForm, SubForm_Name

Question:
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.

sphinney

[ChipR]

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.

[sphinney]

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.

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?

Thanks,
sphinney

[ChipR]

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.

Dim db As Object
Dim tdf As Object
    Set db = CurrentDb
    For Each tdf In db.TableDefs
        If Len(tdf.Connect) > 0 Then
            tdf.Connect = ";DATABASE=" & strPath
            Err = 0
            On Error Resume Next
            tdf.RefreshLink ' Relink the table.
            If Err <> 0 Then
                ReLink = False
                Exit Function
            End If
        End If
    Next tdf
    db.Close
    Set db = Nothing
    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.

[sphinney]

Interesting. I'll give it a try. Thanks for the help!

sphinney