Solved

Sel Functions in a query based subform

Posted on 2004-03-22
11
355 Views
Last Modified: 2007-12-19
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.
0
Comment
Question by:GRayL
  • 5
  • 4
11 Comments
 
LVL 44

Author Comment

by:GRayL
ID: 10672352
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!
0
 
LVL 54

Expert Comment

by:nico5038
ID: 10678561
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)
0
 
LVL 44

Author Comment

by:GRayL
ID: 10695801
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
0
 
LVL 54

Expert Comment

by:nico5038
ID: 10695871
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)
0
 
LVL 44

Author Comment

by:GRayL
ID: 10696198
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
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 54

Expert Comment

by:nico5038
ID: 10696339
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)
 
0
 
LVL 44

Author Comment

by:GRayL
ID: 10761949
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
0
 
LVL 54

Expert Comment

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

Nic;o)
0
 
LVL 54

Expert Comment

by:nico5038
ID: 10770406
No problem.

Nic;o)
0
 
LVL 2

Accepted Solution

by:
Lunchy earned 0 total points
ID: 10798083
Closed, 500 points refunded.
Lunchy
Friendly Neighbourhood Community Support Moderator
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now