Programmatically Set recordsource of form

[brainstorms]

Hello,

I have one form, and would like it be opened with a different recordsource according to a selection by users via another form.

This code works fine to open the form, with the form recordsource being the query "qclosed":

' openforms_allclosedadult
Function openforms_allclosedadult()
On Error GoTo openforms_allclosedadult_Err
DoCmd.OpenForm "cases", acNormal, "", "[qclosed]![age]=""A""And [qcclosed]![adv] = 0 ", , acNormal
Forms!Cases.nowshowing.Visible = True
Forms!Cases.nowshowing.Caption = "Records selected: Adult, All, Closed, Excl. Advocacy"
openforms_allclosedadult_Exit:
Exit Function
openforms_allclosedadult_Err:
MsgBox Error$
Resume openforms_allclosedadult_Exit
End Function

How can I change code this to force the form to use a different query?

Any help much appreciated with this urgent problem!

 

[randy700]

Use an if/then or select statement....
If UserSelection = 1 then
code to open form
else
code to open form with different query
end if

or

select case UserSelection
case 1:
code to open form
case 2:
code using different query
end select


Randy

 

[Gazonice]

Hi,

Here is a quick example of how I use selectable SQL as a source for a form.

Dim sSQL As String
sSQL = "SELECT qry_history.* FROM qry_history WHERE ((........."
Me.subfrm_6_history.Form.RecordSource = sSQL

By adding different SQL strings I can alter the information that the form displays at the press of a button.

Regards,

Garry

 

[TheAceMan1]

How are ya brainstorms . . . . .

Base the form on a query with criteria based on the other form. This way a simple [blue]Me.Requery[/blue] does the job.

See Ya! . . . . . .

 

[brainstorms]

Many thanks for the suggestions - I'll try them out and report back.
TheAceMan1, there is only one form, that I want to open based on either the 'qclosed' query (as in the supoplied code), or another query. Not sure how your solution does this. The form is based on qclosed, but sometimes I want it based on , for example, qopen, but with the same criteria as the code supplied.

 

[brainstorms]

TheAceman1, sorry down the wrong track there. DOH!

I had wondered if a simple 'Forms!Cases.recordsource="alternative query" in the first part of the code would do the trick, but it doesn't do anything.

Thanks again.

 

[RoyVidar]

To use the same query with different criterias, I'd use something like your initial approach, except I'd dropped off the extra quotes:

[tt]DoCmd.OpenForm "cases", acNormal, , "[qclosed]![age]='A' And [qcclosed]![adv] = 0 "[/tt]

just build different criteria strings:

[tt]dim strcrit as string
Select case <some condition>
case 1
strcrit = "fld1 = 2 and fld2 = 3"
case 2
strcrit = "fld3 = 4 and fld4 = 6"
'...
end select
docmd.openform "cases",,,strcrit[/tt]

But to change recordsource, I'd rather do this within the "cases" form (pulling), than pushing from the calling form, using the on open event of the form.

Think I'd passed the relevant info in the openargs argument of the openform method, for instance something like this (simplified sample...)

[tt]DoCmd.OpenForm "cases",,,,,,1[/tt]

Then in the on open event of the other form:

[tt]if not isnull(me.openargs) then
select case me.openargs
case 1
me.recordsource = "qry1"
case 2
me.recordsource = "qry2"
'...
end select
else
msgbox "something wrong"
cancel=true
end if[/tt]

Or pass the queryname as openargs, then

[tt]if not isnull(me.openargs) then
me.recordsource = me.openargs
end if[/tt]

Would some of this apply?

Roy-Vidar