Parameter Query Input Form & Calendar

[scouser]

I WANT to modify how a user inputs data into a parameter query from a form cmdButton to display data on a report!

Hard to explain. The attached DB gives an example but I may well be barking up the wrong tree!!

The user selects 'Order Analysis' cmdButton from frmOrders. This calls frmOrderAnalysisByServiceType. The user inputs criteria. Upon selecting 'OK' rprtOrderAnalysisByServiceType is called and the data displayed is reflective of the parameter entries of the user.

Suffice to say it is not working, but HOW wrong am I?
Help on this one as others greatly appreciated.
Is what I am asking possible?
Cheers,
Phil.

 

[Friday]

You should be referencing the query parameters, but I don't see anything like that in your code. It should be something like (in DAO):

Dim strQdf As DAO.QueryDef

Set strQdf = dbsOrders.QueryDefs("qryOrderAnalysisByServiceType")

strQdf.Parameters("[Enter the Start Date]") = Forms!frmOrderAnalysisByOrderType!FromDate
strQdf.Parameters("[Enter the End Date]") = Forms!frmOrderAnalysisByOrderType!ToDate
strQdf.Parameters("[Enter the Service Type]") = Forms!frmOrderAnalysisBy!cmdServiceType

You send the value to the query this way. The query populates the recordset, and your report is based on that recordset. I'm not sure how to code the greater than or equal to and less and or equal to for your two dates, but I've never used between, so can't help you there. Possibly if you search this forum for the terms parameter query, you will find better advice

 

[IfButOnly]

Phil,

In your query, put the selection criteria on ServiceID as this is the bound column on your combo box - put ServiceDescription in there as a additional field so that your report has all it wants.

Your error is returned when the query does not any matching criteria - the above change will work when there are matching records but you will still be left with the problem when it can't genuinely find any match - you will need to put something in the report to trap this situation.

peter

 

[scouser]

Thanks

Thanks guys (once again Peter!!). I will have a play around.
Peter how about on the Report I add:

Private Sub Report_NoData(Cancel As Integer)
MsgBox "There are no Records to display", vbOKOnly
Cancel = True
End Sub

Cheers,
Phil.

 

[scouser]

Works

OK, Peter your suggestion has worked as did my Report 'No Data' event!
One remaining issue....what I have done is good if the user wants to be Product Specific. However what criteria can I enter to retrieve ALL records? If I leave the combo blank it triggers the No Data event?

I suppose I could have 2 queries. One Product Specific like this example and the second one that retrieves ALL records for the specified dates?

and finally..........Can I put some code on the Open report event to PREVENT the default message 'The openReportAction was cancelled' from appearing. I am happy just to have the 'On No Data' event message!
Nearly there.
Many Thanks,
Phil.

 

[IfButOnly]

Phil, there are a couple of methods you could use.
... Add "ALL" as option in combo box.
... use Like "*" & Forms... in selection criteria.

Given that, it may be neater for you to build your query in VBA using QueryDef. If you plan to do that you will want to use something like this:-

Dim db As DAO.Database
Dim QD As DAO.QueryDef
Set db = CurrentDb()
' Delete the existing dynamic query
db.QueryDefs.Delete ("dynamic_Query")

Set QD = db.CreateQueryDef("dynamic_Query", strSQL)
DoCmd.OpenReport "rptReportName", acViewPreview

.............

On the warning message - I thought something like DoCmd.SetWarnings might work - but no.. Will continue looking.

Hopefully, someone with a bit more report experience can help.

Peter

 

[scouser]

Thanks

Thanks Peter. Amending the existing criteria might be easier!!
I will TRY to implement your suggestion. I was thinking along the lines of:

Current Code
[Forms]![frmOrderAnalysisByServiceType]![cmdServiceType]

Proposed
[Forms]![frmOrderAnalysisByServiceType]![cmdServiceType]or[Forms]![frmOrderAnalysisByServiceType]![*]

Not sure it will work!! I will give it a go!!
Phil.

 

[scouser]

Erm!!!

OK, so how do I add '*' to the combo option? The combo takes its value from a query, the query is based on a table tblServices?
Cheers,
Phil.

 

[scouser]

Tried

I tried this:

[Forms]![frmOrderAnalysisByServiceType]![cmdServiceType] Or [Forms]![frmOrderAnalysisByServiceType]![*] Or Like "*"

This displays EVERYTHING if I leave the combo blank. If I make a selection fro the combo it STILL displays EVERYTHING!!

Any ideas?
Cheers,
Phi.

 

[IfButOnly]

Phil,
Like "*" & [Forms]![frmOrderAnalysisByServiceType]![cmdServiceType]
...
We have a solution to the warning message, couresty of Stormin Norm, in a past thread. I don't know how to link you to it but do a search on NoData and it will bring it up.

The solution is - change your cmdOK_Click module to

Err_cmdOK_Click:
' MsgBox Err.Description
Select Case Err.Number
Case 2501 'There were no records, so close this report, run the next!
Resume Next
Case Else
MsgBox Err.Number & ": " & Err.Description, , "ERROR " & Err.Source
End Select
Resume Exit_cmdOK_Click

Regards ... Peter

 

[scouser]

Nice One

Cheers Peter.
Phil.