Finding the location of a report on the screen or window.

[Petrol]

I have a report containing several subreports and subforms. From one of those subforms I want to open in VBA a small pop-up form which is to be positioned on the subform which opens it *. The difficulty is to determine the correct parameters for the MoveSize command.
To be specific: To locate the subform, in the popup’s Open event procedure I put

SubformLeft = Reports![report name]![subform name].Left 
SubformTop =  Reports![report name]![subform name].Top 
DoCmd.MoveSize SubformLeft, SubformTop, 5*tpcm, 3*tpcm  

(where tpcm=567 twips per cm). Debug.Print confirms that SubformLeft and SubformTop are the distances in twips of the subform from the left edge and the top of the report which contains it.
But the MoveSize command positions the popup those distances from the left and top of the screen (not from the left and top of the report, as I would hope, or from the left and top of the window, as the Microsoft documentation states). In fact, if the database window is not full screen the popup may appear outside the application altogether.
While this behaviour seems inconsistent, I thought I could get around it by determining the left and top position of the main report, and adding these to the position of the subform on the report. But I have been unable to get the position of the report.

ReportLeft = Reports![report name].Left     and
ReportLeft = Reports![report name].Report.Left 

are both illegal (“Runtime error 2455: You entered an expression that has an invalid reference t the property Left”). So how can I get a position which I can use to set the position of the popup?

(* I would have used a small sub-subform on the first subform instead of a popup, but the first subform is a continuous form and continuous forms can't have subforms).

[NeoPa]

Interesting question. I can confirm that the , .Left, etc properties are properties of Control objects and not Form or Report objects. Therein lies your problem of course. You can't use something that's not there ;-)

As it happens you're in luck. Not that I knew the answer, as frankly I couldn't remember what it was, but I was able to find it reasonably easily.

Form & Report properties all start Window. That is to say you should be looking for the Report (or Form)'s .WindowTop, .WindowLeft, .WindowHeight & .WindowWidth properties.

Good luck. Looks like you're doing some pretty clever stuff in code, which is always fun.

[isladogs]

This can be quite a complex topic and following several similar questions a year or two ago, I wrote a detailed article on the topic of accurately moving whole forms and individual controls.
You can easily find it if you do a web search for Accurately Move Forms & Controls - Mendip Data Systems

It covers the various items that affect positioning including use or lack of scrollbars, record selectors etc and the various units involved (twips, pixels and points). It also shows how the little known and undocumented Wizhook function can be utilised including how to 'select' listbox items without clicking just by moving the mouse over the list.

This is an example screenshot from the article:

[Petrol]

NeoPa, Isladogs, thank you both once again. I have spent the last couple of days still struggling with this.

NeoPa: Yes, I had discovered WindowLeft and WindowTop, and used them to try to locate the position of my main report. The problem is that VBA seems to be inconsistent in its treatment of the origin of its measurements. For example,
...[reportname].WindowLeft and ...[reportname].WindowTop are both zero for a main (non-popup) report. In other words they return the location relative to what I might call the working area of the application window, to the right of the navigation pane and below the ribbon. But when I try to position a control using DoCmd.MoveSize, it positions it relative to the top left corner of the screen. And there is no way I can obtain the position of the main report relative to the top left of the screen, or to the top left of the application window.

