set Form properties if Form is not open

[kokostik]

I did a few searches, but couldn't come up with a clear answer to my
question. So here it is: If I am in an open form (MyForm1), is it
possible to set the RecordSource of an un-opened form (MyForm2). I
want to use the OnClose Event of MyForm1. But I do not want to have
the MyForm2 open when this happens. My wish it to have the
RecordSource of MyForm2 permanently changed by the OnClose Event of
MyForm1. Can this be done ?

Thanks for any suggestions, Scott

[salad]

ko******@yahoo.com wrote:

I did a few searches, but couldn't come up with a clear answer to my
question. So here it is: If I am in an open form (MyForm1), is it
possible to set the RecordSource of an un-opened form (MyForm2). I
want to use the OnClose Event of MyForm1. But I do not want to have
the MyForm2 open when this happens. My wish it to have the
RecordSource of MyForm2 permanently changed by the OnClose Event of
MyForm1. Can this be done ?

Thanks for any suggestions, Scott

A couple of options.

Docmd.Openform "FormName"
Forms!FormName.Form.Recordsource = whatever

You could also open the form in design mode, hidden. Modify the
recordsource, and close/save it. Then open it.

[Anthony England]

<ko******@yahoo.com> wrote in message
news:11**********************@g10g2000cwb.googlegr oups.com...

I did a few searches, but couldn't come up with a clear answer to my
question. So here it is: If I am in an open form (MyForm1), is it
possible to set the RecordSource of an un-opened form (MyForm2). I
want to use the OnClose Event of MyForm1. But I do not want to have
the MyForm2 open when this happens. My wish it to have the
RecordSource of MyForm2 permanently changed by the OnClose Event of
MyForm1. Can this be done ?

Thanks for any suggestions, Scott

I would not do this by altering the design view. I would store this string
as some form of setting - perhaps in a settings table or perhaps as a
database property or even a registry entry. Then each time the form opened,
it would look up it's value and set the recordsource accordingly.
To the end user, this would look like the design had changed each time the
form was closed, it would just be implemented differently.

[kokostik]

Wow ! That was quick. Thanks, I will try that. ~Scott

[Larry Linson]

No, you can't do what you want.

Salad has given you some alternatives.

Another would be to write the RecordSource to a Table, and have MyForm2 open
the Table from code in its Open event and reset its own RecordSource. That
may not work so well, depending on the details of your application, if you
have multiple users (though there are probably workarounds for most of those
potential problems, too.)

Another approach would be to use the name of a Query as the Record Source,
and instead of changing the RecordSource, replace the SQL property of that
Query. That, effectively, permanently changes the RecordSource without ever
opening the Form, either in Form or Design View.

What kind of changes are you making to the RecordSource? Generally the only
kind that make sense are those selecting or llimiting which Records are to
be processed. Certainly, for a bound Form to work, both the old and new
RecordSources would have to have Fields with the same names.

The only way to permanently change the RecordSource is to do it with the
Form open in Design View (but could certainly be hidden) then close the
Form.

Larry Linson
Microsoft Access MVP

<ko******@yahoo.com> wrote in message
news:11**********************@g10g2000cwb.googlegr oups.com...

I did a few searches, but couldn't come up with a clear answer to my
question. So here it is: If I am in an open form (MyForm1), is it
possible to set the RecordSource of an un-opened form (MyForm2). I
want to use the OnClose Event of MyForm1. But I do not want to have
the MyForm2 open when this happens. My wish it to have the
RecordSource of MyForm2 permanently changed by the OnClose Event of
MyForm1. Can this be done ?

Thanks for any suggestions, Scott

[kokostik]

I like the option of opening the form in Design view something like
this:
DoCmd.OpenForm MyForm1, , , , , acHidden
Forms!MyForms.Form.RecordSource = "newQry"
DoCmd.Close acForm, MyForm1, acSaveYes
Would that work ?
The recordsource is a Query that limits a users view of previous year's
data. I suppose I could change the SQL property for the query, but not
sure how to make that change permanent either.

~Scott

[Anthony England]

<ko******@yahoo.com> wrote in message
news:11**********************@e56g2000cwe.googlegr oups.com...

I like the option of opening the form in Design view something like
this:
DoCmd.OpenForm MyForm1, , , , , acHidden
Forms!MyForms.Form.RecordSource = "newQry"
DoCmd.Close acForm, MyForm1, acSaveYes
Would that work ?
The recordsource is a Query that limits a users view of previous year's
data. I suppose I could change the SQL property for the query, but not
sure how to make that change permanent either.

