VBA Access - How to make Access wait.

[TraceWilliams]

GHudson,

Your pause function is the perfect solution for my issue. Do you know the command to tell Access to continue normal function during the DO LOOP?

Here is my issue, I am installing a control via function to remove all users from my database for maintenance. I send them a message before my application.quit command and used your pause loop to give them 90 seconds to finish and exit.

The issue is that Access freezes up during this time.

PLEASE HELP!


just put doevents somewhere inside the do loop

that should prevent access freezing

the trouble with kicking users off regardless, though - is what if they are doing something you would not want to just shut down .....

i think you can only do this in conjunction with a menu system that prevents them being able to launch any process, which needs some sort of system control flag.

 

[ZTAtZAU]

ah but why do you want it to wait?

are you opening a form and waiting for it to close - thats a different thing?

That is indeed a "different thing" and is exactly what I need help with! I realize this post is quite old but hoping Gemma-the-husky is still around to help with this.

I'm not interested in making access wait for a specified time but rather trying to make my access code module (which opens a pop up form) pause any further execution until I enter a value into the form and then close the form.

Any help with this would be much appreciated!
ZT

 

[Minty]

Open the pop up form as a modal dialog.
All code in the calling form is suspended until the pop up is closed.

And welcome to AWF!

 

[ZTAtZAU]

Open the pop up form as a modal dialog.
All code in the calling form is suspended until the pop up is closed.

And welcome to AWF!

Thanks Minty for your reply and your welcome!

"Calling form?" Not sure what that means but my pop up form is being opened from code in a "public function" located in a module rather than from code in another form.

I finally got the pop-up form to open and display the correct record based on the current record being processed by a do until loop in the code module. I have set both the pop-up and modal properties of the form to yes... Thinking that would pause the underlying code until I'm done with the pop-up form... but sadly it seems the underlying code continues to run.

In addition to pop-up and modal, You also mentioned "dialog"! Perhaps that's what I am missing? (i.e. The dialog part)???

ZT

 

[ZTAtZAU]

... Perhaps that's what I am missing? (i.e. The dialog part)??? ZT

Nope! I tried setting the pop-up form's border style to "Dialog" but that didn't seem to help. The form opens correctly but the underlying code continues to run!

Any suggestions are welcome! ZT

 

[Minty]

Hmm, can't say I have ever used a module function to open a pop up form. I would need to experiment with that to see if the code pause actually works or not.
They are normally associated with the calling form in some way.

Perhaps you could take a step back and explain why you needed a loop to open a pop up form on the underlying forms current record?

 

[MajP]

I have set both the pop-up and modal properties of the form to yes... Thinking that would pause the underlying code until I'm done with the pop-up form... but sadly it seems the underlying code continues to run.

That will not do it, by setting the form properties. You have to use the ACDIALOG constant of the windowmode of the docmd.open form method in order to stop the code execution in the calling form.

 

[ZTAtZAU]

Hmm, can't say I have ever used a module function to open a pop up form. I would need to experiment with that to see if the code pause actually works or not.
They are normally associated with the calling form in some way.

Perhaps you could take a step back and explain why you needed a loop to open a pop up form on the underlying forms current record?

I may yet take you up on your suggestion to take a step back and explain this but, at the moment, I'd prefer going forward with MajP's advice and try the acdialog route.

Thanks for your help! I'll keep you posted! ZT

 

[ZTAtZAU]

That will not do it, by setting the form properties. You have to use the ACDIALOG constant of the windowmode of the docmd.open form method in order to stop the code execution in the calling form.

Thanks much MajP! It took a bit of fiddling to get this new (to me) acdialog parameter to work correctly but it did seem to stop execution of the code; so I'm further along than I was before.

The new problem is that when my pop-up form opened, it would not let me enter the value I'm trying to enter. That is, the pop-up form, for some reason, appears to be read-only or locked.

I'm just guessing that the record is locked in the pop-up form because the same record is already the current record in the DAO.Recordset my code is looping through; so Access won't let me add the value directly to the underlying table! Does this make sense?

If so, is there anyway around this problem?

I've also been fiddling around with using an InputBox to enter the needed value and then pass the value (from the InputBox) back into my code to "set the value" there. I don't really care for the format/generic look of the InputBox though; and would much prefer to use my own pop-up form; if possible!

Any suggestions are welcome! ZT

 

[Minty]

Maybe now is the time to describe what you are trying to achieve from a top level?

In fact start a new thread - as this has all been tacked on to an old one and is pretty off the original topic.

 

[ZTAtZAU]

Maybe now is the time to describe what you are trying to achieve from a top level?

In fact start a new thread - as this has all been tacked on to an old one and is pretty off the original topic.

Thanks much Minty! I do appreciate your help! However, because of the "read-only" problem I was having with my custom pop-up form, I decided to go ahead and fool around with the InputBox instead of the custom form.

The InputBox does indeed pause the execution of my code until a value is entered and then it was pretty straightforward to pass that value back into the code where it was needed.

I am happy to report that, while I'm not crazy about the look of the InputBox, (rather generic in my opinion), everything is now working as intended.

