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
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.
forms!fmName!sfmPage1.form
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.
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)
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
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.Fo
2. Forms!MainForm!SubForm2.Fo
also,
Screen.Activeform.Selheigh
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.Sel
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
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.Fo
2. Forms!MainForm!SubForm2.Fo
also,
Screen.Activeform.Selheigh
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.Sel
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)
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
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!Subfor
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
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!Subfor
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)
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)
ASKER
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
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)
Nic;o)
No problem.
Nic;o)
Nic;o)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER