Frozen Form after a procedure call

D

dan

I set up command button on a form with the following click
event (see below). I convert a working macro to VBA and
copied it from the function to a sub procedure and
modified it. When I step through the procedure it works
fine up until the end. Instead of ending the sub it enters
the error hander but does not generate an error in a
msgbox. The form is frozen on the button I clicked and the
only way to get out of it is to click on the database
window icon on the task bar and then on the form icon in
the task bar.

There must be an error in my code but it compiles fine.
Does anyone have a suggestion?
Thanks for your help.

Private Sub Import_Click()
On Error GoTo Err_import_click

' Turn off warning dialog boxes
DoCmd.SetWarnings False
' Turn off echo
DoCmd.Echo False, ""
' Delete last months import
DoCmd.OpenQuery "Delete from Import Month Scantron",
acNormal, acEdit
' Import Delimited Text file
h:\shared\personne\access\carpool.txt
DoCmd.TransferText acImportDelim, "CARPOOL Import
Specification", "Import Month
Scantron", "i:\shared\personne\access\carpool.txt",
False, ""
DoCmd.Requery ""
If (Forms![Main Form]!CurrentMonth < Forms![Main Form]!
ImportMonth) Then

' Delete records that have currupted emp no data
DoCmd.OpenQuery "importmonthfilter", acNormal,
acEdit
' Delete records from Prior to Last Month Scantron
Records
DoCmd.OpenQuery "Delete prior to last months
scantron Records", acNormal, acEdit
' Append to Prior to Last Month Scantron from last
month
DoCmd.OpenQuery "Append to Prior to Last Month
Scantron", acNormal, acEdit
' Delete from Last Month Scantron
DoCmd.OpenQuery "Delete from Last Month Scantron",
acNormal, acEdit
' Append to Last Month Scantron from current
DoCmd.OpenQuery "Append to Last Month Scantron",
acNormal, acEdit
' Delete from Current Month Scantron
DoCmd.OpenQuery "Delete from Current Month
Scantron", acNormal, acEdit
' Append from import table to current month
scantron
DoCmd.OpenQuery "Append from import table to
current month", acNormal, acEdit
' Prior to last month scantron table
DoCmd.Close acTable, "Prior to Last Month Scantron"
' Delete Scantron Summary Records
DoCmd.OpenQuery "Delete Scantron Summary Records",
acNormal, acEdit
' Import h:\shared\personne\access\Carsum.txt to
scantron summary update
DoCmd.TransferText acImportDelim, "CARSUM Import
Specification", "Scantron Summary
Update", "i:\shared\personne\access\Carsum.txt", False, ""
' Delete records that have a currupt emp no data
DoCmd.OpenQuery "importsummaryfilter", acNormal,
acEdit
' Message to user that import is complete
Beep
MsgBox "Monthly Scantron Import Completed",
vbInformation, "Scantron Import"
' Update Prior Months Total Points on Master
Record from total points
DoCmd.OpenQuery "update prior months total
points", acNormal, acEdit
' Delete month end points (update to O)
DoCmd.OpenQuery "Delete month end points",
acNormal, acEdit
' run monthly master file update query
DoCmd.OpenQuery "Monthly Master File Update",
acNormal, acEdit
' Message box to user that import is complete
Beep
MsgBox "Master File Update Completed",
vbInformation, "Master File Udate Macro"

Else
' The data you are attempting to import is the same or
older than the data in current month table. You can only
import data with a month and year that is greater than
month and year in the current month table.
Beep
MsgBox "The data you are attempting to import is the
same or older than the data in current month table. You
can only import data with a month and year that is greater
than month and year in the current month table.",
vbExclamation, "Data conflict"
End If


Exit_import_Click:
Exit Sub

Err_import_click:
MsgBox Error$ & " " & Err.Number & " " &
Err.Description
Resume Exit_import_Click
End Sub
 
R

Ron Weiner

Dan

I see where you turn the Echo Off, but not where you turn the Echo back on.
When any error occurs and the Echo is off it will appear that the form is
Frozen. If it were me I'd loose the DoCmd.Echo False until I had the
Routine completely debugged. Also make sure that you turn on the Echo at
the end of the routine AND in the Error handler when you are ready to put
the routine into production.

