Error trying to Email Report

[summer01]

Trying to email a report from a form - based on a query that contains EmpID, Name, Email Address. I continue to get the error: "Data Type Mismatch in Criteria Expression". I am using the same format that I have used on other DB programs successfully except that this only has the EmpID to use for criteria without any other type of ID #. I have checked and rechecked my queries and forms but cannot see where the problem is. Below is the SQL for the query that feeds the report and also the event code used for my email button. I am wondering if someone could take a look at them and perhaps see something that I am not.

Report query:

SELECT tbl_Membership.EmpID, tbl_Membership.Name, tbl_results.ResultID, tbl_results.Date, tbl_results.Weight, tbl_results.WeightCal, tbl_results.Height, tbl_results.HeightCal, tbl_results.LastWkBMI, tbl_results.CurrWkBMI, tbl_results.BMIDiff, tbl_results.BMIPoints, tbl_results.ExerciseDays, tbl_results.ExercPoints, tbl_results.PhysActvty, tbl_results.WeightMgmt, tbl_results.LunchLearn, tbl_results.HRA, tbl_results.Library, tbl_results.Volunteer, tbl_results.Water, tbl_results.WaterReq, tbl_results.Journal, tbl_results.WkPoints
FROM tbl_Membership INNER JOIN tbl_results ON tbl_Membership.EmpID = tbl_results.EmpID
WHERE (((tbl_Membership.EmpID)=[forms]![frm_Results].[EmpID]));

Event Code for Button:

Private Sub btnEmail_Click()
On Error GoTo Err_btnEmail_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 e-mail
Dim stWho As String '-- Reference to qryclientSvcs
Dim stDept As String '-- Department who assigned ticket
Dim stSuggestionID As String '-- Work Order Number
Dim stCorpDte As String '-- CorpRecv Date of Project
Dim stRequester As String '-- Person Requesting
Dim stCoordinator As String '-- Suggestion Coordinator
Dim stIdea As String '-- Name Given to Suggestion
Dim stAssigned As String '-- Person who worked it
Dim stClosedDate As String '-- Actual Comp Date
Dim stDocName As String '-- Work Order Status/completion Details

'This forces the record to be saved.

If Me.Dirty Then
Me.Dirty = False
End If

'-- Combo of names to assign price change to
stWho = Me.EmpID
stWhere = "qryClientServices.EmpID = " & "'" & stWho & "'"

'-- Looks up email addresses from qryClientServices
varTo = DLookup("[Email Address]", "qryClientServices", stWhere)

stSubject = ":: Weekly Wellness Report :: "
stOrderID = Format(Me.EmpID)

'-- Evaluators employee who assigns ticket

stDocName = "IndivWklyByIndiv"

'Write the e-mail content for sending to assignee
DoCmd.SendObject acSendReport, stDocName, acFormatSNP, varTo, , , stSubject, True

stDocName = "IndivWklyByIndiv"

Exit_btnEmail_Click:
Exit Sub

Err_btnEmail_Click:
MsgBox Err.Description
Resume Exit_btnEmail_Click
End Sub

 

[KenReay]

Hi

stWhere = "qryClientServices.EmpID = " & "'" & stWho & "'"

are both EmpId and stWho strings?

The dim for stWho says it is a string, but what about EmpId ?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[genomon]

When you step through this code manually in breakpoint mode, which line of code throws the error?

"Business conventions are important because they demonstrate how many people a company can operate without."

 

[summer01]

thanks for your responses. OK, now I really have to admit that I am still a newbie at all this and unfortunately there are certain things I copy without a full understanding of some of the terms. For example, when KenReay asks, "are both EmpId and stWho strings?", I am not exactly sure how to answer. I used the same type of data that is on previous DB's where this works. The EmpID and corresponding names are in the same table and query. As to stepping through, I know that I should be able to hit F8 and a yellow line should appear but I must be missing some step as I cannot make this happen. I even tried control F8 with now luck.
I hope you can be patient with this newbie. I am signed up for additional classes on Access but they are not until late May. Thanks.

 

[KenReay]

Hi

To step through the code, open the code window, click to the left of the a line of code, to insert a breakpoint (a brown bar will appear). Then when you run the code it will stop at the breakpoint and you can step through using F8. To remove the breakpoint, just click on it again.

Strings vs numbers, in your table, it will say text (string) or number. This affects the syntax of the SQL statement, if is a string, you say myfield = 'value', if its a number you say myfield = value.



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[ck1999]

Is the form for this code

frmreports?

if so try

SELECT tbl_Membership.EmpID, tbl_Membership.Name, tbl_results.ResultID, tbl_results.Date, tbl_results.Weight, tbl_results.WeightCal, tbl_results.Height, tbl_results.HeightCal, tbl_results.LastWkBMI, tbl_results.CurrWkBMI, tbl_results.BMIDiff, tbl_results.BMIPoints, tbl_results.ExerciseDays, tbl_results.ExercPoints, tbl_results.PhysActvty, tbl_results.WeightMgmt, tbl_results.LunchLearn, tbl_results.HRA, tbl_results.Library, tbl_results.Volunteer, tbl_results.Water, tbl_results.WaterReq, tbl_results.Journal, tbl_results.WkPoints
FROM tbl_Membership INNER JOIN tbl_results ON tbl_Membership.EmpID = tbl_results.EmpID;

and then
stWhere = "((tbl_membership.EmpID) = " & "'" & stWho & "')"

and see if this works

 

[summer01]

CK1999,

The form I am using is frm_Results and the query that builds the report is qryClientServices. I tried what you indicated and I get the error, "You cancelled the previous operation".

I still could not get the step through to work, which makes me feel a bit like an idiot but I will keep at it.