open form with a different recordsource

[RLN]

RE: Access 2003

I have a SQL variable (strSQL1 dimmed as String) that gets built using
Select Case logic. The first half of strSQL1 is etched in stone no
matter what.
The 2nd half of strSQL1 (the condition) is built depending on which
option button is chosen:
For option 1, I add "Condition X" to the end of strSQL1.
For option 2, I add "Condition Y" to the end of strSQL1...etc.

When the Select Case logic is completed strSQL1 is built and I then need
to launch a form in Datasheet mode using strSQL1 as my data source.

Can this be done?
*** Sent via Developersdex http://www.developersdex.com ***

[Salad]

RLN wrote:

RE: Access 2003

I have a SQL variable (strSQL1 dimmed as String) that gets built using
Select Case logic. The first half of strSQL1 is etched in stone no
matter what.
The 2nd half of strSQL1 (the condition) is built depending on which
option button is chosen:
For option 1, I add "Condition X" to the end of strSQL1.
For option 2, I add "Condition Y" to the end of strSQL1...etc.

When the Select Case logic is completed strSQL1 is built and I then need
to launch a form in Datasheet mode using strSQL1 as my data source.

Can this be done?
*** Sent via Developersdex http://www.developersdex.com ***

I suppose you could launch and change the source.
DoCmd.OpenForm "FormName", , , , , , strSQL1

Now in the OnOpen even you could do
If Not IsNull(me.opena rgs) then Me.Recordsource = Me.OpenArgs

But I don't see the necessity because the table info isn't
changing...just the filter. Let's say you simply make strSQL1 the
conditional then pass the filter. Ex:
strSQL1 = "customerid = 123".
DoCmd.OpenForm "FormName", , , strSQL1
and this will open it to filtered records where customerid = 123

Once in a form, you could do things like this
Me.Filter = "CustomerID = 123"
Me.FilterOn = True

Powerful
http://www.youtube.com/watch?v=YhrLBrDzj5Q

[RLN]

ok...let me add one bit of mere complexity to this if I may.

The form that calls this query is chock full of text boxes and I cannot
afford realestate on the form to put yet another generic text box for
parameter input.
Is there a way to pop an input box on the dcmd.openf rom statment so the
user can input data for this query (much like what happens when a query
is run as standalone)?

thnkx.......
*** Sent via Developersdex http://www.developersdex.com ***

[Salad]

RLN wrote:

>
ok...let me add one bit of mere complexity to this if I may.

The form that calls this query is chock full of text boxes and I cannot
afford realestate on the form to put yet another generic text box for
parameter input.
Is there a way to pop an input box on the dcmd.openf rom statment so the
user can input data for this query (much like what happens when a query
is run as standalone)?

thnkx.......

That doesn't make sense. You have 2 checkboxes that determines the SQL
string for creating a filter. The recordsource of your second form
won't change, simply the criteria. Are you saying you have no command
button or use the checkbox for launching the second form? How do you
open the form?

You should be able to open a form and set the filter there if you want.

You can also open the form with the filter passed to it.

The only reason you need to change the recordsource of the second form
is if you are using other tables.

>
*** Sent via Developersdex http://www.developersdex.com ***

[RLN]

>>You can also open the form with the filter passed to it.

<<

You mean passed in as a parm?
*** Sent via Developersdex http://www.developersdex.com ***