Solved VBA to set USERFORM comboBox from SQL

[Marshall Brooks]

@MajP - I hope you can indulge me and help me out one more time. Same UserForm, different area of the database. Here's what it looks like:

This is sample data and not actual text, obviously. It works well for short text fields. Not visible in the image: "Please verify database field contents. Click OK when complete." is in TextBox1. I have TextBox2 over that and it is empty. When the UF is displayed, there is no way to click into either textbox.

I revised the UF with a textbox, but when I call the revised UF, if I click the up Arrow, I can move around in TextBox 1:

How can I prevent this? It doesn't happen with the combobox.

Thanks in advance!

 

[Marshall Brooks]

In case anyone is wondering b/c it looks obvious but it isn't - here's what I did:

My first thought was just to make the form and the combobox taller. That changed the size, but not the results b/c there is no word wrap property for a combobox.

I made a textbox (textbox3) with the same dimensions as the combobox. Then I had to set the following properties for the new textbox:
Autosize - false
AutoTab - False
AutoWordSelect - False
BackStyle - 1- fmBackStyleOpaque
Enabled - True
IntegralHeight - False
Locked - False
MultlLine - True
Selection Margin - False
TabKeyBehavior - False
TabStop - False
WordWrap - True
Visible (False) (For Default setting, change to True and Combobox1 to False when calling from VBA.)
Okay button is changed to return public variable from combobox1 if visible or TextBox3 otherwise.

 

[MajP]

I am not clear on the issues. What you currently have working, what you changed, and what is not working. Can you provide me the Userform and a means to test it?
You say that TextBox1 is the thing holding ""Please verify database field contents. Click OK when complete.", but your concern is that you can move around in it. Maybe I am missing something but why is that a textbox and not a label? Do not think you can have that problem with a label.

 

[Marshall Brooks]

@MajP - You nailed it (again).

Short answer is that I built the userform years ago when I didn't know much about them and I thought that a textbox held text and a label held the caption for a button or control.

It works - I changed textbox1 name to textbox1_discarded and created a label with the same dimensions called textbox1.

Then I had to change all the callouts for textbox1.value to textbox1.caption.

Now if I press the up arrow, I go into textbox2, which I think I can now remove.

I think I have it from here.

Let me know if you still want the example file, but I have a lot of other concerns going on now, so it might take me a while.

Thank you again!!!

 

[MajP]

Only need it if you have more issues.

 

[Marshall Brooks]

Sounds good - I think I've got it from here!!!

But I wouldn't have gotten here without your assistance!

 

[Marshall Brooks]

Another new issue:

I have a regular Access form in the database for a "modeless autoclose message box". It is similar to the autoclose Enhanced Message Box, but it allows background code to continue running while the box is displayed.

The message box is called like this:

DoCmd.OpenForm "frmModelessAutocloseBox"
Forms![frmModelessAutocloseBox].Prompt.value = "Your Message Here."

It is hard-coded in the form code to close after 30 seconds like this:

 http://www.iaccessworld.com/close-program-with-count-down-timer/
'Declare a Form Global variable to hold the
'seconds expired since Form was opened.
Dim TimeCount As Long
Dim AutoCloseSec As Integer

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 (" & 30 - TimeCount & ")"
 
 If TimeCount = 30 + 1 Then
 Me.TimerInterval = 0
 'Unload Me
 DoCmd.Close acForm, "frmModelessAutocloseBox"
 End If
End Sub

I would like to pass it a variable for how long before it closes. AutoCloseSec is never used. I know I would just replace 30 with AutoCloseSec, but how do I pass AutoCloseSec to the form when I call it? (Other than making it a global variable for the database.

Thanks again!

 

[Marshall Brooks]

Alternate question - what I would prefer is modifying EMB to do the same thing. I made a copy of the EMB form and went into design mode and changed modal to no and border style from dialog to thin. And then I made a copy of his module called Dialog.box and renamed it to Dialog.PopUp and modified that to call the modified form, but it still waits before running my code in the background. Not sure how to get that working.

 

[MajP]

I would like to pass it a variable for how long before it closes. AutoCloseSec is never used.

Variables at the top of a form's module are class variables. If you make them public then you can call them directly. If you "Dim" that defaults to private in scope so you cannot reference from outside the form.

Public AutoCloseSec as integer

Then

DoCmd.OpenForm "frmModelessAutocloseBox"
with Forms![frmModelessAutocloseBox]
   .AutoCloseSec = 45
   .Prompt.value = "Your Message Here."
end with

Or you can make a Property like this, but still call it in the same way

Private m_AutoCloseSeconds As Integer


Public Property Get AutoCloseSeconds() As Integer
  AutoCloseSeconds = m_AutoCloseSeconds
End Property

Public Property Let AutoCloseSeconds(ByVal NewValue As Integer)
  m_AutoCloseSeconds = NewValue
End Property

The Docmd.open form lets you pass in arguments that are available at opening of the form.

docmd.openForm "FrmModelessAutoCloseBox",,,,,,30

Then in the forms on open Event you can do something like

If isnumeric(me.openArgs) then
  autocloseSec = cint(me.openargs)
else
  autocloseSec = 30
end if

 

[MajP]

Alternate question - what I would prefer is modifying EMB to do the same thing. I made a copy of the EMB form and went into design mode and changed modal to no and border style from dialog to thin. And then I made a copy of his module called Dialog.box and renamed it to Dialog.PopUp and modified that to call the modified form, but it still waits before running my code in the background. Not sure how to get that working.

Hard to answer without seeing the form, module, and the code you expect to run in the background. I am uncertain how these work together.

 

[Marshall Brooks]

Thank you again @MajP !!!

I'm continually amazed and wish I had 1/10th of your knowledge - and the other forum members!!!

The public variable seems simple enough. The property statement I've seen before, but never really understood. It's something I should research when I have free time.

Question - the public variable is not the same as a global variable, correct?

IOW - is there a difference in scope between putting Public AutocloseSec as Integer at the top of the form's class module and putting the same code at the top of module1 in the main VBA module? Would there be a danger in having the same variable name in both places? (I've been burned by that before.)

