Solved VBA to set USERFORM comboBox from SQL (1 Viewer)

[Marshall Brooks]

This will be more of a question for @isladogs and I have a workaround, so I'm not terribly concerned about that.

The goal is to have my progress bars displayed in the center of the screen on whichever monitor Access is currently using. (And ideally, it needs to work if Access is opened on one monitor and then moved to a different screen.

The code that I have works for my userforms at any setting.

It works for frmTimerProgressBar in the following situations:

• Single Screen
• Dual Screens, Primary on Left, Access on Primary.
• Dual Screens, Primary on Right, Access on Primary.
• Dual Screens, Primary on Right, Access on Secondary.

It fails for Dual Screens, Primary on Left, Access on Secondary. And gives me a RT Error 2498 "An expression you entered is the wrong data type for one of the arguments." on this line:

DoCmd.MoveSize sngLeft, sngTop

As a workaround, I changed the AutoCenter property of the form to Yes and added On Error Resume Next before that line and On Error GoTo 0 after it. With that change, it displays on the right-side (secondary) monitor, in the center horizontally, but about 1/3 of the way down the screen.

I saw

Form open from another Form

Hello my freinds, I have a problem, what i cant fix.I have 2 monitor, when i try to open a form from a form on my second monitor it has a runtime error 6 overflow if i try to open on my secondary monitors's farest half. Its only work on 2/3 of my monitors. Sorry for bad english :( www...

www.access-programmers.co.uk

and

".MoveSize" or ".Move" for a popup form positioning

I created an on click button event, which is supposed to move my popup form to the top left corner of the screen. The function that I used was: Private Sub Command1_Click() DoCmd.MoveSize(0 ,0)...

stackoverflow.com

But I couldn't get either one to work.

I think have tblMonitors from the AFR database (but I'm not using AFR with this form), and I think it SHOULD work with making MoveWindow Public and:

Form_Load()
Dim myrect as RECT
'Move the window to the top right, keep width and height equal to the current values
    CheckMonitorInfo
    MoveWindow Me.hWnd, 0, 0, myrect.right - myrect.left, myrect.bottom - myrect.top, True ' Top, Left, Width, Height, Repaint
    ' Get Left and top from DLookup of current Monitors in tblMonitors, adjust above statement
    End Sub

But it either doesn't work or gives me an error message on the "With" Statement when I call the form.

 

[isladogs]

I'm somewhat busy at the moment but try looking at these articles:

Accurately Move Forms & Controls

This article demonstrates how to accurately move forms & controls based on the various units of measurement used in Windows applications (pixels, points and twips) together with the little known and undocumented VBA Wizhook function

www.isladogs.co.uk

Centre Form On Screen

This article compares the effectiveness of four code samples used to centre forms in both horizontal and vertical directions

www.isladogs.co.uk

Extend Access across Multiple Monitors

This example app provides code that allows the Access application interface to be extended across two or more monitors. It can be used to show different forms or reports on separate monitors simultaneously or to simulate having a very large monitor

www.isladogs.co.uk

Auto position objects in extended Access interface

This example app shows how objects can be automatically positioned on different monitors in an Access application interface which is extended across two or more monitors.

www.isladogs.co.uk

The final article includes a comment under 'Positioning Forms' which should explain why you may get errors when placing objects on multiple monitors

 

[Marshall Brooks]

Thank you!!!!

https://www.isladogs.co.uk/centre-form-on-screen/index.html - Looks like it should have something I can use!!!

 

[Marshall Brooks]

Spoke too soon.

I am not trying to extend Access. I want everything on the same monitor.

The specific issue I am having is if the left screen is my primary monitor and Access is running on the secondary monitor (Right). I want the pop-up form centered on the secondary monitor (Right).

The centre pop-up form example database centers all 4 forms on Monitor 1 (Left) regardless of where Access is.

The issues I am running into are:
Access provides a built in 'Center Form' as part of the form property sheet. However this only centres the form in a horizontal direction. The outcomes at times can also be visibly well off-centre.

This is the "workaround" that I am using. The form is centered horizontally, but is about 1/3 of the way down from the top of the screen. I can live with that, but it looks unprofessional.

2. Positioning forms
My original plan was to center the forms on each monitor.
However doing so triggered error 2498 as the integer limit (32767) was exceeded for the left value in the DoCmd.MoveSize code line.

I expect this issue will be fixed soon as the Access team have announced that support for large monitors will be added in the relatively near future.

This is why I was getting the error with the basic centering code that I was using. The error message was unacceptable for my usage.

There is some way to center the form on the secondary monitor using API calls - b/c the Enhanced Message Box code is able to do it, but I couldn't figure out how to adapt that to my simpler form.

 

[Marshall Brooks]

Disregard - I figured out a solution - it's somewhat crude, but it works perfectly.

There's a lot more in the example database that I posted, but the problem line was this:

On Error Resume Next
DoCmd.MoveSize sngLeft, sngTop
On Error GoTo 0

I realised from what you wrote when I copied it, that the built-in pop-up code centers the form horizontally, but doesn't center it vertically. The runtime error occurs when I try to center the form horizontally, but Access handles that fine.

So I just changed the middle line of the code to:
DoCmd.MoveSize , sngTop

So the pop-up property handles the horizontal positioning of the form and the VBA code handles the vertical positioning.

A bit crude and not sure it would work in all situations, but it seems to do what I need.

 

[isladogs]

Glad you have a solution that works for you.
I wasn't able to use that approach for extending the interface for a number of reasons

Hopefully this will become easier to manage when support for larger forms / monitors is added in the not too distant future

 

[Marshall Brooks]

@isladogs - I would never have figured it out without the links you provided.

You might have inside information that I don't have, but I'm not holding my breath on larger forms monitor support. Dual monitors have been supported since Windows 98, and not fully integrated into Access yet.

 

[isladogs]

I may indeed have additional information beyond what is in the public domain
However, I hope you will understand that I'm not able to comment further at this stage

 

[Marshall Brooks]

Hopefully @MajP and @The_Doc_Man can indulge me a bit more.

I'm going back to Reply #77 of this current thread and some timer info from Reply #9 of this thread: https://www.access-programmers.co.uk/forums/threads/three-timer-questions.329391/

I have both a userform and a pop-up Access data form that look like a message box but do not halt code execution.
I have an Access Data form that uses a countdown timer and closes after a specified number of seconds and does not halt code execution.

I was trying to auto-size the pop-up forms and asked here and that seems like it will be difficult: https://www.access-programmers.co.uk/forums/threads/autosizing-text-fields-on-a-pop-up-form.329561/

I know how to size the userforms. I am trying to adapt the timer code for the userform, but there is no built-in timer.

The code for the data form looks like this (I'm omitting the Form_Load() code which positions the form and works):

'Declare a Form Global variable to hold the
'seconds expired since Form was opened.
Dim TimeCount As Long
Public AutoCloseSec As Integer

Private Sub bt1_Click()
DoCmd.Close acForm, "frmModelessAutocloseBox"
End Sub

Private Sub Form_Timer()
'Increment the TimerCount variable by 1
 TimeCount = TimeCount + 1
 'Display the current seconds remaining in the 'text box corner of form
 ' Me.TxtCounter.value = 15 - TimeCount
 Me.Bt1.caption = "OK (" & AutoCloseSec - TimeCount & ")"
 
 If TimeCount = AutoCloseSec + 1 Then
 Me.TimerInterval = 0
 'Unload Me
 DoCmd.Close acForm, "frmModelessAutocloseBox"
 End If
End Sub

The code I tried to use with the userform looks like this (omitting Userform_Iinitialize() code which positions the form and works):

Public AutoCloseSec As Integer

Private Sub bt1_Click()
Unload Me
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
' handles the red x
  If CloseMode = 0 Then
Unload Me
 End If
End Sub

Private Sub UserForm_Current()
Dim TStamp As Date
Dim IElapsed As Integer
TStamp = Now()
AutoCloseSec = 15
Do Until IElapsed > AutoCloseSec
DoEvents
IElapsed = DateDiff("s", TStamp, Now())
MsgBox IElapsed
Me.Bt1.caption = "OK (" & AutoCloseSec - IElapsed & ")"
Loop
Unload Me
End Sub

I don't get any errors, but the timer doesn't work and the MsgBox never displays. I originally had the UserForm_Current() code in Userform_Load() and nothing happened with it there either ...

 

[Marshall Brooks]

Figured this out - simple mistake. There is no Userform_Load or Userform_Current Event. I renamed the Userform_Current procedure above to ButtonCountdown and added a Userform_Activate procedure and added "Call ButtonCountdown" to that and it works perfectly.