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

 

[trustsun]

Got a couple of errors.

1. Getting a invalid procedure call or argument when I update the first combo box after I click the event.

2. When I click the button and thiers no data in the combo box it still run's the report showing all the data. Only want it to run if just the first combo box is populated.

It only works when both boxes are updated.

 

[scriverb]

When you say:

1. Getting a invalid procedure call or argument when I update the first combo box after I click the event.

This would have to be because of some code in the AfterUpdate or OnClick of the combobox(Combo0). I don't know what you have in those procedures. Please check them out and post if necessary.

2. When I click the button and thiers no data in the combo box it still run's the report showing all the data. Only want it to run if just the first combo box is populated.

When you make a selection in Combo0 and leave the second combo empty and click the command button what is happening. You should be prompted with a message box confirming that you don't want to enter info in the second combo. Is that happening?

Also, the reports OnOpen should have the code posted above.

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

Please post back with the specifics of what happens at each step.

[COLOR=006633]Bob Scriver[/color]

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

 

[trustsun]

Your right my explanations to my questions is confusing. I will try to clarify again.

When I said update, I should have said, make my selection from combo0.
To answer your second question, their is no code from the after update event from the combo boxes. Secondly I have the correct code in the report. Not sure if this would help to understand my problem, the report is source from a paremeter quey. Also I do not get the prompt message if I do not select from the second combo box. Still I get "Invalid procedure call or argument"

Private Sub Command4_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
If Not IsNull(Me.Combo0) Then
strFilter = "[myfield1]= '" & Me![Combo0] & "'"
strFilter = strFilter & " and [myfield2]= '" & Me![Combo2] & "'"
DoCmd.OpenReport "MyReports", acViewPreview, , strFilter
End If
MyExit:
Exit Sub
MyErr:
If Err.Number <> 2501 Then
MsgBox Err.Description
End If
Resume MyExit
End If
End If

End Sub

 

[scriverb]

There are a couple of unmatched End If's at the end of the code. Sorry about that. Remove the Highlighted End If's

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

[COLOR=006633]Bob Scriver[/color]

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

 

[trustsun]

Sorry been away a couple of days, wanted to give you a break from me. From your revision, that didn’t work either, hope you can still help me with this?
Maybe I’m asking this the wrong way, let me try again.

From the code you kindly gave me, I would like to leave the second combo box optional and still have the reports filter from the first combo box entry. The second combo box can be use if the records need to be filtered down more. But the report can still run from the first combo box. All the options you provided still require me to use both combo boxes. I only need the first combo box as the requirement.

For you last suggestions:
Removing the IF’s
Getting the error, “The Object Type argument for the procedure or method is blank or invalid.”

 

[scriverb]

I didn't say remove the IF's. I highlighted and said remove the "End If"'s. See the highlighted code.

Now when you run the code and get the error please post the line in the code that is highlighted in the Debug window. That way we know where the error is occuring.



[COLOR=006633]Bob Scriver[/color]

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

 

[PHV]

Have you tried something like this ?
Private Sub Command4_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
Exit Sub
End If
strFilter = "[myfield1]= '" & Me![Combo0] & "'"
If IsNull(Me.Combo2) Then
If MsgBox("2nd Combo is blank. Need to enter?", vbYesNo, 292, "Incomplete combo prompt") = vbYes Then
Me.Combo2.SetFocus
Me.Combo2.Dropdown
Exit Sub
End If
Else
strFilter = strFilter & " and [myfield2]= '" & Me![Combo2] & "'"
End If
DoCmd.OpenReport "MyReports", acViewPreview, , strFilter
MyExit:
Exit Sub
MyErr:
If Err.Number <> 2501 Then
MsgBox Err.Description
End If
Resume MyExit
End Sub

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[mickeyuk]

I've tried to apply the above to my code but can't quite see where i've gone wrong.

I'm trying to get 3 combos on a form to select the records to be shown in a report. have used some VB code on a command button, which should run the code & bring up the report.
code is:

Private Sub cmdapplyfilter_Click()
Dim stryourname As String
Dim Strcustomer As String
Dim strstatus As String
Dim strFilter As String

'Code to open report automatically
If SysCmd(acSysCmdGetObjectState, acReport, "rptRFQ receipt to Tende Sent") <> acObjStateOpen Then
DoCmd.OpenReport "rptRFQ receipt to Tende Sent", acViewPreview
End If

'Build Criteria string for your name field
If IsNull(Me.cboyourname.Value) Then
stryourname = "Like '*'"
Else
stryourname = "='" & Me.cboyourname.Value & "'"
End If

'Build Criteria string for customer field
If IsNull(Me.cbocustomer.Value) Then
Strcustomer = "Like '*'"
Else
Strcustomer = "='" & Me.cbocustomer.Value & "'"
End If

'Build Criteria string for status field
If IsNull(Me.cbostatus.Value) Then
strstatus = "Like '*'"
Else
strstatus = "='" & Me.cbostatus.Value & "'"
End If

'Combine criteria strings into WHERE clause for the filter
strFilter = "[yourname] " & stryourname & " AND [customer] " & Strcustomer & " AND [status] " & strstatus

'Apply the filter and switch it on
With Reports![rptRFQ receipt to Tende Sent]
.Filter = strFilter
.FilterOn = True
End With

End Sub

Once the criteria is selected (name, customer, status) & the command button is press the report is opened but it brings up 2 parameter value boxes-name & status. No-matter what data has been selected in the comboxes or put into parameters the report is blank. I also don't know why only 2 parameter value boxes appear.

Thanks
Michelle