Regarding reply #68 and #70: I might have a solution. I changed the form to non-modal, but I remembered in the calling code for the form, it is still specified as modal, but there is a parameter that can be set to make it modeless - but I didn't change that. I'm thinking if I change that, it will work without halting the background code. I'll test that tomorrow.

Thank you again!

 

[MajP]

The property statement I've seen before, but never really understood. It's something I should research when I have free time.

The property statement can provide a level of protection from the public class variable. You might have some error checking code in the property or trigger some other action when the user sets or gets the property. In my simple example it provides no extra benefit

Question - the public variable is not the same as a global variable, correct? IOW - is there a difference in scope between putting Public AutocloseSec as Integer at the top of the form's class module and putting the same code at the top of module1 in the main VBA module? Would there be a danger in having the same variable name in both places? (I've been burned by that before.

A public variable in a class module can only be accessed by that class instance.
x = Forms!Form1.SomePublicVariable
A variable, procedure, or event name in the class is encapsulated in the class and will not conflict with other names in other modules or objects.

A public variable (global) in a standard module can be accessed directly
x = somePublicVariable
Public variables in a standard module can conflict with other similar names

I changed the form to non-modal, but I remembered in the calling code for the form, it is still specified as modal, but there is a parameter that can be set to make it modeless - but I didn't change that. I'm thinking if I change that, it will work without halting the background code.

In access there is not a way to call it modeless. There is a way to do this the other way. You can make a modeless form Pop Up and Modal. In the Docmd.openform method you can supply the window mode argument ACDIALOG. However this does one more thing which can be advantageous or not. When you use this argument code in the calling form stops and resumes only after the called form closes. This means you can not open a form and try to modify it from the called form. You cannot call a method in the called form.

 

[Marshall Brooks]

@MajP - Thanks - my idea for the code change did NOT work. I'll post a sample DB later. I don't think Olaf used the ACDIALOG argument, but he did use the border style.

Where I'm lost is my form works and his doesn't, and I don't know why.

Update to come later today.

 

[Marshall Brooks]

Demo database attached. It's easier to see if you do NOT have it maximized, but you'll figure it out easily enough.

There are comments in the MajP_testing module.

I haven't added Reply #69 to it, but I'm sure that will work.

So you understand what is going on:

• Sub Halts_Code works, but the form is a lot more crude than the EMB form - does NOT autosize with additional text, does NOT have custom buttons throughout.
• EMB is used throughout the form. There is a EMBTest form that let's you set values and generates the code for you. It is handy.
• I made a couple of modifications to EMB - https://datenbank-projekt.de/projekte/improved-enhanced-message-box-ms-access:
  • I added the ability to change foreground (text) colors for the buttons. I sent this version to Olaf, but he never released it.
  • I slightly modified the test form. (Basically, I set the commonly used options to our default values and change the code generation portion to not include those parameters unless they were changed from the defaults.)
  • I don't think I changed anything else.
• Olaf (EMB Developer) has an option for BoxIsModal. If UNCHECKED, it allows you to make changes to the database while the message box is displayed. It still halts code and code does not run in the background.
• Olaf was working on an option to allow code to run in the background, but I don't think he ever got it to work. There is an untested parameter called "BoxIsOnTop" and I think setting that to False was supposed to allow code to continue running, but I don't think it ever worked.
• frmEMP_PopUp is a crude attempt to use EMB without halting the code. Basically, I simply made a copy of the EMB Dialog form and changed the modal property and border style in design view and then made a copy of dialog.Box and named it dialog.Popup to call the new form and run it modelessly.
• If you can get it working, I'll edit the test form (for myself) to add a new button to create the code for the dialog.popup option.
• Ideally, I'd prefer to use just one form with similar code and a new parameter, but that is WAY beyond my coding skill and I don't want to risk the existing code not working properly.