(In terms of the diagram frmFormInfo in isladog's helpful article (referenced in his last post above), it seems that FL=FT=0 always for a non-popup report.)

What I have tried pretty well confirms what you said about .Left and .WindowLeft etc:
...[reportname].WindowLeft = 0
...[reportname].Report.WindowLeft = 0
...[reportname].Left and ...[reportname].Report.Left are illegal (Error 2455, invalid reference to the property Left)
But surprisingly,
...[reportname].Control.Left and ...[reportname].Control.WindowLeft both give Error 2465, "Application-defined or object-defined error", which essentially means that VBA doesn't know what the error code means.

So that leaves me with no way I can discover to find the coordinates of the main report, and hence to position a control on that main report. What I had been hoping to do was move the control to (mainreport.WindowLeft + subform.Left, mainreport.WindowTop + subform.Top) or similar.

Isladogs: I did the tests and wrote the main body of the above (except for the parenthetical paragraph) before reading your post. It looks as though you may have the answer, but I still have to work through the code to figure out whether and how to apply it to my control-on-a-subform-on-a-report situation.
As you have probably guessed, all this is an attempt to program around the failure of a combo box, which works on a form, to work when that form is placed as a subform on a report, as discussed in my other active thread. I'll reply to both at once when/if I ever get this sorted out! Thanks again.

[isladogs]

Oh dear...I was really hoping this was nothing to do with your previous thread.

I apologise if I've misunderstood your purpose in the rest of this answer ....

Let me say once again that filtering of reports should be done using the calling form or in code using the recordset.
The filtering is done on opening the report ...NOT afterwards

There are some good reasons for using forms as subreports - for example disconnected ADO recordsets can be used with forms but not with reports.
Here you seem to want a popup form to be placed exactly over a certain area of an open report. But why? Hopefully not to filter the report (see above)

My article is intended to reference different types of form...not forms used as subreports.

I expect it is possible to place an object such as a form precisely on (or over) a report in the same way as I've shown for forms.
However, doing so will not allow you to select items from a combo placed on the report itself.

As already mentioned, forms 4 & 5 in the example app supplied with that article show how a listbox item on a form can be 'selected' without clicking just by hovering over the listbox with a mouse. It does so by determining the exact position of the mouse cursor on the screen and therefore with reference to its position over the form. Using the Wizhook function, the code then determines which row is under the mouse cursor (which is dependant on font size & style as well as the actual font itself) That code will only work if each item in the listbox can be seen without scrolling. For similar reasons, it CANNOT be used with a combobox

By all means, spend time trying to use a listbox with your report if you wish. I expect it will take a lot of time to code & test thoroughly even if you can get it to work at all. But frankly, I think it is a completely pointless thing to do when there is a standard method of filtering a report from the calling form which is both easy to do and works reliably.

[Petrol]

I am quite depressed about this, and am at the point of giving up and telling the organisation that what they want can't be done.

On the face of it, it seems simple enough: We have a main report, to be printed on a single page and copies handed out, with various subreports or subforms containing different pieces of information about a forthcoming event. One of those subforms is a list of the leadership team for the event. For some years the event type has been standardised, so the report has been invariant. Now a new type of event has been introduced, so although the standard event will be reported usually, we want the ability to switch to an event of a different type occasionally. All the leaders of all the forthcoming events are in one query (qry_CRTeams), the events being distinguished by a single-letter event type code (EventCode) in each record in qry_CRTeams. All I want is the ability for the user to change, if necessary, the event in the subform by selecting the relevant EventCode, which would then filter the query to include the appropriate leadership team members.

The catch is that there is no "calling form", other than the main switchboard of the application. It allows the user to produce a number of forms and reports, one of which is this main report. For various reasons it is not feasible to split this menu item in several parts. The simple solution, which I tried first, was to put a combo box on the leadership team subform to allow the user to specify the EventCode and use this to filter the query. That works well when the subform is used on its own, but as soon as it is put onto the main report the combo box stops working, with no error message. (FWIW, I consider this to be an implementation deficiency, or bug, in Access, since the change of behaviour is not documented in the Microsoft documentation of either combo boxes or forms).

The various replies in this thread are appreciated, but haven't allowed me to do what I am trying to do.
(Isladogs, I have never used list boxes, preferring the combo box which in this case was bound to the table of forthcoming events. Your idea of selecting an entry from a list box by hovering over it is quite clever, but I need to be able to click on an option and lock it in so that the report can be printed). And it needs to work on a subform or subreport on a main report. (I could convert the "main report" into a "main form", and hopefully still print it like a report, but that seems to be a bit infra dig).

In summary, I don't want to be able to change any data, only to choose what data is displayed. But as I said at the outset, after working at it for 3 weeks I am tempted to give up :-( .

[isladogs]

I can understand your frustration but no piece of coding is worth 3 weeks of work when a perfectly good alternative is available

From your description, your main menu form /switchboard has a series of buttons, one of which opens the report you want to filter.
You already have a (sub)form that will filter the report exactly as needed from a combobox selection.
So all you need to do is open that form/subform and allow users to view the filtered data in the form itself.
Add a Print button to the form which opens the report filtered to the selection made - that's it - job done!

In case that's not clear, here are a couple of low resolution screenshots from one of my apps where multiple filters can be applied:

[Petrol]

So to clarify, are you suggesting printing directly from the subform, without the rest of the main report?

The attached PDF file (if I can attach it correctly) shows the whole report which is produced and printed in multiple copies to hand out at meetings. The leadership team I have been discussing is the third panel from the left, headed "Men's Walk BS106". This is the one for which the user should be able to produce an alternative event's leadership list. (Up till now I have slightly simplified the discussion - there are actually two such events to be reported on in the one sheet, in this case Men's Walk BS106 and Women's Walk BS107. Either or both of these should be able to be replaced by a different event type). What I have been trying to achieve is a dropdown on each of these two subform headers to filter a different event from the query.

With the modification that there are actually two selectable event team lists, not just one, I think this scenario is exactly what I have been describing throughout this thread (e.g. second para in my #6 post), but I suspect that without the picture it probably wasn't clear. I don't think the example in your screenshots matches this scenario.

The "user" I have been referring to is the person who opens the main report, specifies the events for those two right-hand panels and prints the total report, as per the attachment. This report is then photocopied, or multiple copies printed, to be handed out to dozens of people at the meeting. These people never actually get near the computer.

Am I right in my understanding that your suggestion would put a print button on each of these right two panels top allow them to be printed individually, and we would then print the remainder of the report as a third sheet? This would not be an acceptable solution, but I may have misunderstood you.

[NeoPa]

I hope my interjection simplifies rather than complicates things here but let me try to respond to that.

The concept is for the person sitting at the computer to interact with a Form where all relevant selection criteria are made. How you design the Report to handle that is a different level of the discussion and not relevant at this point unless you have difficulties doing that. From what you've already discussed I very much doubt that would be an issue for you.

Specifics of what is selected where are probably an unhelpful distraction. The point being to design an interface that allows them to convert their understanding of what's required into relevant specifications.

Once the specifications are all clear then the Report can be prepared and printed off. If multiple different versions are required from the same fundamental Report object - just with diffferent selections made - then this is simply done by closing the Report after its first instance has been printed then rerunning it with different selections.

You can even have two separate Report definitions (objects) if you prefer, but that's by no means a requirement of this approach.

[isladogs]

I really don't understand why you wrote this comment:
Am I right in my understanding that your suggestion would put a print button on each of these right two panels top allow them to be printed individually, and we would then print the remainder of the report as a third sheet?

No!

My initial response to your last post is that, either I'm not explaining myself well or you are over-thinking the issue...possibly both are true.
I also agree with NeoPa that the fine details aren't particularly relevant here.

I'm suggesting using a Print button on the form which is used to open your report filtered according to the combo selection on the form.
I have numerous examples where the selection(s) made on a form are used to filter a report. It is straightforward to do

For example, if your form contains a combo cboClass used to select a ClassID (a text field) then the Print button could have code like this:

DoCmd.OpenReport "YourReportName", acViewPreview, , "ClassID = '" & Me.cboClass & "'"

It doesn't really matter whether the example I showed matches your situation - it just illustrates the approach you would use.
If you haven't already done so, why not download the 2 example apps from the link I mentioned previously and the idea should become obvious to you. Your setup is far simpler than the example apps in the link

[Petrol]

OK. I have obviously been very slow to understand your posts till now, but to put it simply I think what you are both suggesting by the persistent references to a calling form is to interpose an extra step, whereby the main menu/switchboard opens a form where the user selects the events, and the form then opens the report I showed in the attachment last time. Is that right?

(I had been aware of that possibility, but had been resisting it because nine times out of ten there are only two forthcoming events and these will be the default selections, so I was reluctant to add an extra step for the user to take each time. However, if that's the only way, then what must be must be!)

[isladogs]

That's correct.
As for needing one extra step, all it means for the end user is having to click one extra button to open the report. Hardly a big deal!

[NeoPa]

Hi Petrol.

There is another option that may suit you better if that extra click really is a problem - and I do understand the importance of a friendly and easy to use interface - and that would be to have two options on your switchboard for this report. One would be the commonly used one (That needs no extra parameters.) & the other would be your alternative. If that were to be a simple alternative then no extra Form need be interposed between the selection and the Report, but if any selection / filtering is required beyond the basic split then clearly an extra Form would be needed to cover that.

As you say, that would only very rarely be needed as the main usage is a one-click option.