RecordSource on form

[DS]

How do you set the record source on a form from another form using SQL?
Thanks
DS

 

[Wayne Morgan]

The form you want to change the Record Source on will need to be open. You
would then use a command such as:

Forms!FormName.RecordSource = "SELECT * FROM Table1 ORDER BY Field1;"

This won't still be there if you close and reopen the form. To make it
"stick", you'll have to do this with the form open in design view then close
the form, saving the changes.

Example:
DoCmd.OpenForm "Form1", acDesign,,,, acHidden
Forms!Form1.RecordSource = "SELECT * FROM Table1 ORDER BY Field1;"
DoCmd.Close acForm, "Form1", acSaveYes

 

[DS]

The form you want to change the Record Source on will need to be open. You
would then use a command such as:

Forms!FormName.RecordSource = "SELECT * FROM Table1 ORDER BY Field1;"

This won't still be there if you close and reopen the form. To make it
"stick", you'll have to do this with the form open in design view then close
the form, saving the changes.

Example:
DoCmd.OpenForm "Form1", acDesign,,,, acHidden
Forms!Form1.RecordSource = "SELECT * FROM Table1 ORDER BY Field1;"
DoCmd.Close acForm, "Form1", acSaveYes

When you say make it stick, that meansI'll be able to add fields then.
Cause now it works but when I drop the field list down, nothing is
listed. So everytime, I'll have to open it in design view, save it,
close it and then reopen it? Any down side to this? I'm trying to
avoid using this form over 6 times beccause 1 field needs to be changed.
Thanks
DS

 

[Wayne Morgan]

If only one field needs to be changed, you could also change the control
source of the textbox the same way you change the record source of the form.
You may also be able to just hide the controls you don't need at any
particular time.

By "sticking" I mean that it will still be there after you close the form.
If you change it while the form is open in normal mode, the change will
occur but be lost as soon as the form is closed. To save the change, the
form needs to be open in design mode. If you just need the change for what
you're currently doing, then it probably doesn't need to be saved. Just make
the change when you need it. You could do it when you open the form. Open
the form in hidden mode, set the Record Source, then make the form visible.

 

[DS]

If only one field needs to be changed, you could also change the control
source of the textbox the same way you change the record source of the form.
You may also be able to just hide the controls you don't need at any
particular time.

By "sticking" I mean that it will still be there after you close the form.
If you change it while the form is open in normal mode, the change will
occur but be lost as soon as the form is closed. To save the change, the
form needs to be open in design mode. If you just need the change for what
you're currently doing, then it probably doesn't need to be saved. Just make
the change when you need it. You could do it when you open the form. Open
the form in hidden mode, set the Record Source, then make the form visible.

Thats what I ended up doing. Thanks
DS