If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Passing Parameters from Forms to reports
Hi,
I currently have a query called qryDetails, and form called frmWhatDates and a report rptDetails. The form frmWhatDates has 2 txt boxes StartDate and EndDate and also a combo box cmbCatergory. This allows users to select the criteria for the report to be based upon. Only problem is Iam unsure of the code to put in the report, query and form for this to work as the code i got form the microsoft website doesnt seem to work. Fiona |
#2
|
|||
|
|||
Passing Parameters from Forms to reports
|
#3
|
|||
|
|||
Passing Parameters from Forms to reports
On Apr 23, 8:48*am, wrote:
Hi, I currently have a query called qryDetails, and form called frmWhatDates and a report rptDetails. The form frmWhatDates has 2 txt boxes StartDate and EndDate and also a combo box cmbCatergory. This allows users to select the criteria for the report to be based upon. Only problem is Iam unsure of the code to put in the report, query and form for this to work as the code i got form the microsoft website doesnt seem to work. Fiona Fiona, I have a few apps that use the form to drive the criteria on reports. The following method works for me. First of all, lets limit the code to residing in the form and a bas module. I suggest you place a "Print" and "Preview" button on your frmWhatDates. In the Click event of each button, there should be something like this: sub Preview_Click() subSetDetailsReportSource Me.StartDate, Me.EndDate, me.cmbCatergory Docmd.OpenReport "rptDetails", acPreview End Sub In the basReport module: Public Sub subSetDetailsReportSource (dStartDate as DateTime, dEndDate as DateTime, strCatergory as String) Dim qdf as QueryDef Dim strSQL as String strSQL = "SELECT tblOrderHeaders.CustomerName, tblOrderHeaders.CustomerContact, " & _ tblOrderDetails.OrderDate, tblOrderDetails.ItemNo, tblOrderDetails.ItemPrice " & _ "FROM tblOrderHeaders INNER JOIN tblOrderDetails On tblOrderHeaders.CustomerNumber = tblOrderDetails.CustomerNumber " & _ "WHERE (((tblOrderDetails.OrderDate) Between #" & dStartDate & "# And #" & dEndDate & "#) " & _ "And tblOrderDetails.Catergory = '" & strCatergory & "');" Set qdf = CurrentDb.QueryDefs("qryDetails") qdf.SQL = strSQL End Sub I'm just making an example with the tables and such. There should be enough to surmise how it applies to your tables. Let me know this code differs from the Microsoft website. HTH Pete Aleman Vancouver WA USA |
#4
|
|||
|
|||
Passing Parameters from Forms to reports
The easiest way to filter a report is to NOT filter by the query or table.
The OpenReport method has a Where argument you can use for filtering. It is just like creating a Where clause for the query without the word where. You use the name of the field in the report's record source and the value of the control you want to pass as the filter. For example. Dim strWhere As String strWhere = "[Category] = """ & Me.cmbCatergory & " AND [SomeDateField] BETWEEN #" & Me.StartDAte & "# AND #" & Me.EndDate & "#" Docmd.OpenReport "MyReportName", , , strWhere -- Dave Hargis, Microsoft Access MVP " wrote: Hi, I currently have a query called qryDetails, and form called frmWhatDates and a report rptDetails. The form frmWhatDates has 2 txt boxes StartDate and EndDate and also a combo box cmbCatergory. This allows users to select the criteria for the report to be based upon. Only problem is Iam unsure of the code to put in the report, query and form for this to work as the code i got form the microsoft website doesnt seem to work. Fiona |
Thread Tools | |
Display Modes | |
|
|