Runtime error when i open a form (1 Viewer)

[vbaInet]

Let me see your db. I just want to see where error handling isn't necessary.

 

[rick roberts]

i have attached a full empty version

 

[gemma-the-husky]

rick

having said everything about error handling - you MUST also be getting the same error in a normal (ie not runtime version) - but in the full versions, access gives you a chance to fix the problem. (the debug/end msgbox)

so what happens with the full version? duplicate everything you are doing in the problem version, and see what happens.

 

[vbaInet]

So once you've figured out what the problem is like gemma-the-husky has asked, you will need error handling on all your subs/functions. I've just a look. You've got some functions that call other functions and definitely a recipe for error. Of all your functions/subs they were only two that didn't really need to be error handled.

 

[rick roberts]

husky
everything works fine in the normal version the error only occurs in runtime - thats why i find it hard to pinpoint where its wrong since as you say if the error occured in the normal versiopn at least id get a messasge and the problematic line highlighted

 

[vbaInet]

Is the error consistent and do you remember the sequence of events to reproduce the problem?

 

[rick roberts]

the error occurs when a letter button is clicked and there is a corresponding tickbox ticked - this invokes a message (to stop a duplicate letter being sent) when the message box is closed the error message appears and the program shuts down - it happens for all four boxes

 

[vbaInet]

Let's see the whole code that performs these steps. Wrapped in code tags too.

 

[rick roberts]

this is where the error occurs

Private Sub cmdLetter1_Click()
'Send a message if tickbox is already ticked
On Error GoTo PROC_ERR
Me.Refresh
If CheckLetter1 = True Then
MsgBox "The tickbox is ticked which indicates that a letter or email has been sent." _
& Chr(13) & "If you wish to send another please untick the box. ", vbOKOnly, Warning
End
End If

'-----Error happens here-----

'Check whether to email or post
Email.SetFocus
'Stop error if email isnt sent
On Error GoTo Error

'Email
If Email.Text <> "" Then
SendEmail 'Function
Error:
Resume Next

Else
'Post
'Had to use this routine to make the report print in colour
Dim stDocName As String
stDocName = "rptCustLetter1"
DoCmd.OpenReport stDocName, acViewPreview
With Reports(rptCustLetter1).Printer
.ColorMode = acPRCMColor
End With 'end of colour enforcement

DoCmd.OpenReport "rptCustLetter1", acViewNormal
DoCmd.Close
End If
'Tick to indicate letter is sent
SentStage1 = Date
CheckLetter1.Value = True

Calculator 'Function
DoCmd.Restore

PROC_ERR_EXIT:
Exit Sub

PROC_ERR:
MsgBox Err.Number & ": " & Err.Description
'Resume PROC_ERR_EXIT

End Sub

it only happens if the box is ticked and the message is displayed
if unticked the routine works fine and an email is sent or letter printed

 

[vbaInet]

They were quite a number of things that were wrong with your code. You can spot the difference in your own time.

Private Sub cmdLetter1_Click()
On Error GoTo PROC_ERR
 
    ' Save any changes
    If Me.Dirty Then Me.Dirty = False
 
    ' Display a message and quit sub if tickbox is already ticked
    If Me.CheckLetter1.Value = True Then
        MsgBox "The tickbox is ticked which indicates that a letter or email has been sent." & _
                Chr(13) & "If you wish to send another please untick the box. ", vbOKOnly, Warning
        Exit Sub
    End If
 
    ' Email if Email field is ticked, otherwise print letter
    If Len(Me.[Email].Value & vbNullString) <> 0 Then
        SendEmail 'Function
    Else
        ' Had to use this routine to make the report print in colour
        DoCmd.OpenReport "rptCustLetter1", acViewPreview
 
        Reports(rptCustLetter1).Printer.ColorMode = acPRCMColor
 
        DoCmd.OpenReport "rptCustLetter1", acViewNormal
        DoCmd.Close
    End If
 
    ' Tick to indicate letter is processed
    Me.SentStage1.Value = Date
    Me.CheckLetter1.Value = True
 
    Calculator 'Function
    DoCmd.Restore
 
PROC_ERR_EXIT:
    Exit Sub
 
PROC_ERR:
    MsgBox Err.Number & ": " & Err.Description
    Resume PROC_ERR_EXIT
End Sub

I would mention one thing though, Email is poor choice for a field or control name hence, I've had to enclose it in square brackets.

Run it with the code above and if you get more errors, we will need to see the other functions that you've called, i.e. Calculator and SendMail.

 

[rick roberts]

thats done the trick - i changed the code for all the other buttons too - i guess i was being too vague?? thanks very much for your help in this matter - it was becoming very frustrating - thank you all
one last question -- can anyone figure out a reason why the email routine only seems to work on my computer and no other - even though the other 2 computers ive tried it on both use outlook?
thanks again vbainet

 

[vbaInet]

You're welcome! But I hope you've error handled all your other routines

When you say it doesn't work what do you mean? It's throwing an error or nothing happens?

 

[rick roberts]

it gives that momentary message which i can only presume says its outputting the email (it only appears for a split second) and the routine seems to work fine -- except that the email never gets sent and isnt in outlook at all
im working on the other error handlers and wont make that mistake again
thanks

 

[vbaInet]

Have you tried stepping through the code to see what each step is doing?

Can we see your SendEmail function.

 

[rick roberts]

i havent tried much yet since it only happens on other peopels computers im going to try using my own email to send on another comp and see what happens - i onlly asked in case there was a simple obvious answer
thanks again

Public Function SendEmail()

Dim appOutLook As Object
Dim MailOutLook As Object

'create the report temporarily
DoCmd.OutputTo acOutputReport, "rptCustLetter1", acFormatRTF, "d:\data files\Report1.rtf", False
'assign our object references
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)
'set the recipient list
With MailOutLook
.To = Email
'set the subject
.Subject = "Outstanding Payment"
'set the body text
.body = "The attachment is a reminder invoice for fees for ID No." & CustID & Chr(13) & _
"at" & comboCourse & Chr(13) & _
"started on " & StartDate & Chr(13) & _
"Please open the attached file"
'add the reports we created
.attachments.Add "d:\data files\Report1.rtf"
'send the email
.Send
End With
'tidy up..
'get rid of our object references
Set appOutLook = Nothing
Set MailOutLook = Nothing
'delete our temporary files
Kill "d:\data files\Report1.rtf"

Calculator

End Function

 

[vbaInet]

Nope, nothing obvious. I only asked to see your SendEmail function to check whether you are late binding Outlook.Application, which you are so there's no problem there.

You could check to see if the user can send mail with an attachment via outlook normally.

Also, I'm guessing your db is split?

 

[rick roberts]

sorry i dont know what you mean by - split?

 

[vbaInet]

Front end on each user's machine, Back end on the server.

 

[rick roberts]

if i understand you correctly - that isnt the case - i have the program on an external drive and use that as the location of the program for all computers- im not dealing with an office network - ive developed the program at home and have tested it from there but when i take the same program to the intended location i find that the emails do not send
the eventual intention is to create the package and install it on their computer for them to use

 

[vbaInet]

That's where the problem is, your db is breaking because it's not properly shared.

I don't know how it will cope but if you split your db (research this term) you could try putting your Back End on the drive (which is bad practice and could lead to corruption) and then copy the Front End onto each computer's desktop.

When sharing Access it should normally be a Client-Server architecture.