Solved

Sel Functions in a query based subform

Posted on 2004-03-22
11
343 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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

708 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

13 Experts available now in Live!

Get 1:1 Help Now