set sourceObject property to nothing problems

[kjuhasz via AccessMonster.com]

Hello.

I am using MS Access 97 at work and trying to do the following...
I have a main form which has several combo boxes, 2 buttons (search and
refresh) and an unbound subform on it. This form acts like a search screen,
searching thru a bunch of records using the combo selections as filters. The
search runs when the user makes some selections and then clicks the search
button.

I decided to use the unbound subform for performance reasons. The
sourceobject of the subform is set on the search click event, after the SQL
is assembled that drives the subform.
This all works fine.However...

when I am trying to reset the sourceobject to nothing to clear the screen
(refresh button clears all combo selections and should blank out subform
entirely to prep for another search)
I am getting errors.I am trying to do all this on the click event of the
refresh button which is a control of the search screen so I am referencing
the subform control as

me.mysubformname.sourceobject=nothing

I get an error using the above syntax. I also tried
me.mysubformname.sourceobject=null or
me.mysubformname.sourceobject=""

I also tried closing the referenced subform before setting the sourceobkect,
with no success.
The task is simple, I need to clear the subform when the user clicks
'refresh'. Could anyone let me know why this is not working?

Thanks

 

[Allen Browne]

The SourceObject accepts a string value, so setting it to "" should work.

An alternative strategy is to change the RecordSource so that the search
returns no records when there is no match. For example:
Me.RecordSource = "SELECT * FROM Customers WHERE (False);"

 

[Klatuu]

I just tested this on a form I use for some imported data validaton that uses
5 different forms as sourceobjects for the same subform control. The
Me.fsubTableEdits.SoureObject = "" worked

 

[kjuhasz via AccessMonster.com]

Thanks for the reply. Setting the RecordSource is to return nothing is a
great idea. I may resort to this method. I think I am getting the error b/c I
am trying to set the SourceObject to nothing while the main form is open. The
error I am getting is:

2197

You can't set a subform control's SourceObject property to a zero length
string if you are displaying the main form in form view.

The SourceObject accepts a string value, so setting it to "" should work.

An alternative strategy is to change the RecordSource so that the search
returns no records when there is no match. For example:
Me.RecordSource = "SELECT * FROM Customers WHERE (False);"

Hello.

[quoted text clipped - 31 lines]

The task is simple, I need to clear the subform when the user clicks
'refresh'. Could anyone let me know why this is not working?

 

[kjuhasz via AccessMonster.com]

The actual line of code is...


Private Sub btn_REFRESH_Click()
'clear combo selections
Call bas_ComboUtilities.ClearCombos
'clear embedded subform
Me.sbfrmSearchData.SourceObject = ""
Me.optStatusSelection = 2 'reset option selection to ACTIVE (2)
End Sub