Generate List of -Detail screen < 13 inches

[kacocal]

Hi MS Access Experts,



Could someone help me to use VBA to find the all the forms which has names contain "-detail" that have form width less than 13 inches.

e.g. The display should be like:

Form name Form Width
------------- -------------
A-detail 10
B-detail-xx 12
C-detail 9

Many thanks.

[ranman256]

try:

Public Sub scanAllForms()
Dim f As Form
Dim vTwips
Dim o As Object
Const kTwipsPerInch = 1440


vTwips = 14 * kTwipsPerInch

For Each o In Application.CurrentProject.AllForms
    
    DoCmd.OpenForm o.Name, acDesign
    Set f = Forms(0)   'assumes no other forms are open when this runs
    If f.Width < vTwips Then
      Debug.Print f.Name
    End If


   DoCmd.Close acForm, f.Name
Next
Set o = Nothing
Set f = Nothing
End Sub

instead of debug.print to get the list, you can use sql to append it to a table.

[Minty]

Well the first bit is easy - to get all the form names in a query;

SELECT MSysObjects.Name, MSysObjects.Type, MSysObjects.Flags, MSysObjects.ForeignName, *FROM MSysObjects
WHERE (((MSysObjects.Type)=-32768)) and [Name] Like "*-detail*"

Then you need to get a bit clever - I'm not sure you can get the forms width without it being open... (happy to be proved wrong just haven't had time to investigate)
So you will need to open the form in design view, (to prevent any code running) check the property then close it.

Option Compare Database
Option Explicit


Sub GetFormWidths()


    Dim rs As Recordset
    Dim sSql As String
    Dim iWidth As Long  ' the width will be reported in twips - I'll let you work that out in inches.
    Dim sForm As String
    
    ' open a recordset with the form names - change the criteria appropriately
    sSql = "SELECT MSysObjects.Name, MSysObjects.Type, MSysObjects.Flags, MSysObjects.ForeignName"
    sSql = sSql & " FROM MSysObjects WHERE (((MSysObjects.Type) = -32768)) and [Name] Like " *-Detail* ""


    Set rs = CurrentDb.OpenRecordset(sSql, dbOpenSnapshot)
    
    rs.MoveFirst


    While Not rs.EOF Or rs.BOF
        
        sForm = (rs.Fields("name"))
        DoCmd.OpenForm sForm, acDesign, , , acFormPropertySettings, acHidden
        iWidth = Forms(sForm).Width
        DoCmd.Close acForm, sForm
        Debug.Print rs.Fields("name"), iWidth
        rs.MoveNext
    Wend
End Sub

This will list all the forms and their widths in twips, in the immediate window - hopefully you can work out the rest?
Example from the immediate window of a small sample database;

 GetFormWidth
sfrmDetailLookUp              13656 
sfrmFullDetails              13164 
sfrmRepairList               13296

[kacocal]

Hi Minty,

Thank you for your reply. I am new to MS Access and I want to test your code. It doesn't have any button. Where can I enter the code and how can I run it?

Thanks.

[Minty]

A number of ways - create a new code module and enter code in the VBA editor (Press Alt and F11 to get the editor window open)

When prompted to save the Module call it modBasicFunctions or similar. (Don't call it GetFormWidths access will break!)

In the VBA editor open the immediate window. (Press Ctrl+G to bring it up)
Then simply type GetFormWidths and hit enter.

Learn this technique for debugging it's invaluable. Also for learning functions.
In the immediate window use ? to print a response, if you type the following and hit return

? CDbl(Now())

You should get s0mething like
43727.4002314815

Which is the current date and time value as access actually stores it.

You could then make a form, add a button and in the on click event simply use GetFormWidths to get the reulst in the immediate window.
Finally you could change the results from a Debug.Print and add them to a string and print them out in a message box, or populate a list box with them etc. but we might be getting ahead of ourselves there.

[CJ_London]

? CLng(Now())

You should get s0mething like
43727.4002314815

think that should be CDbl to show the time element

[Minty]

Ooops - I changed it when I tried it then forgot to update the poste - Thanks - fixed!

[kacocal]

Thank you for all your help. The solutions worked just fine. Many thanks.

[Minty]

Glad you got it working!