Thanks in advance!!!

 

[MajP]

This may be beyond me. This form is relying heavily on windows API which is not my skillset.

 

[Marshall Brooks]

Thanks - I really appreciate you looking into it!!!

 

[Marshall Brooks]

This may be beyond me. This form is relying heavily on windows API which is not my skillset.

Shot in the dark ...

Could you go the other direction?

i.e. One of the main things I like with EMB is I can have 4 words or 4 paragraphs of text and it will resize the form appropriately.

You are saying it may not be easy to modify frmEMP_Popup to not halt code execution.

Would it be possible to modify frmModelessAutoCloseBox to automatically adjust sizing based on the content of .Prompt? (That form does not really have much content at all, except it uses some API calls for positioning. I'm not positive it needs those statements either.)

That would give me 95% of the functionality I am looking for by a different method.

 

[Marshall Brooks]

I'm back again with another example database. (Mainly I want to see if I'm overlooking something obvious.) This might be a similar issue to https://www.access-programmers.co.uk/forums/threads/show-a-progress-bar-on-form-loading.328507/

I ended up using the Autoclose Box with a delay of 10 seconds on my Is Web Browser Installed subroutine. It works well and looks like the verification will take 10 seconds (although it really only means the message will close at that time.)

I tried to do the same thing with PDF applications and it doesn't work very well - the example buttons are somewhat in worse to best order and I omitted the auto centering code, but normally everything would be centered.

• AutoClose Box - Looks like a standard MsgBox. After about 2 seconds the OK button shows 8 seconds remaining. After 2 more seconds, it counts down to 7, 6, and then it closes. It works, but not well.
• Modeless Box - Basically, I got rid of the timer so that it looks like a standard message box. Not good either. No indication that the code is running in the background and the box will be closed. Users will think they need to click okay to close the box before the check begins. And sometimes it is difficult to click okay on the box b/c it doesn't seem to have focus.
• Progress Bar Timer - I didn't have a lot of events for this, so I used @isladogs https://www.isladogs.co.uk/progress-bar/index.html frmPB2. I modified the form so that the pb started on form load and restarted if it reached 100% before the process completed. My idea was that if it worked, I could modify the form to look like my userform Pb and use the new form instead. It works well with the examples, or if you just open the form, but when I tried calling it, even with only 2 events, which should make it faster, it opens and sits there and then eventually gets to about 25% and closes b/c the procedure is complete.
• Simple Progress Bar - This just pops up my User Form Progress Bar at 50% complete and then closes it. But it gives the general idea.
• Better Progress Bar - This runs in 2 steps, so it does SOMEWHAT show progress. Might be the best I can do.

It seems like the main issue is not so much any of the methods as that the ApplicationisAvailable function is stealing focus and not allowing the progress bars and timers to update, but I don't know of a way to fix that.

Thanks in advance!!!

 

[MajP]

If I understand correctly you want something more like an hourglass feature than a progress meter. You can do an hourglass like

DoCmd.Hourglass method (Access)

Office VBA reference topic

learn.microsoft.com

The progress meters work when you have a loop and a known number of steps. These do not work when you have a single long running activity. But I modified that so it just shows a moving progress meter that repeats until the task is complete. Like a more obvious hourglass. See if this is what you are getting at. I just modified @isladogs example to not show the steps and keep looping until closed.

 

[Marshall Brooks]

@MajP - Thank you!!! - We are on the same page with this.

I put a good deal of work into this yesterday and today and attached is what I came up with.

Basically, it is the same idea that you came up with, but I modified @isladogs frmPB2 to not need an external module and to be able to be called from the main module.

So now I can use my userform for event-based code and I have an access form-based progress bar that looks as close as I can make it to identical to my userform-based progress bar.

I don't call the progress bar in the same way as @isladogs does on his website, but if you look at sub PB(), I'm able to call the progress bar with a custom caption and specify a time for the pb to complete - in this case 3.5 seconds. (If the procedure takes longer than 3.5 seconds, the pb starts over again, and if it takes less then 3.5 seconds the pb form closes before the pb reaches the end.)

The issue I have which might be beyond my control is that it seems like "IsApplicationAvailable" seems to steal focus, and I'm not sure if there is a way to avoid that.

On a fast system, it isn't really noticeable - the progress bar runs fairly smoothly to the end in 3 to 5 seconds.

On my home computer over VPN, the progress bar pops up, sits there for 2-3 seconds, smoothly scrolls to 50% sits there for 2-3 seconds, scrolls to the end and closes. I don't expect the forum to help with that, but it seems odd b/c only the backend is remote. i.e the front end is local, the PDF apps are local, all of this portion of the code should be running locally, etc.