ZT

 

[MajP]

The new problem is that when my pop-up form opened, it would not let me enter the value I'm trying to enter. That is, the pop-up form, for some reason, appears to be read-only or locked.

I'm just guessing that the record is locked in the pop-up form because the same record is already the current record in the DAO.Recordset my code is looping through; so Access won't let me add the value directly to the underlying table! Does this make sense?

No, something else is likely going on. If you make multiple connections to a table such as a bound form and an open recordset, it should not lock anything. You are more likely to get a message something like "this record cannot be saved it has been updated by another user"

I would think instead of an input box you would use openargs.
https://docs.microsoft.com/en-us/office/vba/api/access.form.openargs

 

[Minty]

I'm with MajP - Input boxes are clunky, ugly and you have no control over what is entered into them.

You should use values from the calling form, as MajP said OpenArgs is a good way of passing things around.

 

[MajP]

Maybe now is the time to describe what you are trying to achieve from a top level?
In fact start a new thread - as this has all been tacked on to an old one and is pretty off the original topic.

I am with Minty on this one. I think what you are describing is a pretty routine functionality, and there is no need for code to pause execution. If the pop up is read only then there is something else going on and you should be able to overcome that. We just need more details on what you are doing.

 

[ZTAtZAU]

No, something else is likely going on. If you make multiple connections to a table such as a bound form and an open recordset, it should not lock anything. You are more likely to get a message something like "this record cannot be saved it has been updated by another user"

I would think instead of an input box you would use openargs.

Thanks MajP! I appreciate your help with the ACDIALOG parameter! That worked fine to pause my code. As for the read-only problem, do you have any idea what might be going on with that? I tried adding another docmd.OpenForm parameter (ACFORMEDIT) but that didn't help!

I do really prefer the look of my custom pop-up form over the "bland" InputBox. I do use a couple of other InputBoxes in this app for entering "security passwords". My biggest complaint with the InputBox in this instance is the long horizontal input textbox when I only want to enter a 2 or 3 digit number.

I've never before familiarized myself with openargs but I'll heed your advice and look into that. Thanks for the link!

I'm with MajP - Input boxes are clunky, ugly and you have no control over what is entered into them.

You should use values from the calling form, as MajP said OpenArgs is a good way of passing things around.

Thanks Minty! I'm with you on the clunky & ugly! But hey! It's hard to give up right now on what I have working. I think a well constructed message prompt in the InputBox will go a long way toward minimizing the chances of someone entering anything other than what's intended to go in the inputBox.

Thanks again to you both! ZT

 

[MajP]

As for the read-only problem, do you have any idea what might be going on with that?

Without more details it is hard to tell, but there are lots of things that could cause it.
1) You set allow edits / additions to false
2) opened it with a non editable query, or somehow made the query non update able.
3) Filtered it to no records

Can you show code to pop open the form? Can you describe the forms recordsource?

If you want and not proprietary then post the db.

 

[ZTAtZAU]

RE: My Popup Frm being "read-only"

Without more details it is hard to tell, but there are lots of things that could cause it.
1) You set allow edits / additions to false
2) opened it with a non editable query, or somehow made the query non update able.
3) Filtered it to no records
Can you show code to pop open the form? Can you describe the forms recordsource?

Here ya' go MajP...

Without more details it is hard to tell, but there are lots of things that could cause it.
1) You set allow edits / additions to false

Not that I know of??? In Design View:
Form Properties:
Data Entry = No
Allow Additions = No
Allow Deletions = No
Allow Edits = Yes
Allow Filters = Yes

MyValuebox Properties
Enabled=Yes
Locked=No

2) opened it with a non editable query, or somehow made the query non update able.
Not that I know of??? Form is bound to the underlying table with a simple Select Query that returns all fields in the underlying table

3) Filtered it to no records
I don't think so! The Form opens normally and displays the desired record based on the value of an ID field passed from the same ID field in the current record of the codes DAO.RecordSet. The RecordSet is based on a similar query of the same underlying table.

Can you show code to pop open the form?
Dim DocName As String
Dim LinkCriteria As String
LinkCriteria = "[MyID]=" & MySet![MyID]
DocName = "MyPopupFrm"
DoCmd.OpenForm DocName, , , LinkCriteria, acFormEdit, acDialog

Can you describe the forms recordsource?
SELECT DISTINCTROW MyTbl.*
FROM MyTbl;

Do you see anything here that could cause the popup form to be locked or read-only?

 

[Minty]

Yup

SELECT [COLOR="Red"]DISTINCTROW [/COLOR]MyTbl.*
FROM MyTbl;

You are only returning unique records, which effectively groups them making the recordsource not editable .

As hint for future debugging of similar issues, simply paste your rowsource into the query editor and see if you can edit the data.

 

[isladogs]

Try removing DISTINCTROW. Not needed if you only have one table.
However I don't think that will solve it!

 

[isladogs]

Hi Minty
DISTINCT certainly makes queries read only.
DISTINCTROW should not do so...though its superfluous here