Use VBA to set the width of the parent window to show a form and a table side by side

I am not sure how you can accomplish this based upon another Form, but if you know the dimensions you can simply use the MoveSize method to set the Form dimensions.

http://msdn.microsoft.com/en-us/library/bb238004(v=office.12).aspx

Hope this helps,

Daniel

This is what I use to resize the Access window after loading:

Public Sub ResizeApp()
    'Resize main app window to 800 high x 1200 wide
    'x and y are the orgin of the top/let corner
    'cx and cy are the size of the window.
    Application.RunCommand (acCmdAppRestore)
    SetWindowPos Application.hWndAccessApp, 0, 0, 0, 1200, 800, 0
End Sub

I call this function form my startup form on Load. But it can be called at any time. You can modify this to use passed parameters for height and width.

Scott,

I see where you are coming from but I still cannot crack this problem.

I need to be in the table to calculate its width based on the max length of the longest record for each field and add say 1000 for the two side margins - this gives the width of the table 'WidthOfTable'

I have already found the width of the form 'WidthOfCreateNew'

I now want to set the width of the parent window to equal WidthOfCreateNew + WidthofTable.

To do this I think  that I have two options:

1. Set the focus onto the parent window and set its width and then return to the table or

2. Use DoCmd.MoveSize but direct the action to the parent window.

Thus think that I need to get the name of the parent window so I can either move there for Option 1 above or direct the action there for Option 2.

Look forward to any assistance.

Thanks again,

Regards

Barry GC

Please clarify what you mean by "parent window"? Does this refer to main Access window or a specific form?

My function works on the main Access window. You can add code to calculate what the width should be and plug that in as a parameter into the function.

If you are referring to an Access form, then I believe it does need to have focus to adjust it.

Scott,

I was referring to the main Access window that I wanted to be just large enough for the form and the data table and leave room on the right for the code during development phase.

I now have SetWindowPos Application.hWndAccessApp, 0, 0, 0, BigWindowWidth, 6000, 0.

The code currently appears to be forcing the main Access window to fit the full screen and not basing the width and height on BigWindowWidth wide and 6000 high Thus it is not leaving any space on the right of the screen for the VBA code window.

I shall continue fiddling to see what is happening.

Regards

Barry GC

Scott,

Module "modUser" has:

Declare Sub SetWindowPos Lib "user32" (ByVal hWnd&, ByVal hWndInsertAfter&, ByVal X&, ByVal Y&, ByVal BigWindowWidth, ByVal cY&, ByVal wFlags&)

Form "frmCreateNew" has the lines:

BigWindowWidth = .........

SetWindowPos Application.hWndAccessApp, 0, 0, 0, BigWindowWidth, 6000, 0

I get a "Bad DLL calling convention" error on the last line of the code.

I am sure that this is a simple problem.

Regards

Barry GC 

That's not where I would do it.

Public Sub ResizeApp(intwidth As integer, intheight As integer)
    Application.RunCommand (acCmdAppRestore)
    SetWindowPos Application.hWndAccessApp, 0, 0, 0, intheight, intwidth, 0
End Sub

Then call it using ResizeApp(BigWindowWidth,800)

Scott,

Sorry for the 2 week delay, I have had other things going on!

It is not quite working there yet. I will re-cap the current situation instead of following through the threads above.  

My modUser module currently includes:

Public Sub ResizeApp(intwidth As integer, intheight As integer)
    Application.RunCommand (acCmdAppRestore)
    SetWindowPos Application.hWndAccessApp, 0, 0, 0, intheight, intwidth, 0
End Sub

The active form 'frmCreateNew" needs code to allow its parent window to display the form and the separate table 'ShowAllocNos' alongside it.

The code in frmCreateNew calculates values for:

WidthOfCreateNew

WidthOfTable

These two width values are then added together to give a value for the parent window:

BigWindowWidth = WidthOfCreateNew + WidthOfTable

I then call up the ResizeApp using:

DoCmd = ResizeApp(BigWindowWidth, 6000)

but it gives an error message 'Expected Function or Variable'.

What is going wrong here?

Once I have got the width corrected then I will correct the height in the same manner.

Thanks for your patience.

Regards

Barry GC