Build a subform using a select query as input.

[Les]

I have built a command button on a main form that runs a select query and
displays a the result in a subform on the same main form. when I press the
command button a second time to run the query again it says that it can not
get control of the select query in order to run it again because someone is
got control of it. I fig that I have the control from the first query how do
I release the control so the second query may run. each query shows a
different result.

 

[Mark Burns]

Les,

You must first either close the subform's recordset
me.frmSub.form.recordsource = ""
or close the entire subform from the main form
me.frmSub.SourceObject = ""

Then you can make new assignments to the recordsource (if the subform stays
the same)
me.frmSub.form.recordsource = "SELECT..."
(or me.frmSub.form.recordsource = "QueryNameHere")
or map in a new and entirely different form object as the subform
me.frmSub.SourceObject = "MySecondSubFormNameHere"
(you may also need to refresh or reset the Link Child and Link Master fields
to match the new form's control names if they're different)

 

[Les]

Thank you I will give it a try tonight.
--
Les

Les,

You must first either close the subform's recordset
me.frmSub.form.recordsource = ""
or close the entire subform from the main form
me.frmSub.SourceObject = ""

Then you can make new assignments to the recordsource (if the subform stays
the same)
me.frmSub.form.recordsource = "SELECT..."
(or me.frmSub.form.recordsource = "QueryNameHere")
or map in a new and entirely different form object as the subform
me.frmSub.SourceObject = "MySecondSubFormNameHere"
(you may also need to refresh or reset the Link Child and Link Master fields
to match the new form's control names if they're different)

 

[George Nicholson]

How does it "..display the result in a subform.."?

If you've assigned the query directly to the recordsource of the subform
then either just requery the subform or set the recordsource to "" & 'rerun'
the query.

Otherwise: Code please.

HTH,

 

[John W. Vinson]

I have built a command button on a main form that runs a select query and
displays a the result in a subform on the same main form. when I press the
command button a second time to run the query again it says that it can not
get control of the select query in order to run it again because someone is
got control of it. I fig that I have the control from the first query how do
I release the control so the second query may run. each query shows a
different result.

It's neither necessary nor appropriate to "run" the query. Instead, set the
Subform's Recordsource property to the SQL string - the subform will display
the data; opening a query datasheet just gets in the way!

John W. Vinson [MVP]

 

[Les]

This is all new to me. I opened the subform on the mainfrom and set the
property to the query. the eventbuilder looks like the following:
Me.Refresh
DoCmd.SetWarnings False
Close Me.frmSub.Form.RecordSource = "srcCaseType#"

DoCmd.OpenQuery "Src0MkTbl", acNormal, acEdit
DoCmd.OpenQuery "Src1ApTblCT#", acNormal, acEdit
DoCmd.OpenQuery "Src2ApTblCT#", acNormal, acEdit
DoCmd.OpenQuery "Src3ApTblCT#", acNormal, acEdit
DoCmd.OpenQuery "Src4ApTblCT#", acNormal, acEdit
DoCmd.OpenQuery "Src5ApTblCT#", acNormal, acEdit
DoCmd.OpenQuery "SrcCaseType#", acNorman, acReadOnly
DoCmd.SetWarnings True
-- The "SrcCaseType#" is what is being displayed on the subform. each of the
query is listing items from different tables and will in the end be displayed
in the SrcCaseType# query. I need to than be able to select a like from the
output and dblclick on it and have the approiate screen open.

any help would be very welcome.
Les

 

[John W. Vinson]

This is all new to me. I opened the subform on the mainfrom and set the
property to the query. the eventbuilder looks like the following:
Me.Refresh
DoCmd.SetWarnings False
Close Me.frmSub.Form.RecordSource = "srcCaseType#"

DoCmd.OpenQuery "Src0MkTbl", acNormal, acEdit
DoCmd.OpenQuery "Src1ApTblCT#", acNormal, acEdit
DoCmd.OpenQuery "Src2ApTblCT#", acNormal, acEdit
DoCmd.OpenQuery "Src3ApTblCT#", acNormal, acEdit
DoCmd.OpenQuery "Src4ApTblCT#", acNormal, acEdit
DoCmd.OpenQuery "Src5ApTblCT#", acNormal, acEdit
DoCmd.OpenQuery "SrcCaseType#", acNorman, acReadOnly
DoCmd.SetWarnings True
-- The "SrcCaseType#" is what is being displayed on the subform. each of the
query is listing items from different tables and will in the end be displayed
in the SrcCaseType# query. I need to than be able to select a like from the
output and dblclick on it and have the approiate screen open.

any help would be very welcome.

I'm sorry, I'm totally perplexed at what you're trying to accomplish here.

I have no idea what you mean by "be able to select a like from the output".
Select a "line" from the output, or what?

The OpenQuery method is almost certainly NOT what you want to do. All that
will do is open a Query Datasheet view of the data; such a datasheet has no
usable events, and you can't "select" from it. What are these different
queries? What tables are they based upon? And what real-life problem are you
attempting to solve?

John W. Vinson [MVP]

 

[Les]

I have a data base that has 4 main table a Proposal tbl, Sold Case tbl,
Admendment tbl, and Renewal tbl. they all have the following fields in common
Case Track # (unique number), Case Name (one for each tbl like 'soldcase',
'proposal' etc.), Underwriter, Account #. the rest of the fields are
different in each table. I have 4 forms one for each table. I have a menu
that the user wants to be able to enter a search entry like the case track #
or case name or underwriter or account # and the command button for the
search is pressed I am to bring a list of all the records that have the
requested search info up and allow them to select the one they want and when
they dbl click on the request I present to them the form with that info. they
will only be giving me one piece of the info at a time. in other words the
will enter a case track # press search for case track # I will return in a
subform the results and allow them to select the one they want.

I hope this helps.

Thank you very much for you help in this matter.