Solved VBA to set USERFORM comboBox from SQL

[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.