Getting Errors With Report after Filtering

[XsivelyLost]

I searched the forum and found several ideas to even get to where I am now. BUT!, I entered the following code to filter my report:



Private Sub PrintRptBttn_Click()
On Error GoTo Err_PrintRptBttn_Click

Dim stDocName As String
Dim strFilter As String

stDocName = "EmpActivityReport"

strFilter = "EmpNumber" = Forms!EmpAttForm!EmpNumber

DoCmd.OpenReport "EmpActivityReport", acPreview, , strFilter



Exit_PrintRptBttn_Click:
Exit Sub

Err_PrintRptBttn_Click:
MsgBox Err.Description
Resume Exit_PrintRptBttn_Click

End Sub


This finally got past all the errors and I can preview the report but nothing is showing up on it. It's like the EmpNumber went south which error'd all the calc fields. Can someone tell me what I am missing? The report is based on a query of two tables. *Employees* & *Occurrence Activity* (Yes, the space has been a nightmare! I'm Learning!) Anyway, Primary in the *Employees* table is the EmpNumber. This number drives the Attendance form. I type it in and all EmpInfo is filled in for me. I follow with whatever occurrence activity has happened over the past week. I can run the report from the reports tab and it's fine, although 57 pages long. I am not telling the procedure something it needs to know apparently. The FilterOn on the Report is set to Yes. Any help would be greatly appreciated.

Thanks,
XsivelyLost

 

[ghubbell]

Great handle...Hello! and, aren't we all? Try this:

DoCmd.OpenReport "EmpActivityReport", acPreview, , "EmpNumber = " & Me.EmpNumber

Evil little quote marks...
Pitch or comment out " ' " all the other stuff between your subs title and exit.
I don't bother with the MS built variables in one-off cases as this...I'm also making an assumption that the form you're firing this off of is called "EmpAttForm". In this case we can save some typing and just call it Me. If your field "EmpNumber" was "Emp Number" You'd have to use square brackets arround it or type it as Emp_Number. Give it a try and make sure the field on your form is really Named "EmpNumber". It should fly. Gord
ghubbell@total.net

 

[XsivelyLost]

Thank you for such a quick response. I made the changes as prescribed, checking everything twice and ran into another problem. I received the error message that the EmpNumber could relate to two different tables in the FROM statement of my SQL Statement. Well, yes this is true. It could. The Employees table Primary is EmpNumber, the Occurrence Activity Foreign is EmpNumber. Perhaps the problem is how I set up the query. When I built it I simply double clicked the astericks of both tables to add them. I then added the EmpNumber field from the Employees table, unchecked Viewed, for sorting purposes. I gave this some thought and tried to change the name of the combo-box field that houses this elusive number to 'EmployeeNumber'. I was thinking that perhaps this would focus the filtering process on this one field. It didn't. I am now being asked to enter the 'EmployeeNumber Parameter'. No doubt because I have no table field named this. If I enter a valid number in this box then I do finally get a previewed report complete with the info I am looking for BUT, it is not filtered. All 57 Employees are present. I only want 1 employees record.

I will include the a copy of the SQL statement but I don't know if it will help:

SELECT Employees.*, [Occurrence Activity].*
FROM Employees INNER JOIN [Occurrence Activity] ON Employees.EmpNumber = [Occurrence Activity].EmpNumber
ORDER BY [Occurrence Activity].EmpNumber;

Note* The combo-box's control source is the Occurrence Activity.EmpNumber field.

I am trying to supply as much info as needed. Please let me know if there is something that I am leaving out.

Thanks in advance for your time,
XsivelyLost

 

[ghubbell]

Bingo! I suspected this would happen (as I've done the same many times too). Try this:

DoCmd.OpenReport "EmpActivityReport", acPreview, , "Employees.EmpNumber = " & Me.EmpNumber

Should hammer your value specficly to the "Employees EmpNumber" in your reports query. Let me know how you fair! Gord
ghubbell@total.net

 

[XsivelyLost]

Oh, Gord. It appears that everything has a resident 'BUT' statement. Ok, the previous error is gone, BUT, I am getting a new one saying that there is a "Data type mismatch in the criteria expression". (I hate a button that forces you to say "OK" especially when I am certainly NOT ok with it.) Anyhow, I have looked at the EmpNumber Fields in both tables. Their data types, field sizes, and all other properties are identical. --Am I looking in the wrong place? Is this a design flaw of my own making? Is it because I am declaring no variables in the Event Procedure (I commented everything out earlier). I am assuming the criteria expression is the one that I am trying to get working in the _Click Event. (Then one you so graciously supplied above) Or is it somewhere else in the application.

Sorry to not be gettin' it. I am usually quicker. I have learned much but there have been many holes not explained.

Thanks again for your diligence.

XsivelyLost

 

[ghubbell]

No sweat! been there many times me'self...

We're back to more evil little little quotes " ' "
Try this:

DoCmd.OpenReport "EmpActivityReport", acPreview, , "Employees.EmpNumber = '" & Me.EmpNumber & "'"

(String instead of number!)

Go for it, I'll wait for your sucessful reply! Gord
ghubbell@total.net

 

[XsivelyLost]

As my son says, "You da Man!!!!" This works Perfectly!!! I can't thank you enough. I'm sure as this "XsivelyLost" little mouse runs his little maze you will hear from me again.

I don't know if there is a feedback page on this sight but if I find it you'll get my best.

Thanks again,
XsivelyLost

 

[ghubbell]

Always a pleasure. I really enjoy "woopin" these things and am happy to see you succeed! Gord
ghubbell@total.net