Some errors on compiling

[Lou_Reed]

In the file that I have attached the form frmEmail crashes when you click send email. It fact it crashes on the
last executable line before the statement End Sub. It says something like method not found, but the method is right there in the source. It is the
biggest sub form the form frmEmail. I just do not know what could be wrong.

Also, the combo near the bottom of the form, the combo box works alright, it just does not allow a selection to go in the
box. It plays a 3 or 4 note tune and then - nothing. I checked properties and found the box was unlocked and enabled. Now
I cannot understand what else must be wrong.

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[ranman256]

can you post the code for us who cannot download?

[Lou_Reed]

Here it is:

Option Compare Database
Option Explicit
Private Sub chkMeeting_Invite_Sent_AfterUpdate()
'Enable/disable Send Ticket command button
'if Ticket assigned checkbox is checked/unchecked
    If Me.chkMeeting_Invite_Sent = -1 Then
        Me.cmdSend_Email.Enabled = False
    Else
        Me.cmdSend_Email.Enabled = True
    End If
End Sub
Private Sub btnClear_Click()
'     Me.txtTo = Null
'     Me.txtBody = Null
     Me.txtAttachment = Null
End Sub
Private Sub cmdSend_Email_Click()
    On Error GoTo Err_cmdSend_Email_Click
    
    Dim stWhere As String        '--Criteria for DLookup
    Dim varTO As Variant         '--Address for SendObject
    Dim stText As String         '--E-mail text
    Dim RecDate As Variant       '--Rec date for e-mail text
    Dim stSubject As String      '--Subject line of email
    Dim StWho As String          '--Reference to tblPersonnel
    Dim strBranchhead As String  ' Branchhead making meeting invite
    Dim strSQL As String         '--Create SQL update statement
    Dim stMeetingID As String
    Dim errLoop As Error
    
    '-- Combo of name to send email to
    StWho = Me.cboFullname
    stWhere = "tblusers"
    '--Looks up email address from Personnel Meeting Query
    varTO = DLookup("stEmail", "", stWhere)
    
    stSubject = ":: Meeting Invitation ::"
    RecDate = Me.txtMeetingDate
    '--Branchhead who emails invitation
    strBranchhead = Me.cboBranchheads
    
    stText = "You have neen invited to a meeting." & Chr$(13) & Chr$(13) & _
             "Meeting: " & stMeetingID & Chr$(13) & _
             "This mmmeting invitation has been sent to you by: " & strBranchhead & Chr$(13) & _
             "Received Date:  " & RecDate & Chr$(13) & Chr$(13) & _
             "This is an automated message. Please do not respond to this e-mail."
             
             
 'Write the e-mail content for sending to employee
 DoCmd.SendObject , , acFormatTXT, varTO, , , stSubject, stText, -1
 
 'Set the update statement to disable command button
 'once email has been sent
 strSQL = "UPDATE tblMeetingID, SET tblMeetingID.ysnMeeting_Invitite_SentID = -1 " & _
          "Where tblMeetingID.lngMeetingID = " & Me.txtMeetingID & ";"
          
 On Error GoTo Err_Execute
 CurrentDb.Execute strSQL, dbFailOnError
 On Error GoTo 0
 
 'Requery checkbox to show checked
 'after update statement has ran
 'and disable send mail command button
 Me.chkMeeting_Invite_Sent.Requery
 Me.chkMeeting_Invite_Sent.SetFocus
 Me.cmdSend_Email.Enabled = False
 
 Exit Sub
 
Err_Execute:
 
     ' Notify user of any errors that result from
     ' executing the query
     If DBEngine.Errors.Count > 0 Then
         For Each errLoop In DBEngine.Errors
         MsgBox "Error number: " & errLoop.Number & vbCr & _
                errLoop.Description
         Next errLoop
     End If
     
     Resume Next
     
Err_cmdSend_Email_Click:
     MsgBox Err.Description
     Resume Exit_cmdSend_Email_Click
     
