Filter Report based on Selection from Form

  • Thread starter Thread starter Su
  • Start date Start date
S

Su

I have a form with 4 combo boxes set up and a run report button. I
would like to pass the selection from the combo boxes to the report
select statement. Below is my code from the "run report" button. I am
currently getting a type mismatch error. Any help would be great. I
can't seem to find a straight forward way to do this anywhere.

Private Sub Run_Task_Tracker_Click()
On Error GoTo Err_Run_Task_Tracker_Click

Dim stDocName As String

stDocName = "Task Tracker"
DoCmd.OpenReport stDocName, acPreview, , "[emp_name]= &
[Forms]![TaskTracker2]![Employee]"

Exit_Run_Task_Tracker_Click:
Exit Sub
 
Su said:
I have a form with 4 combo boxes set up and a run report button. I
would like to pass the selection from the combo boxes to the report
select statement. Below is my code from the "run report" button. I am
currently getting a type mismatch error. Any help would be great. I
can't seem to find a straight forward way to do this anywhere.

Private Sub Run_Task_Tracker_Click()
On Error GoTo Err_Run_Task_Tracker_Click

Dim stDocName As String

stDocName = "Task Tracker"
DoCmd.OpenReport stDocName, acPreview, , "[emp_name]= &
[Forms]![TaskTracker2]![Employee]"
Click to expand...


That message usually means that the field in the table is a
different type than the value you are using in the
condition. In this case I suspect that it's a Text field,
in which case, you could use:

... , "[emp_name]= """ & Me]![Employee] & """"
 
Thanks! I tried that and I am still getting the same error. Is there
any other way to get this done? Basically, I need to pass 4 selections
from combo boxes into the select statement for a report. I haven't
spent much time on access so I am kind of at a loss for ideas. Thanks,
 
I finally got that to work. My report now displays data selected in
the combo box.

I have another question. I am using the AddAllToList function. Is
there a way to make the report display all of the data when All is
selected.
 
Su said:
Thanks! I tried that and I am still getting the same error. Is there
any other way to get this done? Basically, I need to pass 4 selections
from combo boxes into the select statement for a report.
Click to expand...


Well, there are other ways, but they are more complex and
will get the same error. So you might as well work on
getting the simple way to work.

Let's modify the code slightly to make it easier to figure
out what's going on:

Private Sub Run_Task_Tracker_Click()
Dim stDocName As String
Dim stWhere As String

stDocName = "Task Tracker"
stWhere = "[emp_name] = """ & Me]![Employee] & """"
Debug.Print stWhere
DoCmd.OpenReport stDocName, acPreview, , stWhere
End Sub

Open the debug/immediate window (Ctrl+G), then click your
button and look at the debug window to see what stWhere
really looks like. If that doesn't make the problem obvious
to you, Copy/Paste the code and the line in the debug window
so I can see it.

Please confirm the **type** of the [emp_name] in your table.
 
Su said:
I finally got that to work. My report now displays data selected in
the combo box.

I have another question. I am using the AddAllToList function. Is
there a way to make the report display all of the data when All is
selected.
Click to expand...


I never heard of the AddAllToList function, so I can't be
specific. The general idea is to check if "All" was
selected and skip the criteria:

stDocName = "Task Tracker"
If Me!Employee <> "All" Then
stWhere = "[emp_name] = """ & Me!Employee & """"
End If
. . .
 
Back
Top