filter report from combo box in form 1

[trustsun]

Below is what I have, but when I try to filter the report using the selected text in the combo box, it will not filter.
Could someone help me and what I did wrong.

Private Sub Command4_Click()
On Error GoTo MyErr
If IsNull(Me.Combo0) Then
MsgBox "No data found! Closing report.", vbCritical, "Select location.."
Me.Combo0.SetFocus
Else
If Not IsNull(Me.Combo0) Then
strFilter = "[myfield1]= " & Me.Combo0
strFilter = "[myfield2]= " & Me.Combo2
DoCmd.OpenReport "myreport", acViewPreview
MyExit:
Exit Sub
MyErr:
If Err.Number <> 2501 Then
MsgBox Err.Description
End If
Resume MyExit
End If
End If

End Sub

 

[scriverb]

First of all I would change your MsgBox as it is not really descriptive of the problem encountered:

 MsgBox "No location selected, a selection must be made to run report", vbCritical, "Select location.."
Me.Combo0.SetFocus
Me.Combo0.dropdown

You stated that the data is text so the following syntax must be used:

strFilter = "[myfield1]= [red]'[/red]" & Me.Combo0[red] & "'"[/red]
strFilter = [red]strFilter & [/red]" [myfield2]= [red]'[/red]" & Me.Combo2 [red]& "'"[/red]
DoCmd.OpenReport "myreport", acViewPreview[red], strFilter[/red]

I have made adjustments to your code in RED above. Your code did not cancatenate the two expression into one and you did not designate the strFilter string as the final argument of the DoCmd.OpenReports command.

Hopefully, this will clear this up for you.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[trustsun]

Sorry that didn’t work either. I looked at the SQL closely it's a number value.
So, how can I revise the syntax to pull by a number value?
Here's what I got so far.

If Not IsNull(Me.Combo0) Then
strFilter = "[myfield1]= '" & Me.Combo0 & "'"
strFilter = strFilter & " [myfield2]= '" & Me.Combo2 & "'"
DoCmd.OpenReport "myreport", acViewPreview, strFilter

 

[scriverb]

This should do it:

If Not IsNull(Me.Combo0) Then
strFilter = "[myfield1]= " & Me.Combo0 
strFilter = strFilter & " and [myfield2]= " & Me.Combo2
DoCmd.OpenReport "myreport", acViewPreview, strFilter

Let me know how that works for you.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[trustsun]

Still the report want filter from your suggestions using a combo box from the form. FYI, when I set the parameter in the query its works. Thus the SQL is not the problem, maybe there’s something I should add to the report?
Hope you can still help me.

 

[scriverb]

Put a stop in your code just in front of the OpenReport code. In the Immediate window see what the string strFilter looks like and post it here.

Just enter ?strFilter and then Enter. Copy the results and post.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[scriverb]

Put an extra comma in the code line as follows:

DoCmd.OpenReport "myreport", acViewPreview,[red],[/red] strFilter

This should work now.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[trustsun]

Now I get the error,

