Build a subform using a select query as input.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Les - 23 Aug 2007 22:54 GMT

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.

Signature

Les

Reply to this Message

Mark Burns - 23 Aug 2007 23:06 GMT

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)

> 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
[quoted text clipped - 3 lines]
> I release the control so the second query may run. each query shows a
> different result.

Reply to this Message

Les - 23 Aug 2007 23:12 GMT

Thank you I will give it a try tonight.

Signature

Les

> Les,
>
[quoted text clipped - 19 lines]
> > I release the control so the second query may run. each query shows a
> > different result.

Reply to this Message

George Nicholson - 23 Aug 2007 23:12 GMT

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,

>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
[quoted text clipped - 6 lines]
> I release the control so the second query may run. each query shows a
> different result.

Reply to this Message

John W. Vinson - 24 Aug 2007 05:03 GMT

>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
[quoted text clipped - 3 lines]
>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]

Reply to this Message

Les - 27 Aug 2007 13:56 GMT

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

> >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
[quoted text clipped - 9 lines]
>
> John W. Vinson [MVP]

Reply to this Message

John W. Vinson - 27 Aug 2007 18:52 GMT

>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:
[quoted text clipped - 16 lines]
>
>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]

Reply to this Message

Les - 29 Aug 2007 22:26 GMT

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.

Signature

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:
[quoted text clipped - 29 lines]
>
> John W. Vinson [MVP]

Reply to this Message