Disablling a Command Button for 15 seconds. 1

[Speiro1]

Disabling a Command Button for 15 seconds.

Hi, I am learning Access/SQL and started building forms as a front end. I have a command button that runs the following code.

DoCmd.SetWarnings False
DoCmd.OpenQuery "qrAllocationsPhase1Update"
DoCmd.OpenQuery "qrtemptable"
DoCmd.OpenQuery "qrBeginingbalance"
DoCmd.OpenQuery "qrNextBreakUpdateTrue"
DoCmd.OpenQuery "qrTaxProvisionUpdate"
DoCmd.OpenQuery "qrRevalTaxUpdate"
DoCmd.OpenQuery "qrRevalTaxPhase2Update"
DoCmd.OpenQuery "qrTaxStuffingGainsUpdate"
DoCmd.OpenQuery "qrTaxStuffingLossesUpdate"
DoCmd.OpenQuery "qrTemporaryTaxAllocationsEco"
DoCmd.OpenQuery "qrTemporaryTaxAllocationsPR"
DoCmd.OpenQuery "qrTemporaryTaxAllocationsNR"
DoCmd.OpenQuery "qrTemporaryTaxAllocations2Eco"
DoCmd.OpenQuery "qrTemporaryTaxAllocations2PR"
DoCmd.OpenQuery "qrTemporaryTaxAllocations2NR"
DoCmd.OpenQuery "qrTaxAllocationEcoUpdate"
DoCmd.OpenQuery "qrTaxAllocationNRUpdate"
DoCmd.OpenQuery "qrTaxAllocationPRUpdate"
DoCmd.OpenQuery "qrTemporaryTaxAllocationsEcoDelete"
DoCmd.OpenQuery "qrTemporaryTaxAllocationsPRDelete"
DoCmd.OpenQuery "qrTemporaryTaxAllocationsNRDelete"
DoCmd.OpenQuery "qrTemporaryTaxAllocations2EcoDelete"
DoCmd.OpenQuery "qrTemporaryTaxAllocations2PRDelete"
DoCmd.OpenQuery "qrTemporaryTaxAllocations2NRDelete"
DoCmd.OpenQuery "qrAllocationPhase1Delete"
DoCmd.OpenQuery "qrTaxBasisUpdate"
DoCmd.SetWarnings True
Me![fmBreakDateOpenClose]![Allocated] = True

It takes about 10-15 seconds to run the code and have found that if the user pushes the button a second time, before the first cycle is done it ruins all the data because it starts doubling up. Is there a way to disable the command button for 15 seconds so this does not happen? I have searched the boards and found something called Twait, shown below, but I have no idea how to apply it or if it is even the correct way to go. If you look at my code and laugh that's fine just help me I need it.

TWait = Time
TWait = DateAdd("s", 15, TWait)
Do Until TNow >= TWait
TNow = Time
Loop

 

[dhookom]

I would try something like:

Me.theCommandButton.Enabled = False
DoCmd.SetWarnings False
DoCmd.OpenQuery "qrAllocationsPhase1Update"
DoCmd.OpenQuery "qrtemptable"
DoCmd.OpenQuery "qrBeginingbalance"
DoCmd.OpenQuery "qrNextBreakUpdateTrue"
DoCmd.OpenQuery "qrTaxProvisionUpdate"
DoCmd.OpenQuery "qrRevalTaxUpdate"
DoCmd.OpenQuery "qrRevalTaxPhase2Update"
DoCmd.OpenQuery "qrTaxStuffingGainsUpdate"
DoCmd.OpenQuery "qrTaxStuffingLossesUpdate"
DoCmd.OpenQuery "qrTemporaryTaxAllocationsEco"
DoCmd.OpenQuery "qrTemporaryTaxAllocationsPR"
DoCmd.OpenQuery "qrTemporaryTaxAllocationsNR"
DoCmd.OpenQuery "qrTemporaryTaxAllocations2Eco"
DoCmd.OpenQuery "qrTemporaryTaxAllocations2PR"
DoCmd.OpenQuery "qrTemporaryTaxAllocations2NR"
DoCmd.OpenQuery "qrTaxAllocationEcoUpdate"
DoCmd.OpenQuery "qrTaxAllocationNRUpdate"
DoCmd.OpenQuery "qrTaxAllocationPRUpdate"
DoCmd.OpenQuery "qrTemporaryTaxAllocationsEcoDelete"
DoCmd.OpenQuery "qrTemporaryTaxAllocationsPRDelete"
DoCmd.OpenQuery "qrTemporaryTaxAllocationsNRDelete"
DoCmd.OpenQuery "qrTemporaryTaxAllocations2EcoDelete"
DoCmd.OpenQuery "qrTemporaryTaxAllocations2PRDelete"
DoCmd.OpenQuery "qrTemporaryTaxAllocations2NRDelete"
DoCmd.OpenQuery "qrAllocationPhase1Delete"
DoCmd.OpenQuery "qrTaxBasisUpdate"
DoCmd.SetWarnings True
Me![fmBreakDateOpenClose]![Allocated] = True 
Me.theCommandButton.Enabled = True

Duane
Hook'D on Access
MS Access MVP

 

[Speiro1]

Thank you so much, you are a genius.

 

[kjv1611]

Speiro1,

The reason that code works is that you are disabling the button, and then not showing the button again until the code has completely run.

I would add one thing to the form, myself, just in case the code behind the button ever has issues and breaks in the middle.

Private Sub Form_Load()
     Me.theCommandButton.Enabled = True
End Sub

Private Sub theCommandButton_Click()
     [green]'dhookom's code here[/green]
End Sub

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[missinglinq]

You should also keep in mind that code-wise, Access is asynchronous, which is to say, if given a series of Commands, it starts to execute one, moves on to the next one and starts executing it, and so forth. It doesn't wait for the first Command to be completed before starting the second one, and this can cause timing problems. Notice that I said can, not will.

An example would be exactly what you're doing here. The following VBA code

DoCmd.OpenQuery "QueryA"
DoCmd.OpenQuery "QueryB"
DoCmd.OpenQuery "QueryC"

will immediately run all three, not waiting for one to finish executing before starting the next one. The answer to halting the code in this type of situation is to use DoEvents.

DoCmd.OpenQuery "QueryA"
DoEvents
DoCmd.OpenQuery "QueryB"
DoEvents
DoCmd.OpenQuery "QueryC"

DoEvents returns control to Windows, allowing QueryA to complete running before starting to run QueryB. It then allows QueryB to finish running before starting QueryC.

If you have no problems, running this series of Queries, that's fine! Just keep the above in mind. The run time of Queries can increase with time, as more Records are added to the underlying Tables, and what runs fine today may not run fine tomorrow, when the data increases.

The Missinglinq

Richmond, Virginia

The Devil's in the Details!

 

[kjv1611]

That's some really good info, Missinglinq. Thanks for sharing! I don't have many, if any, processes right now where this would matter, myself, but I have had some in the past where If I'd of realized the timing issue (I always just assumed.. or maybe forgot.. that the events would always trigger Event... wait until finished, and trigger next event... at least it always seemed that way).. I wonder if there is a way to test it.

I use the DoEvents regardless in longer processes, b/c it seems to help things from sticking/freezing at times.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57