Extra)in query expression ‘([myfield1]= combo0 and [myfield2]=’.

 

[scriverb]

Since we are not using right parents in the expression this seems strange. You have the code ther so look and see if the code has a right parent rather than a right square bracket somewhere. If not then like I said before stop your code after the filter string is completed and using the immediate window to display what the value is for strFilter.


Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[trustsun]

That did it,thanks a million for your patients and time.
Had to convert it back to a text value, works like candy to a baby.

 

[scriverb]

Finally. I knew we could get it. Just a matter of time. Thanks for the Star. Much appreciated.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[trustsun]

Update!!!
Ok this works fine when I need to print or view the reports. How could I use the same method to file or email the reports?

 

[scriverb]

For emailing a report check out the DoCmd.SendObject comand.

DoCmd.SendObject [objecttype][, objectname][, outputformat][, to][, cc][, bcc][, subject][, messagetext][, editmessage][, templatefile]

This command has a string of parameters that identify the report to send, output type, and then the parameters for emailing. With your query working for your report you then just have to adapt this command to your report name and the individuals that you want your report to be sent to.

Post back if you need more information. There is also quite a number of other threads here at TT concerning emailing of reports and the report formats that work the best. I have found that the Snapshot format works best as it preserves the ACCESS object formatting.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[scriverb]

Here is a link(

http://www.peterssoftware.com/c_emfa.htm

) that includes some code for emailing with or without reports.

Hope this helps you.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[trustsun]

From the link you suggested the only way to have the report filter from a combo box is to put the filter option on the “OnOpenEvent” via my report. However when I execute the command button a parameter box shows with the value from the combo box I selected.

Below is the code for the report;
Private Sub Report_Open(Cancel As Integer)
Me.Filter = "myfield1=" & Forms![formname]![Combo0]
Me.Filter = "myfield2=" & Forms![formname]![Combo2]
Me.FilterOn = True

End Sub


Below is the code for the form;
Private Sub Command7_Click()
On Error GoTo MyErr
If IsNull(Me.Combo0) Then
MsgBox "No location selected, a selection must be made to run report", vbCritical, "Selection Required "
Me.Combo0.SetFocus
Me.Combo0.Dropdown
Else
If Not IsNull(Me.Combo0) Then
strFilter = "[myfield1]= '" & Me![Combo0] & "'"
strFilter = strFilter & " and [myfield2]= '" & Me![Combo2] & "'"

DoCmd.SendObject acSendReport,, "myreport"
MyExit:
Exit Sub
MyErr:
If Err.Number <> 2501 Then
MsgBox Err.Description
End If
Resume MyExit
End If
End If
End Sub


Hope you can still help me with this?

 

[scriverb]

There is a difference between the OpenReport and the SendObject command. The OpenReport has an argument that can be set to the value of the filter to be used. The SendObject command does not. Earlier in the thread your code was being desinged to use the OpenReport with the use of the variable strFilter as an argument:

DoCmd.OpenReport "myreport", acViewPreview,, strFilter

Now that you want to send the report through the SendObject command we have to pass the strFilter to your report in another manner. The variable strFilter is declared someplace in your code. It MUST now be declared as a Global variable in a database module if it is not alaready. Remove the Dim statement from where it is and add the following to a database module:

Global strFilter as String

Now we are talking about two distinctly different operations here and we may get our wires crossed. The code we originally set up for passing the strFilter through the argument of the OpenReport command works now. In that report there is no need to have any code in the report OnOpen event procedure. The filter is being passed automatically through the command argument.

Now that we are going to use the SendObject command we must pass the global variable strFilter to the report in the OnOpen event procedure.

Private Sub Report_Open(Cancel As Integer)
Me.Filter = strFilter
Me.FilterOn = True
End Sub

The only other thing I question in your code is the building of the strFilter value. It looks like you have reverted back to the code where you said your fields were strings, thus the cancatenating of single quotes around the combobox values. To make the OpenReport command work you modified it by removing the single-quotes. Must be consistent.

Post back with questions.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[trustsun]

Thanks again, that worked as well.
However I would like to use this on more than one report, having the options to print, view, file, and email. I got all four options to work with just one report but not the others. Not sure what's the problem, been at this Access stuff for 2 years periodically and it's a joy to have you guys to help those in need.

In the mean time I'll just combine all the records into one report and filtered it that way. The only problem with that the combo options may be longer, trying to keep it at a minimum.
Thanks and "KUDOS".


Trust.

 

[scriverb]

You are quite welcome. Thanks again for the Star.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[trustsun]

Update,
Everything worked with other reports, just had some typing issues.
Given the same method how about leaving the second combo box blank or optional for the users to choose? The only requirement is the first combo box. The reports are blank unless I update both combo boxes. I only need the first combo box updated and the second is optional.

Could you revise the code, PLEASE..

 

[scriverb]

To make the second combo optional use the following Command button OnClick event procedure code.:

Private Sub Command7_Click()
On Error GoTo MyErr
If IsNull(Me.Combo0) Then
   MsgBox "No location selected, a selection must be made to run report", vbCritical, "Selection Required "
   Me.Combo0.SetFocus
   Me.Combo0.Dropdown
Else
   If IsNull(Me.Combo2) then
      Dim x as variant
      x = MsgBox("2nd Combo is blank.  Need to enter?", VbYesNo, 292, "Incomplete combo prompt")
      If X = 6 then
         Me.Combo2.setfocus
         Me.Combo2.dropdown
      Else
         strFilter = "[myfield1]= '" & Me![Combo0] & "'"
      End If
   Else
      strFilter = "[myfield1]= '" & Me![Combo0] & "'"
      strFilter = strFilter & " and [myfield2]= '" & Me![Combo2] & "'"
   End If
End If
DoCmd.SendObject acSendReport,, "myreport"
MyExit:
  Exit Sub
MyErr:
  If Err.Number <> 2501 Then
    MsgBox Err.Description
  End If
  Resume MyExit
 End If
End If
End Sub

Post back if you have any questions.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]