End Sub
Private Sub Form_Current()
' Enable Send Ticket command button
' if meeting invite sent
' checkbox is not checked
     If Me.chkMeeting_Invite_Sent = True Then
         Me.cmdSend_Email.Enabled = False
     Else
         Me.cmdSend_Email.Enabled = True
     End If
 End Sub
          
  Private Sub bntBrowse_Click()
    Dim fileDiag As FileDialog
    Dim file As Variant
    
    Set fileDiag = FileDialog(msoFileDialogFilePicker)
    
    fileDiag.AllowMultiSelect = False
    If fileDiag.Show Then
        For Each file In fileDiag.SelectedItems
            Me.txtAttachment = file
        Next
     End If
End Sub

It fails on this line:

 Resume Exit_cmdSend_Email_Click

I just do not see where it fails.

Respectfully,

Lou Reed

[jwhite]

1. Your error trapping layout is not correct. You do not have a normal exit point labeled "Exit_cmdSend_Email_Click".

2. Your Bound Column of the combo box is Branch_ID, but the Control is bound to the text field FullName.

[Lou_Reed]

Thanks I will try and correct it.

Respectfully,

Lou Reed

[Lou_Reed]

2.  Your Bound Column of the combo box is Branch_ID, but the Control is bound to the text field FullName.

I see your point, I must confess it is a new one on me. I always use the wizard when I create a combo box and I did not think it would let me make an error like that. I just follow the steps and then it
works - mostly. How can this error be avoided using the Combo Box Wizard?

Respectfully,

Lou Reed

[Lou_Reed]

1.  Your error trapping layout is not correct.  You do not have a normal exit point labeled "Exit_cmdSend_Email_Click".

I have this command in the final executable before End Sub

1.  Err_cmdSend_Email_Click:
     MsgBox Err.Description
     Resume Exit_cmdSend_Email_Click

Is that not sufficient?

Respectfully,

Lou Reed

[jwhite]

1. I personally never use the wizards - most seasoned developers don't.
2. You need to add "Exit_cmdSend_Email_Click:" one line above "Exit Sub"

[Lou_Reed]

Sorry about the error in my last post. It is correct now.

As you can see I do have

Resume Exit_cmdSend_Email_Click

This is the last line of the Sub. it is not one line above "End Sub" since there is a blank space inserted there. But it is the last executable line before "End Sub"

Respectfully,

Lou Reed

[Lou_Reed]

Okay, I see your point the line

Resume Exit_cmdSend_Email_Click:

Needs to be one above Exit Sub, not End Sub.


I will try it.

Thanks.


Respectfully,


Lou Reed

[jwhite]

No, the Resume... line is fine where it is - you need to add the reference point just above Exit Sub, "Exit_cmdSend_Email_Click:", and note the : at the end.

[Lou_Reed]

2.  Your Bound Column of the combo box is Branch_ID, but the Control is bound to the text field FullName.

I assume that for columns in this case the first column is 0 not 1. Thus binding to the second is binding to column 1.


Respectfully,

Lou Reed

[jwhite]

Hmm.... The value you select for the Bound Column Property starts at 1, not 0. Only in VB code does it start at 0.

You have bigger issues with your frmEmail. Branch_ID isn't a field available in the query you made for the form, and even worse your query is based on multiple tables. This is not good. A recordsource for a form should be a single table -- if data in other tables are to be shown/edited, then you link them properly with primary/foreign keys, and add subforms for the other tables, using Child/Master link properties in the Subform Control.

[Lou_Reed]

This section below shows the essence of my problem. I made the change you suggested and made the control source the email not the Fullname. It worked perfectly.

There is just no easy way to see after a few weeks what the names of the zero th and the first column are. I could I guess go back to the query record source, but seems unnecessarily round about, it would
be far more convenient to se it with the properties window open.

Anyway just a thought.

Code:
2.  Your Bound Column of the combo box is Branch_ID, but the Control is bound to the text field FullName.I assume that for columns in this case the first column is 0 not 1. Thus binding to the second is binding to column 1.


 Respectfully,

 Lou Reed

[Lou_Reed]

I see your on the point record source based query that is based on multiple tables. That is unavoidable.

I see no way around it. All the information that I wanted to use
was not available on one table. It required at least three tables as I remember. I will change the other error that you point out.

Respectfully,

Lou Reed