changing subform source object

  • Thread starter Thread starter Pendragon
  • Start date Start date
P

Pendragon

Access03/WinXP

A form allows a sales rep to select a Trade Name and assign it to a specific
company. When a Trade Name needs to be added to the underlying source table,
an admin person has gone into the database window and opened the table to
enter in new information; the table is accessed by a label on the form using
a hyperlink. I'm trying to prevent this. This style of adding data is
prevalent in my client's application.

I've created a form, frmResults, which has various command buttons in the
form's header for the admin's needs. The detail section of frmResults is a
subform, frmResultsSub. frmResultsSub is set with an unbound Source Object.

Depending on where in the database a user is coming from, I would like to be
able to set the Source Object programmatically; all Source Objects are simple
queries on the relevant table. In the above example, the Source Object would
be qryTradeNames.

I initially thought above a public variable (strSource) but I don't know
enough about defining them and using them. Now from my reading on threads in
these Access newsgroups, I'm thinking this is not a good idea....???

The application always opens to a Main Switchboard, so if I understand
correctly, I would have "Public strSource as String" immediately under the
Options statement and before any Private Subs. strSource should be
initialized to "". How is this done?

Then, in frmTradeNames, when the user clicks on the label "Edit Trade
Names", in the OnClick property I would define strSource to be
"qryTradeNames".

In the OnOpen property of frmResults, the SourceObject property of the
subform frmResultsSub is set to strSource (whose value is qryTradeNames).

In the OnClose property of frmResults, strSource is reset to "".

Does this flow make sense? Is it appropriate? If so, I would appreciate
specifics on coding and placement of the code.

If not, suggestions are most welcome.

Thanks!
 
Pendragon said:
Access03/WinXP

A form allows a sales rep to select a Trade Name and assign it to a specific
company. When a Trade Name needs to be added to the underlying source table,
an admin person has gone into the database window and opened the table to
enter in new information; the table is accessed by a label on the form using
a hyperlink. I'm trying to prevent this. This style of adding data is
prevalent in my client's application.

I've created a form, frmResults, which has various command buttons in the
form's header for the admin's needs. The detail section of frmResults is a
subform, frmResultsSub. frmResultsSub is set with an unbound Source Object.

Depending on where in the database a user is coming from, I would like to be
able to set the Source Object programmatically; all Source Objects are simple
queries on the relevant table. In the above example, the Source Object would
be qryTradeNames.

I initially thought above a public variable (strSource) but I don't know
enough about defining them and using them. Now from my reading on threads in
these Access newsgroups, I'm thinking this is not a good idea....???

The application always opens to a Main Switchboard, so if I understand
correctly, I would have "Public strSource as String" immediately under the
Options statement and before any Private Subs. strSource should be
initialized to "". How is this done?

Then, in frmTradeNames, when the user clicks on the label "Edit Trade
Names", in the OnClick property I would define strSource to be
"qryTradeNames".

In the OnOpen property of frmResults, the SourceObject property of the
subform frmResultsSub is set to strSource (whose value is qryTradeNames).

In the OnClose property of frmResults, strSource is reset to "".
Click to expand...


There is no need to set the subform control's SourceObject
to "" when closing the form. As long you don't try to close
the form using acSaveYes, which is an exceedingly rare thing
to do, then it will retain it's original setting the next
time it is opened.

Unless there is more going on in this subform control than
you have explained, you don't need to set any variables
either. Just use:
Me.subformcontrol.SourceObject = "qryTradeNames"

If you want the subform to go blank at some point before the
main form is closed, then you can set SourceObject = ""
 
I understand what you have; however, I would like to use the same form and
subform for four or five different areas of the application and for each
area, the SourceObject of the subform will be a different query. Is there a
good way to determine from which area the form is being opened and thus
assign the appropriate source object?

Thanks.
 
Ahhh, that's different. See if I got this straight now,
another form opens frmResults and you need a way to tell
frmResults what to display in its sunform. Right?

If that's the question, I recommend that you use the
OpenForm method's OpenArgs argument. For example, the
button you use to add a trade name would use:

DoCmd.OpenForm frmResults, OpenArgs:="qryTradeNames"

Then, qryTradeNames Open event procedure would stuff it into
the subform control's SourceObject:

If Not IsNull(Me.OpenArgs) Then
Me.subformcontrol.SourceObject = Me.OpenArgs
End If
 
Outstanding! I had to add one element to your code though, as the first time
I put in your code I got an error message about not finding the object
"qryTradeNames". I added "Query." & me.OpenArgs to identify the source
object as a query and then it worked fine.

Thanks!!

Marshall Barton said:
Ahhh, that's different. See if I got this straight now,
another form opens frmResults and you need a way to tell
frmResults what to display in its sunform. Right?

If that's the question, I recommend that you use the
OpenForm method's OpenArgs argument. For example, the
button you use to add a trade name would use:

DoCmd.OpenForm frmResults, OpenArgs:="qryTradeNames"

Then, qryTradeNames Open event procedure would stuff it into
the subform control's SourceObject:

If Not IsNull(Me.OpenArgs) Then
Me.subformcontrol.SourceObject = Me.OpenArgs
End If
--
Marsh
MVP [MS Access]

I understand what you have; however, I would like to use the same form and
subform for four or five different areas of the application and for each
area, the SourceObject of the subform will be a different query. Is there a
good way to determine from which area the form is being opened and thus
assign the appropriate source object?
Click to expand...
Click to expand...
 
Back
Top