Ron W
dan said:
I set up command button on a form with the following click
event (see below). I convert a working macro to VBA and
copied it from the function to a sub procedure and
modified it. When I step through the procedure it works
fine up until the end. Instead of ending the sub it enters
the error hander but does not generate an error in a
msgbox. The form is frozen on the button I clicked and the
only way to get out of it is to click on the database
window icon on the task bar and then on the form icon in
the task bar.

There must be an error in my code but it compiles fine.
Does anyone have a suggestion?
Thanks for your help.

Private Sub Import_Click()
On Error GoTo Err_import_click

' Turn off warning dialog boxes
DoCmd.SetWarnings False
' Turn off echo
DoCmd.Echo False, ""
' Delete last months import
DoCmd.OpenQuery "Delete from Import Month Scantron",
acNormal, acEdit
' Import Delimited Text file
h:\shared\personne\access\carpool.txt
DoCmd.TransferText acImportDelim, "CARPOOL Import
Specification", "Import Month
Scantron", "i:\shared\personne\access\carpool.txt",
False, ""
DoCmd.Requery ""
If (Forms![Main Form]!CurrentMonth < Forms![Main Form]!
ImportMonth) Then

' Delete records that have currupted emp no data
DoCmd.OpenQuery "importmonthfilter", acNormal,
acEdit
' Delete records from Prior to Last Month Scantron
Records
DoCmd.OpenQuery "Delete prior to last months
scantron Records", acNormal, acEdit
' Append to Prior to Last Month Scantron from last
month
DoCmd.OpenQuery "Append to Prior to Last Month
Scantron", acNormal, acEdit
' Delete from Last Month Scantron
DoCmd.OpenQuery "Delete from Last Month Scantron",
acNormal, acEdit
' Append to Last Month Scantron from current
DoCmd.OpenQuery "Append to Last Month Scantron",
acNormal, acEdit
' Delete from Current Month Scantron
DoCmd.OpenQuery "Delete from Current Month
Scantron", acNormal, acEdit
' Append from import table to current month
scantron
DoCmd.OpenQuery "Append from import table to
current month", acNormal, acEdit
' Prior to last month scantron table
DoCmd.Close acTable, "Prior to Last Month Scantron"
' Delete Scantron Summary Records
DoCmd.OpenQuery "Delete Scantron Summary Records",
acNormal, acEdit
' Import h:\shared\personne\access\Carsum.txt to
scantron summary update
DoCmd.TransferText acImportDelim, "CARSUM Import
Specification", "Scantron Summary
Update", "i:\shared\personne\access\Carsum.txt", False, ""
' Delete records that have a currupt emp no data
DoCmd.OpenQuery "importsummaryfilter", acNormal,
acEdit
' Message to user that import is complete
Beep
MsgBox "Monthly Scantron Import Completed",
vbInformation, "Scantron Import"
' Update Prior Months Total Points on Master
Record from total points
DoCmd.OpenQuery "update prior months total
points", acNormal, acEdit
' Delete month end points (update to O)
DoCmd.OpenQuery "Delete month end points",
acNormal, acEdit
' run monthly master file update query
DoCmd.OpenQuery "Monthly Master File Update",
acNormal, acEdit
' Message box to user that import is complete
Beep
MsgBox "Master File Update Completed",
vbInformation, "Master File Udate Macro"

Else
' The data you are attempting to import is the same or
older than the data in current month table. You can only
import data with a month and year that is greater than
month and year in the current month table.
Beep
MsgBox "The data you are attempting to import is the
same or older than the data in current month table. You
can only import data with a month and year that is greater
than month and year in the current month table.",
vbExclamation, "Data conflict"
End If


Exit_import_Click:
Exit Sub

Err_import_click:
MsgBox Error$ & " " & Err.Number & " " &
Err.Description
Resume Exit_import_Click
End Sub
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Posting a dataset to a SQL table using VBA 0
suppress macro messages 1
MEMORY ERROR When Running Code 1
Resume Next versus GoTo 5
Updating only changed records 7
Refresh Subform from Main form button 1
Excel file won't import unless open 1
Suppressing Access error messages 1

Top