Question DoCmd.Rename does not work in Runtime (1 Viewer)

MLUCKHAM · Jul 23, 2013

Hi
Not sure if anyone can shed any light on why I have this problem. I have a function that creates a form dynamically to enable the end use to edit the table (or query) contents.

For example, if I have a table called Activities. By calling this function it will create a form with the fields contained within this table. This form can then be viewed through a form (as a sub-form) in datasheet mode enabling the user to edit the details.

This approach works really well to view queries as well, especially cross tab queries where the column heading could be variable.

Anyway, I wrote it, tested it and it works fine as long as you are not using Access Runtime!

When it first failed when run under Runtime I thought it must have been the Application.CreateForm method, but it creates the forms fine, saves them, it is the Docmd.Rename method that does not work.

Here is the code: -

Code:

Public Function CreateDataModificationForm(ByVal ObjectName As String, ByVal ObjectType As String) As String
Dim Tbl As TableDef
Dim fld As Field
Dim ctl As Control
Dim frm As Form

    Set frm = CreateForm(, "__TemplateDataSetForm")
    'This template form is a blank form that is set to Datasheet Only view

    frm.RecordSource = ObjectName
    
    If ObjectType = "Table" Then
        For i = 0 To CurrentDb.TableDefs(ObjectName).Fields.Count - 1
    
           Set ctl = CreateControl(frm.Name, acTextBox, acDetail, , CurrentDb.TableDefs(ObjectName).Fields(i).Name)
            ctl.Name = CurrentDb.TableDefs(ObjectName).Fields(i).Name
        Next i
    Else
        For i = 0 To CurrentDb.QueryDefs(ObjectName).Fields.Count - 1
    
           Set ctl = CreateControl(frm.Name, acTextBox, acDetail, , CurrentDb.QueryDefs(ObjectName).Fields(i).Name)
            ctl.Name = CurrentDb.QueryDefs(ObjectName).Fields(i).Name
        Next i
    End If
    
    CreateDataModificationForm = frm.Name
    DoCmd.Close acForm, frm.Name, acSaveYes

End Function

You call this function as so: -

Code:

frmName = CreateDataModificationForm(TableName, "Table")

The frmName returned is whatever Application.CreateForm has chosen. This is normally Form1 (or Form2 if Form1 exists etc).

I then rename the form to something more valid: -

Code:

DoCmd.Rename "frmAdminDataSetModificationSub", acForm, frmName

No error is generated, it just does not do it when in RunTime. The error is generated when I try and reference the form: -

Code:

Sub1.SourceObject = "frmAdminDataSetModificationSub"

As the rename has failed, the form frmAdminDataSetModificationSub does not exist and so setting the subform.sourceObject to this is invalid.

Has anyone got any ideas as to why DoCmd.Rename does not work until RunTime?

gemma-the-husky · Jul 23, 2013

i presume you cannot use runtime, because in runtime you cannot maintain the database objects.

MLUCKHAM · Jul 23, 2013

I thought that initially to, but when I tested it the Application.CreateForm works. The Docmd.DeleteObject works. The Application.CreateControl works. All of which are database objects that are being created or destroyed within the RunTime environment.

Why the Docmd.Rename command does not work I have no idea.

Anyway, I have come up with a work around. If I just accept that the new form is called "Form1" and use that as the source for the Sub Form then it will work. It would cause me issues if I shared the database with anyone, but I don't so I think it is going to be OK.

Thanks for your reply.

nanscombe · Jul 23, 2013

How many fields are talking about typically?

Would a continuous form be of any use?

I have attached a demo which will dynamically allow you to choose a table and reconfigure the text boxes and labels.

It only contain 7 columns but does work with the Access Runtime. I've also just tested it as a subform.

I basically start off with 7 sets of hidden textboxes and labels then make them visible, and set their control sources and captions, as required.

MLUCKHAM · Jul 23, 2013

I like your database, however it does put a limitation on the number of fields. I have come up with a work around that does work in Runtime, but the form created cannot be renamed.

It is not the end of the world for me, but for anyone else who create a form at runtime and wants the user to be able to store it for reuse then it would be a problem.

nanscombe · Jul 23, 2013

How many columns were you thinking of needing?

MLUCKHAM · Jul 23, 2013

The issue is the number of columns really could be n. For example, I have a cross tab query that converts contents in to table column headings. Using my earlier function it means that regardless of how many items are in this column the form is generated and it works. Normally it is running at about 10 to 15, however it could be 100. I would never advise the users go to this level, my point is that the software currently will support it.

I have in the past used hard coded fields on forms and used them. It works, but it always comes with baggage. Often it bloats your database as you end up with a form that has 100 fields and 100 labels to manage the "occasional heavy user", but with the majority of times you only need 10 to 15.

Try my function above and see what you think. The template form called __TemplateDataSetForm is just a blank form with the DefaultView set to Datasheet. Using the function you can dynamically view and edit any size tables or queries.

Let me know what you think...

nanscombe · Jul 23, 2013

Docmd.Rename doesn't work in the Runtime environment. :banghead:

However, DoCmd.CopyObject and DoCmd.DeleteObject appear to.

Time to get sneaky.

Create your new form. (Form1)

Make a copy with a new name (myForm1)

Delete originally created form. (Form1)

If at first you don't succeed ... Cheat!

nanscombe · Jul 23, 2013

You might just be able to modify your code like this ...

Code:

Public Function CreateDataModificationForm([COLOR="Red"]byVal NewFormName as String,[/COLOR] ByVal ObjectName As String, ByVal ObjectType As String) As String
Dim Tbl As TableDef
Dim fld As Field
Dim ctl As Control
Dim frm As Form

    Set frm = CreateForm(, "__TemplateDataSetForm")
    'This template form is a blank form that is set to Datasheet Only view

    frm.RecordSource = ObjectName
    
    If ObjectType = "Table" Then
        For i = 0 To CurrentDb.TableDefs(ObjectName).Fields.Count - 1
    
           Set ctl = CreateControl(frm.Name, acTextBox, acDetail, , CurrentDb.TableDefs(ObjectName).Fields(i).Name)
            ctl.Name = CurrentDb.TableDefs(ObjectName).Fields(i).Name
        Next i
    Else
        For i = 0 To CurrentDb.QueryDefs(ObjectName).Fields.Count - 1
    
           Set ctl = CreateControl(frm.Name, acTextBox, acDetail, , CurrentDb.QueryDefs(ObjectName).Fields(i).Name)
            ctl.Name = CurrentDb.QueryDefs(ObjectName).Fields(i).Name
        Next i
    End If
    
    CreateDataModificationForm = frm.Name
    DoCmd.Close acForm, frm.Name, acSaveYes

    [COLOR="red"]Docmd.CopyObject "", NewFormName, acForm, CreateDataModificationForm
    DoCmd.DeleteObject acForm, CreateDataModificationForm[/COLOR]

End Function

MLUCKHAM · Jul 23, 2013

I like this! I will give this a go tonight... Thanks for the lateral thinking!

Question DoCmd.Rename does not work in Runtime (1 Viewer)

MLUCKHAM

Registered User.

gemma-the-husky

Super Moderator

MLUCKHAM

Registered User.

nanscombe

Registered User.

Attachments

MLUCKHAM

Registered User.

nanscombe

Registered User.

MLUCKHAM

Registered User.

nanscombe

Registered User.

Attachments

nanscombe

Registered User.

MLUCKHAM

Registered User.

Similar threads

Users who are viewing this thread