pausing code until button in form is pressed

[Laoballer]

I'm working on an access program where the user can choose multiple
excel files to get imported. My code takes the excel data then loads
it into a temporary table where it will be checked to make sure
critical fields follow a standard format. If the data in the
temporary table meets all criteria the data in it gets appended to
another format. However if there are fields that don't have the
correct formatted data a form is opened with the incorrect field
highlighted to indicate fields that need changing. Once fields have
been changed and meet the criteria a command button is activated that
allows the user to append that data to the final table. All of this
is in a loop. What I would like to do is pause the loop when the form
to change the data opens until the user makes all the necessary
changes and then loads the data to the final table. Hope the
description of my problem is clear. How would I do this?

Thanks,

 

[Allen Browne]

Any chance you could handle this in 3 stages?

The first one gets the data, and calls the validation code which gives any
warnings. The user can then fix any issues and click the 2nd button to run
the validation code again. If validation is passed, the 3rd button is
enabled, and the user can click that to perform the input into the real
tables.

 

[John Spencer (MVP)]

If you can't do it in stages as Allen suggested, you might try opening the
form in dialog mode. That should stop any code in the calling function until
the form is closed. The problem with this is the user could just close the
form and then the calling code would continue to run.

You could put use a loop to force the user to fix the records. The loop would
have to revalidate the imported records and continue to call the form until
the records were all fixed.

The loop might look like the following

While fValidateFunction = False
DoCmd.OpenForm "FixTheStupidErrors",WindowMode:=acDialog
Wend

Of course, your users would not be happy if they just wanted to exit the
process. So you might have to work something into the code to handle that
eventuality.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Laoballer]

If you can't do it in stages as Allen suggested, you might try opening the
form in dialog mode.  That should stop any code in the calling functionuntil
the form is closed.  The problem with this is the user could just closethe
form and then the calling code would continue to run.

You could put use a loop to force the user to fix the records.  The loop would
have to revalidate the imported records and continue to call the form until
the records were all fixed.

The loop might look like the following

While fValidateFunction = False
    DoCmd.OpenForm "FixTheStupidErrors",WindowMode:=acDialog
Wend

Of course, your users would not be happy if they just wanted to exit the
process. So you might have to work something into the code to handle that
eventuality.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I opened the form in dialog mode as John has suggested and have
created two command buttons. A load button that is only enabled when
all necessary errors are fixed in the data which will load the data
into the final table then close the form to continue on to the next
file. And a cancel button which will discard all data currently being
checked and then close the form to continue the loop and onto the next
file. Does this sound like a reasonable algorithm?

Thanks

 

[Allen Browne]

That sounds reasonable if you really do need to pause some other running
code until the user has added or cancelled.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

If you can't do it in stages as Allen suggested, you might try opening the
form in dialog mode. That should stop any code in the calling function
until
the form is closed. The problem with this is the user could just close the
form and then the calling code would continue to run.

You could put use a loop to force the user to fix the records. The loop
would
have to revalidate the imported records and continue to call the form
until
the records were all fixed.

The loop might look like the following

While fValidateFunction = False
DoCmd.OpenForm "FixTheStupidErrors",WindowMode:=acDialog
Wend

Of course, your users would not be happy if they just wanted to exit the
process. So you might have to work something into the code to handle that
eventuality.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I opened the form in dialog mode as John has suggested and have
created two command buttons. A load button that is only enabled when
all necessary errors are fixed in the data which will load the data
into the final table then close the form to continue on to the next
file. And a cancel button which will discard all data currently being
checked and then close the form to continue the loop and onto the next
file. Does this sound like a reasonable algorithm?

Thanks