Sel Functions in a query based subform

This question is two days old and I haven't heard a peep. Am I blowing smoke or can it be done? It looks like you otta be able to, but maybe Access just doesn't deal with queryies in subforms the way it deals with tables. Interestingly, when the crosstab is run as a query, an area is highlighted, and I go to the Immediate window, the code line  "? screen.activedatasheet.selheight"  gives me the height of the selected area. It's a mystery to me!

Hmm, I guess it's a hard one as it should work similar as the other forms so I guess the crosstable is causing the trouble.
Did you try to create a new query selecting all fields from the crosstable query and place that behind the subform?
Another option is to define a create (temp) table query based on the crosstable query and generate that in the OnOpen form event and refer the subform to that.

Just some thoughts .

Nic;o)

Nico: Further to the above, I tried overlaying the form with the query in datasheet view but I cannot programatically get to the activedatasheet. This is how I see it.
I open two forms in an Access Desktop both of which have imbedded tables in datasheetview in subforms.  I highlight different areas in each table in their respective subforms. I can determine both form selected area heights by:

1.      Forms!MainForm!SubForm1.Form.Selheight, and
2.      Forms!MainForm!SubForm2.Form.Selheight.
also,
      Screen.Activeform.Selheight  works for the form which is active.

This only works for tables in datasheetview. It does not work for queries!

Now open two separate tables and create different sized highlighted areas. Do not close either of the forms. I can still determine the heights of the highlighted areas in both forms by 1. and 2. above.

I can only determine the height for the active table only by:

3.      Screen!ActiveDataSheet.Selheight

If I make either of the two forms active, how do I determine the selheight of  either or both tables? Is there a container for open tables and queries in Access like there is for Forms and Reports. It beats me!!

Ray

Hi Ray,

I do wonder what you try to achieve with all these selections, but perhaps a look on the form with the problem might give me an idea.
Can you campact and zip (part of) the .mdb and drop it in my mail ?

Nic;o)
(Click my membername for the emailaddress)

Nico: I am doing a scheduling program for 4 doctors in a clinic. Each has about 1500 patients. The clinic is open from monday thru friday and each doctors works different hours on different days (table DrsDays). Each doctor advises in advance when they will not be available, holidays, courses (table DrsAway). The clinic closes for those holidays which fall on weekdays (talble Holidays). The running schedule has to be at least a year ahead of date(). I have developed the process to produce a table DrsAppts consisting of fields DrID, PatientID, DateTime and Reason. I have designed a cross tab based on DrsAppts with columns consisting of the dates, and the rows consisting of times with criteria of DrID and dates for the first 185 days. If needed, the crosstab is regenerated for the last half of the year.
Thus with a doctor selected from the Doctor page  and a patient selected selected from the Patient page of a tab-controlled form, I can generate the crosstab query on the fly to see where vacancies exist in the schedule.

I can imbed the crosstab query on a subform of the form, but, to my suprise, I discovered you cannot programatically determine the selheight of any imbedded query. The intent was to use the selected patient and the selected doctor from different pages, plus the date and time from the subquery to update the table DrsAppts. When the query is opened directly as an activedatasheet, the info is available, when it is imbedded in a subform using sourceobject, is is not.

I then reasoned I could open the crosstab directly on top of part of the form and by clicking on a commandbutton, with the proper code in onclick, I could determine selheight, and thus the number of 15 minute periods required for the appointment.  However, as soon as the button is clicked, the crosstab disappears behind the main form and no longer is the activedatasheet, the source of my selheight.  After another day of scratching around the objects and properties I discovered there did not appear to be a container for queries and tables opened directly in the Access window as there were for forms and reports.

Forms!MainFormName1!SubformName1.form.selheight will give you the height of the selected area of any open subform in any mainform of the Access window regardless of whether it is active or not. That info is available only for the active open table or query similarly in the window.

How do I programatically determine:

a.  the selheight of a highlighed area in a query imbedded on a subform, or

b. the selheight of a highlighted area in a table open, but not the active element in the Access window.

Perhaps I will have to write a macro which senses a function key press to get the selheight of the active table. I don't know.

As you can appreciate, I have some real sample data in the database and am therefore reluctant to Email it anywhere. If needed, I will produce some dummy data and we will go from there.  I hope the above at least explains where I am going with this issue. The fact that the crosstab produces a very neat schedule in seconds and is constantly updated keeps me wanting to use this approach.

much obliged,

Ray

Nice piece of work sofar Ray !

I've tried to get the properties from a launched query and not successfull yet, but when your columnheadings are fixed a "quick workaround" might work too.
Just create a "MakeTable" query based on the crosstable query and set a subform to the created table.
After the first run you need to drop the table before running the maketable query.

I'll keep trying :-)

Nic;o)
 

Hi People: The only fix to this problem seems to be to overlay an open query on the form (in the right position and right size) and after having created a command tab to execute a "captureselsize"  code function which does not "hide" the active screen, you can get to the numbers - selleft, seltop, selheight, and selwidth. It's not pretty but it works. How do you tell Bill he  has a flawed app? I will close the posted question in 4 days unless someone really wants to do a points grab!

Stay tuned,

Ray

No problem, just post a request in CS(https://www.experts-exchange.com/Community_Support/) for a PAQ and refund.

Nic;o)

No problem.

Nic;o)