Change RecordSource

[jcethiopia]

Hi,

I was just curious if there was anyway to change permanently the recordsource for a form? Specifically, I used the CopyObject method to create a new form based off an existing form. Now I want the new form to have a new recordsource different from the recordsource of the copied form.

[Jim Doherty]

Hi,

I was just curious if there was anyway to change permanently the recordsource for a form? Specifically, I used the CopyObject method to create a new form based off an existing form. Now I want the new form to have a new recordsource different from the recordsource of the copied form.

After copying you could open in design change the recordsource and then save the form on close obviously amend this to suit your object names.

 
    DoCmd.CopyObject "", "YourNewformname", acForm, "CopiedFromFormName"
    DoCmd.OpenForm "YourNewFormName", acDesign, , , , acHidden
    Forms!YourNewFormName.RecordSource = "Select * from YourTableName"
    DoCmd.Close acForm, "YourNewFormName", acSaveYes
 

Jim :)

[jcethiopia]

After copying you could open in design change the recordsource and then save the form on close obviously amend this to suit your object names.

 
    DoCmd.CopyObject "", "YourNewformname", acForm, "CopiedFromFormName"
    DoCmd.OpenForm "YourNewFormName", acDesign, , , , acHidden
    Forms!YourNewFormName.RecordSource = "Select * from YourTableName"
    DoCmd.Close acForm, "YourNewFormName", acSaveYes
 

Jim :)

What if my newform name is dynamic, i.e. I use a variable to refer to the form? I always have trouble with the quotes and syntaxing. For example, how do I correctly do this command: Forms!YourNewFormName.Recordsource with a variable representing the form?

Thanks!

[Jim Doherty]

What if my newform name is dynamic, i.e. I use a variable to refer to the form? I always have trouble with the quotes and syntaxing. For example, how do I correctly do this command: Forms!YourNewFormName.Recordsource with a variable representing the form?

Thanks!

If we assume that you have just used the copyobject command to create your new form then it will exist as a named object, therefore the following code will open that form in design and instead of saving on close without prompt, it will show it to you with the amendment to the recordsource.

Obviously the form name here is called Form1 but you will know what the name of your form is you are copying into so just replace "Form1" here with the name of your newly created form. If this is not what you mean then post your code and lets see the context in which you are programming.

 Dim strFormName As String
strFormName = "Form1" 
Dim frm As Access.Form
DoCmd.OpenForm strFormName, acDesign
    Set frm = Forms!Form1
    ' Set form properties.
    With frm
        .RecordSource = "SELECT * FROM MyTable"
    End With
 

Jim :)

[jcethiopia]

If we assume that you have just used the copyobject command to create your new form then it will exist as a named object, therefore the following code will open that form in design and instead of saving on close without prompt, it will show it to you with the amendment to the recordsource.

Obviously the form name here is called Form1 but you will know what the name of your form is you are copying into so just replace "Form1" here with the name of your newly created form. If this is not what you mean then post your code and lets see the context in which you are programming.

 Dim strFormName As String
strFormName = "Form1" 
Dim frm As Access.Form
DoCmd.OpenForm strFormName, acDesign
    Set frm = Forms!Form1
    ' Set form properties.
    With frm
        .RecordSource = "SELECT * FROM MyTable"
    End With
 

Jim :)

I'm not sure I'm understanding you...Here is my code and what I want to do with it.

[code]
Dim NewName As String
Dim Concatnewfrm As String


NewName = Me.NewEquiplist.Value
Concatnewfrm = "frm_" & NewName

DoCmd.CopyObject , NewName, acTable, "Standard"
DoCmd.CopyObject , Concatnewfrm, acForm, "frm_Standard2"
[code]

Here is the context/situation:
On the working form, the user types in the name of a new list. The control where this is typed into is called NewEquiplist. Upon clicking a command button, I hope to create a new table based on the "Standard" table with the name the user just typed in. Then, I would like to create a form based on "frm_Standard2" but with its recordsource changed to the new table i just created.

Thanks

[Jim Doherty]

I'm not sure I'm understanding you...Here is my code and what I want to do with it.

[code]
Dim NewName As String
Dim Concatnewfrm As String


NewName = Me.NewEquiplist.Value
Concatnewfrm = "frm_" & NewName

DoCmd.CopyObject , NewName, acTable, "Standard"
DoCmd.CopyObject , Concatnewfrm, acForm, "frm_Standard2"
[code]

Here is the context/situation:
On the working form, the user types in the name of a new list. The control where this is typed into is called NewEquiplist. Upon clicking a command button, I hope to create a new table based on the "Standard" table with the name the user just typed in. Then, I would like to create a form based on "frm_Standard2" but with its recordsource changed to the new table i just created.

Thanks

OK the reason I asked for the context of this is because you mentioned referring to a form using a variable. I see that you are relatively new to Access so I assumed you might not know the differences technically. A variable has different meanings in different situations.

Now looking at your code I can see that yes....you are using a variable...but only in the sense that it is a string variable referring to the 'name' of the form rather than the form itself as an object reference. There is a subtle difference.

I have no idea what your overall design strategy is in doing what you are doing by copying existing objects into more objects I am curious as to the reasoning behind it.

Going with your strategy as it is:

When you do create extra objects you have to consider how you control that scenario. A simple command of 'Docmd Copyobject' does what it says on the tin so to speak...but..what happens if it doesnt, what happens if the object is already in place or if the user types invalid character into the textbox or too many perhaps. The program flow errors and so on.

You can try to perceive the obvious problems that might be encountered during a program flow trap for those and for those that you cannot ? ...well it errors out and you debug to track any causes etc.

The following code is a reshape of that which you have seen thus far. Now that I have seen your code context I can give you something back to chew over and maybe you might see things in there that are relevant to you. I have included in it error handling to sit within the on click event procedure of a control (command button) called command1

I have attached the code logic in a text file mainly because the messaging on here is truncating my replies to this thread tonight.

Regards

Jim :)

[jcethiopia]

OK the reason I asked for the context of this is because you mentioned referring to a form using a variable. I see that you are relatively new to Access so I assumed you might not know the differences technically. A variable has different meanings in different situations.

Now looking at your code I can see that yes....you are using a variable...but only in the sense that it is a string variable referring to the 'name' of the form rather than the form itself as an object reference. There is a subtle difference.

I have no idea what your overall design strategy is in doing what you are doing by copying existing objects into more objects I am curious as to the reasoning behind it.

Going with your strategy as it is:

When you do create extra objects you have to consider how you control that scenario. A simple command of 'Docmd Copyobject' does what it says on the tin so to speak...but..what happens if it doesnt, what happens if the object is already in place or if the user types invalid character into the textbox or too many perhaps. The program flow errors and so on.

You can try to perceive the obvious problems that might be encountered during a program flow trap for those and for those that you cannot ? ...well it errors out and you debug to track any causes etc.

The following code is a reshape of that which you have seen thus far. Now that I have seen your code context I can give you something back to chew over and maybe you might see things in there that are relevant to you. I have included in it error handling to sit within the on click event procedure of a control (command button) called command1

I have attached the code logic in a text file mainly because the messaging on here is truncating my replies to this thread tonight.

Regards

Jim :)

Thanks so much for your help, Jim. That was great.