Avatar of GRayL
GRayLFlag for Canada asked on

Sel Functions in a query based subform

I have a 3 tab form. In the first two pages the sourceobject of the subforms is a table. In the last page, the sourceobject of the subform is a query. I can determine the size and location of a selected/highlighted area on the first two pages with:
forms!fmName!sfmPage1.form.selleft  or seltop,selheight, or selwidth - this works fine. Of course I switch between sfmPage1 and sfmPage2.

When I select page 3 of the form and use the same code, I get a runtime error 2455. Incorrect reference to SelHeight.

I have been chasing this one for over a month. Can anyone tell me how to programatically determine the location and size of a highlighted area on a query which is the sourceobject of a subform? I am using Access 2000 under XP Home. The query is a crosstab and therefore cannot be updated.
Microsoft Access

Avatar of undefined
Last Comment
Lunchy

8/22/2022 - Mon
ASKER
GRayL

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!
nico5038

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)
ASKER
GRayL

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
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
nico5038

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)
ASKER
GRayL

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
nico5038

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)
 
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
GRayL

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
nico5038

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

Nic;o)
nico5038

No problem.

Nic;o)
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER CERTIFIED SOLUTION
Lunchy

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question