Report Filter

[csdrex87]

Im trying to filter the report based off the team manager's name. When i do this I have the name put into a hidden form called UserLevelAccess. When they open the report it should look in that textbox marked txtTM and apply it as a filter on the report. Here is the code and the line marked with an asterix is the problematic line.

Case 1

If (IsNull(Forms!formUserLoginAccess!txtTM) = False) Then
**DoCmd.OpenReport Forms!PromptBoxReport!ReportNames, acViewReport, , "[Team_Mngr]='" & Forms!_ formUserLoginAccess!txtTM & "'"
DoCmd.Close acForm, "formMenuBar"
DoCmd.Close acForm, "PromptBoxReport"

Else:
strUN = Forms!formUserLoginAccess!txtUN
intID = DLookup("[OMD_ID]", "tblEmployeeId", "[EmployeeName]='" & strUN & "'")
intUSL = Forms!formUserLoginAccess!txtUSL


If intUSL = 1 Then
DoCmd.OpenReport Forms!PromptBoxReport!ReportNames, acViewReport
DoCmd.Close acForm, "formMenuBar"
DoCmd.Close acForm, "PromptBoxReport"
End If

If intUSL = 2 Then
DoCmd.OpenReport Forms!PromptBoxReport!ReportNames, acViewReport, , "[OMD_ID]='" & intID & "'"
DoCmd.Close acForm, "formMenuBar"
DoCmd.Close acForm, "PromptBoxReport"
End If

If intUSL = 3 Then
DoCmd.OpenReport Forms!PromptBoxReport!ReportNames, acViewReport, , "[OMD_ID]='" & intID & "'"
DoCmd.Close acForm, "formMenuBar"
DoCmd.Close acForm, "PromptBoxReport"
End If

If intUSL = 4 Then If MsgBox("You do not have sufficient access to this database!", vbOKOnly, "Restricted Access") = vbOK Then DoCmd.Quit
End If

 

[pbaldy]

Does this work?

DoCmd.OpenReport Forms!PromptBoxReport!ReportNames, acViewReport, , "[Team_Mngr]='" & Forms!UserLoginAccess!txtTM & "'"

 

[csdrex87]

Nope, the formUserLoginAccess is the actual name of the form (just my naming convention)
forms = form...
tables = tbl...
reports = rpt...

anyway still a type mismatch

 

[pbaldy]

I guess this threw me off: "I have the name put into a hidden form called UserLevelAccess".

Did you try with the actual name, without the underscore and space? Do the others work, which would tell us if the name of the report is coming through properly?

 

[csdrex87]

Yep everything works right for each individual user it calls the report fine except for when its a team manager logging in. the information is getting input into that "hidden form" correctly the problem is that when i try to compare it to the information in the report its getting a data mismatch and cant display the report.

 

[pbaldy]

Is it actually a name? A data type mismatch implies the data type of Team_Mngr is a number. Try

DoCmd.OpenReport Forms!PromptBoxReport!ReportNames, acViewReport, , "[Team_Mngr]=" & Forms!formUserLoginAccess!txtTM

 

[csdrex87]

Yea both are actual names ... i.e. Julia Roberts or John Wayne. Something to that effect.

And i also tried it the way you had it written and it gave me an error for a missing operator

 

[pbaldy]

Can you post the db?

 

[csdrex87]

Nevermind i seem to have fixed the problem looking through my database. Somehow I managed to set a number autolookup to a text field in another table so while it showed text in one table it stored it as a number? Anyway thats what created the data mismatch thanks for the help though!

 

[pbaldy]

Sounds like a lookup field:

http://www.mvps.org/access/lookupfields.htm