is this code crapping out on me?

[bpaquette]

Hi everyone. The code below is attached to the onload event of my switchboard. the db hides the database window and has shiftkey bypass and only allows certain users in (moderate security measures).

flow of the function goes a bit like this:

verify user is authorized, if not close database

open form that references table holding dates each of 5 different reports was last sent

check each 'last sent' date against today's date, if difference is greater than a predefined variable (different for each report), then send it and increment a counter variable

repeat this for five different reports (give or take five)

give message saying either none or some reports were sent

end sub


anyway, i get the first msg box, the one that says it's going to check to see if reports needed to be sent, but then it just freezes. now the first report, rptSIK, needs to be sent because it has been more than 7 days. this wasn't happening yesterday, so i can only conclude that this has something to do with it.

anyway, here's the code:

Private Sub Form_Load()

Dim reports As Integer
'reports counts how many reports are sent for end-of-sub msg purposes
reports = 0

'don't want unauthorized users getting in!  i have a password protected shiftbypass function (thanks ghudson!) to further secure the db

Select Case Environ("Username")
Case "PaquetteBM", "RusselYR", "UlyakJJ", "ThompsonSL", "DunnSW", "WatsonHL", "RodgersCJ"

Case Else
MsgBox ("This database is for use only by the authorized user.  If you believe this to be you, please contact the Orderly Room at 486-7425.")

DoCmd.Quit acQuitSaveNone
End Select




'This opens a form with a few fields that need to be referenced for the emailing commands
'the form contains fields from a table that stores the dates these reports are last sent, thus is able to calculate how long it has been

DoCmd.OpenForm "frmDateLastSent", , , , , acHidden
MsgBox ("Welcome!  Access will now check to see if any reports are due to be sent, and send them if they are.  Please stand by, as this may take a few moments.  If any prompts request access to your outlook email functions; please click Yes")




'This function sends the proper report if it has been seven days since last send
If (DateDiff("d", Now, Forms!frmDateLastSent!LastSentDFAC) <= -7) Then
sent = True
reports = reports + 1

DoCmd.SendObject acSendReport, "rptSIK", acFormatRTF, "EMAIL ADDRESSES (had to remove for obvious reasons)", _
, , "Current SIK Listing", "Hello, Attached you will find the current listing of valid meal card holders for the 435th MDS.  Thank you  **This document contains FOR OFFICIAL USE ONLY (FOUO) and/or Privacy Act information which must be protected or removed prior to further disclosure.  (Reference: AFM 37-126, FOUO and AFI 37-132, Privacy Act)", 0

Forms!frmDateLastSent!LastSentDFAC = Date

End If


If (DateDiff("d", Now, Forms!frmDateLastSent!LastSentBirthdayRoster) <= -30) Then
sent = True
reports = reports + 1


DoCmd.SendObject acSendQuery, "qryBirthDay", acFormatXLS, "EMAIL ADDRESSES (had to remove for obvious reasons", _
, , "Current Birthday Listing", "Hello, Attached you will find the current listing of people with birthdays in this and the next month.  Thank you.  **This document contains FOR OFFICIAL USE ONLY (FOUO) and/or Privacy Act information which must be protected or removed prior to further disclosure.  (Reference: AFM 37-126, FOUO and AFI 37-132, Privacy Act)", 0

Forms!frmDateLastSent!LastSentBirthdayRoster = Date

End If

If (DateDiff("d", Now, Forms!frmDateLastSent!LastSentOhara) <= -30) Then
sent = True
reports = reports + 1
DoCmd.SendObject acSendQuery, "qryOhara", acFormatXLS, "EMAIL ADDRESSES (had to remove for obvious reasons", _
, , "Current 435 MDS Personnel listing", "Hello, Attached you will find the current listing of 435TH MDS members.  Thank you.  **This document contains FOR OFFICIAL USE ONLY (FOUO) and/or Privacy Act information which must be protected or removed prior to further disclosure.  (Reference: AFM 37-126, FOUO and AFI 37-132, Privacy Act)", 0

Forms!frmDateLastSent!LastSentOhara = Date

End If

If (DateDiff("d", Now, Forms!frmDateLastSent!lastsentbuck) <= -30) Then
sent = True
reports = reports + 1
DoCmd.SendObject acSendQuery, "qryKatherineBuck", acFormatXLS, "EMAIL ADDRESSES (had to remove for obvious reasons", _
, , "Current 435 MDS Personnel listing", "Hello, Attached you will find the current listing of 435TH MDS members.  Thank you.  **This document contains FOR OFFICIAL USE ONLY (FOUO) and/or Privacy Act information which must be protected or removed prior to further disclosure.  (Reference: AFM 37-126, FOUO and AFI 37-132, Privacy Act)", 0

Forms!frmDateLastSent!lastsentbuck = Date

End If

If (DateDiff("d", Now, Forms!frmDateLastSent!lastsentgeckeis) <= -14) Then
sent = True
reports = reports + 1
DoCmd.SendObject acSendQuery, "qryGeckeis", acFormatXLS, "EMAIL ADDRESSES (had to remove for obvious reasons", _
, , "Current 435 MDS Personnel listing", "Hello, Attached you will find the current listing of 435TH MDS members.  Thank you.  **This document contains FOR OFFICIAL USE ONLY (FOUO) and/or Privacy Act information which must be protected or removed prior to further disclosure.  (Reference: AFM 37-126, FOUO and AFI 37-132, Privacy Act)", 0

Forms!frmDateLastSent!lastsentgeckeis = Date

End If

'Status msgbox
If reports > 0 Then
MsgBox ("Thank you for your patience! I sent " & reports & " report(s).")
Else
MsgBox ("Thank you for your patiences!  No reports needed to be sent at this time.  Please remember to open the database at least once a day, so reports can be sent out in a timely manner.")
End If





End Sub

did i fudge something up?

thanks guys!

ps, sorry for the long lines of code, i have hi res on my screen so it usually almost all fits and i find those underscore line continuers cumbersome