I have a command button (Submit) on a form. Once clicked a function loops through 124 textboxes and inserts their values into a table.
The "looping & insering process" might take up to 30 seconds. During this process it appears -- to the user -- that nothing is happening... potentially making him/her click Submit again or even do something else unexpected.
Once the last question has been answered, it's already throwing a message box (part of another function though) which indicates the it might take 30 seconds or so.
At this time, however, I'd like to call an "hourglass" once SUBMIT has been pressed. The hourglass then should disappears once the "Thank you" msgbox has been clicked.
I added the "DoCmd.Hourglass True" & "DoCmd.Hourglass False" following the "Next intFrame" & the msgbox... unfortunately, this approach doesn't seem to work.
Does anyone know how to call the hourglass properly (and make it disappear) in the function listed below?
Thanks,
Tom
==========================
==========
==========
==========
==========
==
Private Sub Submit_Click()
'Set variables to determine current quarter (Q1, Q2, Q3, Q4)
Dim strField As String
'Set variables for INSERT statement
Dim strSQL As String
Dim intFrame As Integer, cFrame, cComment
Dim varTask As Variant
Dim ctl As Control
'Loops through the "outer-left not-visible" textboxes on frmSurvey
'They provide the foreign keys (KPILevel3FK) relationship once the Submit button has been executed
For intFrame = 1 To 124
varTask = Me("txt" & intFrame)
cFrame = Me("Frame" & intFrame).Value
cComment = Me("Comment" & intFrame).Value
'Inserts the FKs into tblKPIAnswers
'Based on current quarter the proper field (ScoreQ1, ScoreQ2, ScoreQ3, ScoreQ4) is selected
strSQL = "INSERT INTO tblKPIAnswers (KPILevel3FK, KPIParticipantsFK, Score, Comment) " & _
"VALUES (" & varTask & ", " & varID & ", " & cFrame & ", '" & FixUp(cComment) & "')"
CurrentDb().Execute strSQL, dbFailOnError
Next intFrame
'Shows hourglass until INSERT statement has been executed... which can take up to 20 seconds
DoCmd.Hourglass True
'Temporarily disables the warning message "Update are about to update 1 row()"
DoCmd.SetWarnings False
'Resets the "Update..." warning message
DoCmd.SetWarnings True
'Pops up a thank you message and closes the form
MsgBox "Thank you for completing the survey!", vbInformation, "Survey Status"
'Disables the hourglass once INSERT has been completed and user confirmed "Thank you" msgbox
DoCmd.Hourglass False
'Closes the current database
DoCmd.Quit
End Sub
Start Free Trial