~Scott

If you used the function below, you could write something like:

If Not DefineQuery("MyQuery","SELECT * FROM MyTable WHERE MyYear<2006") Then
Beep
End If

So you always have the same query, named MyQuery, you just change its
definition.
Public Function DefineQuery(strQuery As String, _
strSQL As String) As Boolean

On Error GoTo Err_Handler

Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef

Set dbs = CurrentDb

Set qdf = dbs.QueryDefs(strQuery)

qdf.SQL = strSQL

DefineQuery = True

Exit_Handler:

If Not qdf Is Nothing Then
Set qdf = Nothing
End If

If Not dbs Is Nothing Then
Set dbs = Nothing
End If

Exit Function

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Function

[Larry Linson]

I don't recall ever using acHidden, but I have, at times used
DoCmd.Echo False before my code to freeze the display, run the code, and
DoCmd.Echo True after the code to put things back to normal.

If you do that, be very, very sure that the code runs without error.
Otherwise the screen won't be responding and you will have the Devil of a
time getting back to normal. When I use it, I run and test and test and test
without setting Echo off -- and still, sometimes, I worry.

Larry Linson
Microsoft Access MVP
Larry Linson
Microsoft Access MVP
<ko******@yahoo.com> wrote in message
news:11**********************@e56g2000cwe.googlegr oups.com...

I like the option of opening the form in Design view something like
this:
DoCmd.OpenForm MyForm1, , , , , acHidden
Forms!MyForms.Form.RecordSource = "newQry"
DoCmd.Close acForm, MyForm1, acSaveYes
Would that work ?
The recordsource is a Query that limits a users view of previous year's
data. I suppose I could change the SQL property for the query, but not
sure how to make that change permanent either.

~Scott

[DFS]

Larry Linson wrote:

I don't recall ever using acHidden, but I have, at times used
DoCmd.Echo False before my code to freeze the display, run the code,
and DoCmd.Echo True after the code to put things back to normal.

If you do that, be very, very sure that the code runs without error.
Otherwise the screen won't be responding and you will have the Devil
of a time getting back to normal. When I use it, I run and test and
test and test without setting Echo off -- and still, sometimes, I
worry.
No need to worry. Just add Application.Echo True into the error handling.

Larry Linson
Microsoft Access MVP
Larry Linson
Microsoft Access MVP
<ko******@yahoo.com> wrote in message
news:11**********************@e56g2000cwe.googlegr oups.com...

I like the option of opening the form in Design view something like
this:
DoCmd.OpenForm MyForm1, , , , , acHidden
Forms!MyForms.Form.RecordSource = "newQry"
DoCmd.Close acForm, MyForm1, acSaveYes
Would that work ?
The recordsource is a Query that limits a users view of previous
year's data. I suppose I could change the SQL property for the
query, but not sure how to make that change permanent either.

~Scott

[Larry Linson]

<ko******@yahoo.com> wrote in message
news:11**********************@e56g2000cwe.googlegr oups.com...

I like the option of opening the form in Design view something like
this:
DoCmd.OpenForm MyForm1, , , , , acHidden
Forms!MyForms.Form.RecordSource = "newQry"
DoCmd.Close acForm, MyForm1, acSaveYes
Would that work ?
The recordsource is a Query that limits a users view of previous year's
data. I suppose I could change the SQL property for the query, but not
sure how to make that change permanent either.

~Scott

If you change the SQL property of a Query at runtime, it stays changed
(unlike changes to the design of a Form). Just to make certain my memory
wasn't playing any tricks on me, I just tried and verified that.

What, particularly, determines the year you want displayed? Is it always the
year prior to the current year, or is it always the current year, or do you
allow the user to specify it somehow?

Larry Linson
Microsoft Access MVP
Larry Linson
Microsoft Access MVP

[Larry Linson]

"DFS" <nospam@dfs_.com> wrote

No need to worry. Just add Application.Echo True
into the error handling.

Yes, that is the solution, but as I sit there and the code takes longer than
I remembered it taking, I wonder, "Hmm. Did I add it?" And, even more to the
point, "Did something happen so my code is in an unending loop?"

Larry Linson
Microsoft Access MVP

[salad]

ko******@yahoo.com wrote:

I like the option of opening the form in Design view something like
this:
DoCmd.OpenForm MyForm1, , , , , acHidden
Forms!MyForms.Form.RecordSource = "newQry"
DoCmd.Close acForm, MyForm1, acSaveYes
Would that work ?
The recordsource is a Query that limits a users view of previous year's
data. I suppose I could change the SQL property for the query, but not
sure how to make that change permanent either.

