Sub or Function not defined

[legolas75]

I'm getting an error code when I click my button that says
"Compile error: Sub or Function not defined". How do I prevent this?

FYI I'm trying to email a report based on a number if a table called tblEmail. One report for each line in the table. Thanks.

Mike

Private Sub Command79_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim vIPA_NUM As String

Set db = CurrentDb
Set rs = db.OpenRecordset("tblEmail", dbOpenDynaset)
rs.MoveFirst
Do
    vIPA_NUM = rs("IPA_NUM")

    DoCmd.SetWarnings False
    DoCmd.RunSQL "SELECT DATA1.* INTO [1 rpt_Summary] FROM DATA1 WHERE (((DATA1.IPA_NUM)= '" & vIPA_NUM & "'))", -1
   DoCmd.SendObject acSendReport, "1 rpt_Summary", "MicrosoftExcelBiff8(*.xls)", rs("Email_Address"), "", "", "1 rpt_Summary", "Please find attached your report", True

   rs.MoveNext

Loop Until rs.EOF
rs.Close
db.Close
End Sub

 

[kjv1611]

Which line is highlighted when you get the error?

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

If you are using a version of Office prior to 2003, then my first guess is that you haven't set a reference to the most current version of Microsoft DAO.

--

"If to err is human, then I must be some kind of human!" -Me

 

[legolas75]

The first line is highlighted, Private Sub Command79_Click(). Also, although your right about me running on an older version of Access, 2002 I have the reference to Microsoft Office DAO 3.6 Object Library checked already.
Mike

 

[kjv1611]

Hmm, my next guess:

On this line:
[blue]Set rs = db.OpenRecordset("tblEmail", dbOpenDynaset)[/blue]

Change it to this:
[blue]Set rs = db.OpenRecordset("tblEmail")[/blue]

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

Also, I would be curious as to the reason for this particular setup:
[blue]vIPA_NUM = rs("IPA_NUM")[/blue]

I would think it would be best to do it like this:
[blue]vIPA_NUM = rs.Fields("IPA_NUM")[/blue]

At the least.

Also, is the "IPA_NUM" field a numeric field? If so, why not set it to a numeric variable value instead of string? If you do not have a specific limit to the size, then I would set it to a Long variable.

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

Also...

This line:
[blue]
DoCmd.RunSQL "SELECT DATA1.* INTO [1 rpt_Summary] FROM DATA1 WHERE (((DATA1.IPA_NUM)= '" & vIPA_NUM & "'))", -1
[/blue]

If the "vIPA_NUM" field contains numeric values as apposed to string/text, then you would best type that line like this:
[blue]
DoCmd.RunSQL "SELECT DATA1.* INTO [1 rpt_Summary] FROM DATA1 WHERE (((DATA1.IPA_NUM)= " & vIPA_NUM & "))", -1
[/blue]

...if I am not mistaken.

--

"If to err is human, then I must be some kind of human!" -Me

 

[Eupher]

legolas75,

Are you certain that the name of your command button is "Command79"? Did you possibly rename your command button *after* creating the command button's code?

What happens if you compile within the VBE?

Ken S.

 

[kjv1611]

Good point, Eupher. I've done this a time or two in the past, myself.

--

"If to err is human, then I must be some kind of human!" -Me

 

[legolas75]

Yes the button really is named Command79. I just threw in a button to test the code. I am really new at programing so this was the only way I knew how to test the code. The code I borrowed from someone else's tip while surfing this site and tried to modify it. [IPA_NUM] is character.

kjv1611, I tried your suggestions changing

On this line:
Set rs = db.OpenRecordset("tblEmail", dbOpenDynaset)

Change it to this:
Set rs = db.OpenRecordset("tblEmail")

AND

vIPA_NUM = rs("IPA_NUM")

I would think it would be best to do it like this:
vIPA_NUM = rs.Fields("IPA_NUM")

but I still get the same err highlighting the first line.
Ken, I don't know how to run this code "within the VBE".

 

[PHV]

How did you create this sub ?
Seems to me that you didn't click the ellipsis (...) in the OnClick property.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[legolas75]

I created the sub by adding a button to my form. Right clicking on properties, Event tab, On Click, ... then selecting coder builder and OK.

 

[kjv1611]

legolas75, to check what PHV is hinting at, take a look now at the properties for your button.

Confirm that:
1. The name is indeed the same (you didn't change it afterwards for sure, not even by a mistaken keystroke. This is located on the "Other" tab.

2. There are the words, "[Event Procedure]" (without the quotes) in the text box next to "On Click" on the "Events" tab.

--

"If to err is human, then I must be some kind of human!" -Me

 

[legolas75]

I got the database fixed. Somehow there were some leading blank spaces that were messing things up. I think this happened when I copied someone's code off of the web and tried to modify it. Thanks a lot for all of your help.
Mike

 

[kjv1611]

Yeah, that could do it!

--

"If to err is human, then I must be some kind of human!" -Me