Can anyone help me with a function to get the useable screen size i.e. minus
any toolbars or ribbons (if showing), minus navigator pane (if showing) and
minus the strip at the bottom of the screen. That way I can get a form to
fill the screen vertically (I don't want it maximised, only the correct
height. Unfortunately, Restore doesn't seem to work for forms in a referenced
database. Thanks
Phil

On Feb 18, 12:06*pm, "Phil" <p... (AT) stantonfamily (DOT) co.uk> wrote:
Hi Phil,

Because my generalized forms look quite differently in design mode
than in user mode, I tune every form in place and size.
For that purpose I use, as one of the last statements in the OnOpen
event:
DoCmd.MoveSize Left, Up, Width, Height


Imb.

On 18/02/2011 16:14:26, imb wrote:
Hi Imb & Steve
Thanks for replies.

What I am trying to achive is that continuous forms open to the correct size.
If say there is a header 1" high, and each detail row is 1/4" high and there
are 8 records, I want the form to open to 3" (1" + 8 * 1/4") If on the other
hand there ar 400 records, then the form should be 101" long. This obviously
is too long for the screen, so I want to set the form length to the screen
size. I know I don't have to remind Imb, but let me stress that this form is
in the referenced database.

Code so far is

Private Sub Form_Open(Cancel As Integer)

Dim MyDb As Database
Dim SQLStg As String
Dim RecCount As Long

Set MyDb = GetDb ' Get the main database

SQLStg = "SELECT Backups.* "
SQLStg = SQLStg & "FROM Backups "
SQLStg = SQLStg & "IN '" & MyDb.Name & "' "
SQLStg = SQLStg & "ORDER BY BackupDate DESC;"

Me.RecordSource = SQLStg
Me.RecordsetClone.MoveLast
RecCount = Me.RecordsetClone.RecordCount

Call SetPosition(Me, RecCount)

End Sub

Option Compare Database 'Use database order for string comparisons
Option Explicit

Private Type RECT
Left As Long
Top As Long
Right As Long
Bottom As Long
End Type

' CONSTANTS
Private Const SPI_GETWORKAREA& = 48
Private Const TWIPSPERINCH = 1440
' Used to ask System for the Logical pixels/inch in X & Y axis
Private Const LOGPIXELSY = 90
Private Const LOGPIXELSX = 88

Private Declare Function SystemParametersInfo Lib "user32" _
Alias "SystemParametersInfoA" (ByVal uAction As Long, _
ByVal uParam As Long, lpvParam As Any, ByVal fuWinIni As Long) As Long

Private vate Declare PtrSafe Function apiGetDeviceCaps Lib "gdi32" Alias
"GetDeviceCaps" _ (ByVal hdc As Long, ByVal nIndex As Long) As Long

Private Declare PtrSafe Function CreateDCbyNum Lib "gdi32" Alias "CreateDCA"
_ (ByVal lpDriverName As String, ByVal lpDeviceName As String, _
ByVal lpOutput As Long, ByVal lpInitData As Long) As Long 'DEVMODE) As Long

Function GetScreenHeight() As Long

Dim Rc As RECT
Dim msg As String

Call SystemParametersInfo(SPI_GETWORKAREA, 0&, Rc, 0&)

msg = "The coordinates indicating area not used by taskbars is:" & vbCrLf
msg = msg & " left - " & Rc.Left & vbCrLf
msg = msg & " top - " & Rc.Top & vbCrLf
msg = msg & " rght - " & Rc.Right & vbCrLf
msg = msg & " bttm - " & Rc.Bottom & vbCrLf & vbCrLf

msg = msg & "To position full-screen, the syntax is:" & vbCrLf
msg = msg & " Me.Move rc.Left * Screen.TwipsPerPixelX, _" & vbCrLf
msg = msg & " rc.Top * Screen.TwipsPerPixelY, _" & vbCrLf
msg sg = msg & " rc.Right * Screen.TwipsPerPixelX, _" & vbCrLf msg sg = msg &
" rc.Bottom * Screen.TwipsPerPixelY" & vbCrLf & vbCrLf

GetScreenHeight = Rc.Bottom - Rc.Top

'MsgBox msg

End Function

Function SetPosition(Frm As Form, RecCount As Long)

Dim FrmHeight As Long, ScreenHeight As Long
Dim blWH As Boolean
' Reports Device Context
Dim hdc As Long
' Holds the current screen resolution
Dim lngDPI As Long
Dim TwipsperPixel As Long

FrmHeight ight = Frm.Section(acDetail).Height * RecCount ' Detail Height
FrmHeight ight = FrmHeight + Frm.Section(acHeader).Height +
Frm.Section(acFooter).Height

ScreenHeight = GetScreenHeight

hdc = CreateDCbyNum("WINSPOOL", GetDefaultPrintersName, 0&, 0&)
If hdc = 0 Then
' Error cannot get handle to printer Device Context
Err.Raise rr.Raise vbObjectError + 255, "SetPosition", "Cannot Create Printer
DC" End If
' Get current device resolution
' blWH=TRUE then we are TextHeight
blWH = True
If blWH = True Then
lngDPI = apiGetDeviceCaps(hdc, LOGPIXELSY)
Else
lngDPI = apiGetDeviceCaps(hdc, LOGPIXELSX)
End If

' Calculate TwipsPerPixel
TwipsperPixel = TWIPSPERINCH / lngDPI

If FrmHeight > ScreenHeight * 5 Then
Frm.Move 0, 0, Frm.Width, ScreenHeight * TwipsperPixel * 5
Else
Frm.Move 0, 0, Frm.Width, FrmHeight * TwipsperPixel
End If

End Function

Code nicked from various sources, but doesn't work as expected
SystemParametersInfo gives the same screen size regardless of whether the
ribbon is maximized or minimized, or of the nav pane size The bit about the
printer is rubbish, partially nicked from Stepen Lebans modtextheight
routine, but I thought it had somethin to do with screen size. The fiddle
factor of 5 in the last bit, just sort of gets it to work.

Any thought please

Phil

Hi Phil,

The maximum number of records you can display, can be calculated very
easily:

Nmax = Height_form(wanted) – Height_header – Height_footer /
(Height_per_record + Record_distance)

To calculate the actual height of the form to be used, you use:

N = Minimum(Number_of_records,Nmax)
Height_form = Height_header + Height_footer + N*(Height_per_record +
Record_distance)


For continuous forms I normally do not change the height of the form,
but I change the width of the form depending on the width of the used
fields.
In a Single_form (to display/edit a single record) I have the fields
vertical, and I adjust the height of the form according to above
mentioned rules (Height_per_field instead of Height_per_record). The
same holds for a New_form, to edit/define a new record.

In my approach I do not need to correct for banner or nav pane,
because I do not use them.
In the StartupForm all necessary functionality is available to
navigate through the application, so I do not need the nav pane. In
the same way all necessary functionality of the banner is already
build in in the standarized forms that I use.


Imb.

On 19/02/2011 23:26:44, imb wrote:
Hi Imb
The width is not really the problem, as in the AccDe, the Nave pane is
hidden. As you do, I have a menu which shows only those forms and reports
that each user is allowed to view or edit. However, I still think it is nicer
with a continuous form with only a few records, to show only enough form to
show those records rather than a form the full height of the screen with a
large empty area. I use a simililar calculation to get height of form needed
to show those records, so I still need the height of the screen to establish
whether all the records can be displayed, or only those that will fit on the
screen. Thanks again
Phil

Hi Phil,

Perhaps I do not understand your question: “I still need …”

If the maximum number of records you can display is 20, then you
calculate the height of the form for 20 records if there are more than
20 records in your recordset, else you calculate the height of the
form for the actual number of records in the form.
In fact this is the minimum value of the number of record and 20.


Imb.

On 21/02/2011 11:47:54, imb wrote:
Imb

Of course you are right

I am getting thick. Put it down to old age.

Thanks again

Phil

On 21/02/2011 12:57:44, "Phil" wrote:
Hi Imb

Having thought about it again, maybe I'm not so senile after all.
The maximum height of the form is limited by the screen size, hence my
question. The useable screen size is governed by such things as screen
resolution and what is showing other than the form (Nave Pane & ribbon are
the obvious ones) That is why I attempted to use the code posted earlier to
get the screen size. Cheers
Phil

On 21/02/2011 13:36:37, "Phil" wrote:
Hi Imb

Incidently, totally off the subject, Re Progress Meter, did you pick up the
link to Former MVP Sandra Daigle has a nice selection of Progress Bars and
forms: http://www.accessmvp.com/SDaigle/ProgressMeter.ZIP

Looks good

Phil

Hi Phil,

Glad to read you are still young.
About the height of the banner, you can measure it, and account for it
in the determination of the maximum number of records you will have on
the screen. In the user applications I do not use banners or something
like that, so this is not an item for me.
Screen resolution is important. Your maximum number of visible records
is directly proportional to the resolution. In the BE I have a
Setting_table where the screen resolution and other kind of
information can be stored, and accounted for at opening of a form. In
this way this information is preserved from new updates.

About the off-subject:
Yes, I saw that progress bar, and mine is very similar. But I am
always working towards more playful (in fact parametrized) routines,
to investigate technical possibilities.


Imb.