~Scott

I normally don't change the recordsource except in specific situations.
Here is what I do. I open the form and pass an argument.
DoCmd.OpenForm "SomeForm", , , , , , "Test"

In the OnOpen event for the form you can check the argument. Let's say
50%+ of the time you are going to use the recordset used when the form
was designed. Then you could check for arguments and update the
recordsource. Let's say the recordsource is currently Query1. You
could do something like this if "Test" is passed.
If Me.OpenArgs = "Test" Then Me.Recordsource = Query2
This changes the recordsource only if the argument is Test.

For whatever reason...I prefer setting/changing my recordsource in the form.

[kokostik]

Thanks for all the suggestions. I wish my Access knowledge was
sufficient to figure out how to make this easy. But I am still
struggling. I have the following code behind a button that I can't get
to work. It hangs up on the Forms! argument, giving me this error:
"Type declaration character does not match declared data type."

'>>> Code behind On-Click button of Form1
Dim MyForm As String, MySource As String
Dim MyEdit As Boolean, MyDelete As Boolean

MyTarget = chr(34) & Me.FormName & chr(34)
MySource = Me.DataSource
MyEdit = Me.EditOption
MyDelete = Me.Add_Delete

DoCmd.OpenForm MyForm, , , , , acHidden
Forms!(MyTarget).Form.RecordSource = MySource
Forms!(MyTarget).Form.AllowEdits = MyEdit
Forms!(MyTarget).Form.AllowAdditions = MyDelete
Forms!(MyTarget).Form.AllowDeletions = MyDelete
DoCmd.Close acForm, MyForm, acSaveYes

My fear is that I am way off base with this approach. Any thoughts ?
Should I be declaring MyTarget as a Form object?
-Scott

[Lyle Fairfield]

ko******@yahoo.com wrote in news:1143486125.365016.79760
@u72g2000cwu.googlegroups.com:

Thanks for all the suggestions. I wish my Access knowledge was
sufficient to figure out how to make this easy. But I am still
struggling. I have the following code behind a button that I can't get
to work. It hangs up on the Forms! argument, giving me this error:
"Type declaration character does not match declared data type."

'>>> Code behind On-Click button of Form1
Dim MyForm As String, MySource As String
Dim MyEdit As Boolean, MyDelete As Boolean

MyTarget = chr(34) & Me.FormName & chr(34)
MySource = Me.DataSource
MyEdit = Me.EditOption
MyDelete = Me.Add_Delete

DoCmd.OpenForm MyForm, , , , , acHidden
Forms!(MyTarget).Form.RecordSource = MySource
Forms!(MyTarget).Form.AllowEdits = MyEdit
Forms!(MyTarget).Form.AllowAdditions = MyDelete
Forms!(MyTarget).Form.AllowDeletions = MyDelete
DoCmd.Close acForm, MyForm, acSaveYes

My fear is that I am way off base with this approach. Any thoughts ?
Should I be declaring MyTarget as a Form object?
-Scott

I haven't used this really clumsy Forms!(SomeFormName) for many years and
have no idea why it's recommended by seemingly all. Regardless, if MyTarget
is a string, as it seems to be, then the chr(34)'s in
MyTarget = chr(34) & Me.FormName & chr(34)
are redundant.

--
Lyle Fairfield

[kokostik]

I have tried it with and without the forced quotation marks, but I get
the same error. -Scott

[kokostik]

Any other thoughts on this ?
I have the following code behind a button that I can't get to work. It
hangs up on the Forms! argument, giving me this error:
"Type declaration character does not match declared data type."

'>>> Code behind On-Click button of Form1
Dim MyForm As String, MySource As String
Dim MyEdit As Boolean, MyDelete As Boolean
MyTarget = Me.FormName
MySource = Me.DataSource
MyEdit = Me.EditOption
MyDelete = Me.Add_Delete
DoCmd.OpenForm MyForm, , , , , acHidden
Forms!(MyTarget).Form.RecordSource = MySource
Forms!(MyTarget).Form.AllowEdits = MyEdit
Forms!(MyTarget).Form.AllowAdditions = MyDelete
Forms!(MyTarget).Form.AllowDeletions = MyDelete
DoCmd.Close acForm, MyForm, acSaveYes

Should I be declaring MyTarget as a Form object? Is there a more
elegant way to get this to work ?
-Scott