Reports :: Navigation Form - Passing Query Parameters To A Report
Aug 4, 2014
I have a navigation form that will have 6-8 tabs. We were using about that many databases, but we are finally consolidating them into one. The result of us using so many databases has been the multitude of forms and reports that were necessary for each database prior to merging them together.
The problem: There will be anywhere from 12-20 (text boxes) that the user can use to search anything in our database. What we need to have happen, if possible, is for those search parameters to show up in the header of our report if they have text in them. If the text box is blank, it should not show up in the header of the report.
I have read how to to do the start/end date technique, but I do not know if that would work for what we are doing since the boxes would only show up if they are populated by the user.
I would like to have a user enter a start date and an end date into two textboxes on a form. The two dates will be used to query a table. I would then like to print a report that was created from that query.
Here is the query created as a stored procedure:
SELECT Transactions.*, Hoods.* FROM Hoods INNER JOIN Transactions ON [Hoods].[ID]=[Transactions].[BoxID] WHERE ([Transactions].[Date] Between [@StartDate] And [@EndDate]) ORDER BY [Transactions].[Date];
What would be the best way to pass txtStartDate to @StartDate and txtEndDate to @EndDate in the VBA code of the form? How would I open or print the report created from that query filtered on that date range?
Any suggestions? Am I going about it wrong? Should I have created the report from the above query, or should I do it another way? Can anyone direct me to some code that does all of the above or something similiar?
There is a command button on my form that calls a filtered query. I'm filtering the query based on a combo box within the form using
[Forms]![frmAssignDwgs]![Combo15]
It works beautifully until I put the form as a subform in the Navigation Form I'm using. When I execute it from within the Navigation form, it errors out looking for [Forms]![frmAssignDwgs]![Combo15].
Is there another syntax I can use so this will still work from within the Navigation Form? Or another way to approach this functionality?
frmNavigation is the main navigation form frmAssignDwgToPkgs is the subform that holds Combo15
I found another thread (which I can't link to since I'm new) and tried various arrangements of the following to no avail.
The report is bound to a query that has 2 group by fields, 1 count field a a further field, a date field ([Part Date], that I put a default criteria on. This field is not displayed. If I don't put a criteria on this field disappears when I close and open again.
I pass a date to the program via a form and this ultimately ends up in SQLDate. When I run this I get promted to enter [Part Date] even though I'm setting it equal to SQLDate above. I can out garbage to a proper date in here either way the report picks up the default date entered in by the query.
1. Get rid of all of the parameters off the query.
2. Then you can use the Where Clause of the DoCmd.OpenReport code to specify the parameters based on your variables.
1.Not sure what this means but when I get rid of the criteria for the parameter the field disappears (I'm setting the show field to no as I don't want totals group by date). Getting rid of the field gives me all parts used.
2.I think I'm doing this in the above but will bow to superior knowledge!!
or is it I can't pass a parameter to a report run by a query that is grouping fields together to produce a count.
Incidentally once the report has been run (albeit with the wrong parameters) and I go into design mode and look at the property sheet for the report the correct filter is there (i.e., the date that has been input) but it quite clearly ignores this.
I am using an on-click event on my form to call up a report for the current record. It has been a while since I've used the form to call the report, but I always remember it working correctly. Today when I try to call up the report, it opens but no record is being pulled up so the report pulls up blank.
I am using the Navigation form and I have a tab that has all the sales quotes for a particular salesperson. Because my salespeople like to work with paper I have created sub tabs which contain a report with the same information but filtered based as follows: this Month, this year, and Last year. This report needs to be printable based on the filtered information so I created a print button within the report however when clicked it prints the entire report not the filtered report.
The filter criteria is located in the navigation button, navigation where under the data properties. I have the on click event of the button set to open the report in print preview but I need to know how I filter the where in the macro to read the criteria from the active navigation button property?
I have a continuous form that is bound to a SQL Server view.
For each record in my form I have a button, which when pressed opens up a second form. The second form is bound to a stored procedure that takes a parameter. The parameter value that I want to pass to this second form is the value of one of the fields in the first form.
I did the following in the click event of the button on my first form:
Dim stDocName As String Dim stLinkCriteria As String
However, when I run this it keeps prompting me to specify the value of Field1 so this value is obviously not getting through. Do you have any idea why this might be happening?
In my Access Database I have a query (lets say qry1) and in this query i have 2 fields for start and end date, which is provided by 2 Get functions.
also i have qry2 based on qry1 then qry3 based on qry2 and finally qry4(using sql in code) based on qry3, and non of these 3 queries have the start and end date fields.
now here is the problem: I am trying to set the criteria in qry4 and then open a record set on this query(qry4) to use the data that it pulls out...
strsql = "" strsql = "SELECT Sum AS AREA_TOTAL " _ & "FROM qry3 " _ & "WHERE ENERGY_AREA like '" & Area & "';"
Set MyDB = CurrentDb
Set rst = MyDB.OpenRecordset(strsql)
but when the last line is executed I get this error message:
Runtime Error '3061': Too few parameters, expected 2.
i also tried doing this:
strsql = "" strsql = "SELECT Sum AS AREA_TOTAL " _ & "FROM qry3 " _ & "WHERE ENERGY_AREA like '" & Area & "';"
Set MyDB = CurrentDb
MyDB.QueryDefs("qry4").sql = strsql
Set rst = MyDB.OpenRecordset("qry4")
but when the last line is executed it gives me an error message saying that the query does not exist or the name is not spelled correctly. (Ps. I have created the query and the criteria does update once the Select statement is run in the code!)
again the reason for this is that the query has not been populated as the main query (qry1) needs 'strat date' and 'end date'!
Is there anyway I can pass these 2 parameters to qry4 directly using code? If there is a way then this will definitly work as i tried opening the query manually in the Query window and after I input the 2 dates in the input box the query ran successfully!
I would appreciate any help/suggestion guys, I need to sort this out quickly as i have a deadline... Cheers
I am trying to create a form where a user will enter in a value into a text field. Afterwards, when the user clicks "Enter", a query will run and will LOOK FOR THE VALUE THAT WAS ENTERED INTO THE TEXT FIELD. i.e. User enters their address into the field and clicks the enter button. Afterwards, a query will run like select * from customers where address = @address <== the value the user entered into the text field. This is where the mystery lies. How do you pass values?????
I have a query that searches for records that are between two dates using the WHERE clause. The two dates are referenced to two respective text boxes on a form. On the same form I have a button that will launch the query in VBA using querydefs. I get the error 3061 saying I need to input the parameters. Therefore I am a bit unsure how to set the parameters in VBA. So far I have done this:
Code:
Dim Db As DAO.Database Set Db = CurrentDb Dim QDef As DAO.QueryDef Dim rst As DAO.Recordset Set QDef = Db.QueryDefs("Rqt_F_BrokerageMandate_MF3_TEST")
[Code] ....
Where Date_VL is the field to be filtered. I know this is wrong but all examples I have seen have equated the parameter to a fixed value i.e 30/12/2012 for example, but I want this to be at the users discression. The only way I know of to get around this at the moment would be to write a temp query in VBA with PARAMETERS in the SQL code instead using the method above/
After this I'm going to assign the recordset to a matrix but that's a different story!
I did a query which parameter is written in a text box - tprj. This text box is in a form, which is in a navigation control, which is within another navigation control. How can I refer, in the query, to this text box?
The navigation forms are nmain which contains nprojects.
nmainsub and nconsultprojects are the navigation subforms fprjconsult is a normal form, which is inside nconsultproject
And other forms too but I can't get it right.how to refer to forms inside navigation forms, inside navigation forms, in SQL? What are the rules for writing it clearly?
I have an report that uses name paramaters.this is the sql for the report
Code:
PARAMETERS [whatCompany] Text ( 255 ); SELECT tblInvoices.ClientCompany, tblInvoices_Details.Charge, Sum(tblInvoices_Details.Hours) AS SumOfHours, tblInvoices.InvoiceID FROM tblInvoices INNER JOIN tblInvoices_Details ON tblInvoices.InvoiceID = tblInvoices_Details.InvoiceID GROUP BY tblInvoices.ClientCompany, tblInvoices_Details.Charge, tblInvoices.InvoiceID HAVING (((tblInvoices.ClientCompany)=[whatCompany]));
How do I pass the paramaters to the report? I've tried several different ways but can't get it to work
Code: Dim stdocname As String Dim stLink As String stdocname = "RptWithParm" stLink = "ClientCompany = " & "'" & Me.lstCustomer & "'" 'Using the field name doesn't work DoCmd.OpenReport stdocname, acViewReport, , stLink
'When I try to set the value of the paramater that doesn't work either stLink = "[whatCompany] = " & "'" & Me.lstCustomer & "'" 'using the paramater name doesn't work DoCmd.OpenReport stdocname, acViewReport, , stLink
I know I could use the value of the form in the criteria like this
Code: HAVING (((tblInvoices.ClientCompany)=[Forms]![frmTesRptParm]![lstCustomer]));
If I use the list box as the criteria I want to be able to use reports in other than one place, plus there are over 80,000 records and it'll run faster if I set the criteria before the report opens instead of setting a filter after it opens to only show up to about 100.
I've made a report on a blank report template. I've dropped in 5 sub reports. I have entered into the criteria for each of the queries based on [forms]![ClientForm]!CleintID. to run the report. It wortks but I have to enter the ClientID 5 times. Usually, when I create a button to run the form from, it only asks for the CleintID once. The 5 reports are based on 3 separate queries.
How can I pass data (an employee's first and last name) to a report ? I captured the employee's name from the listbox, but can't seem to pass it to the report. The desired report will only have the employee's name and records for related fields on the report. The table (contains emloyees' history data), form name, listBox (contains employees' names), and variable (contains the employee's name) are listed below.
I have a form with 5 buttons on it. Each button is meant to select a warehouse location, so a query can be run to give an inventory report for that location. There is a separate query for each button and the OnClick event does properly modify the recordsource to give the appropriate data to the report for the location selected.
To this functionality I want the OnClick event VBA to pass the warehouse location to a textbox on the report, so the title of the report reflects that inventory location.
My code thus far is:
Private Sub Command5_Click() Dim mySQL As String Dim WHSE As String mySQL = "SELECT [Master Part List].[Part Number], [Master Part List].Category, [Master Part List].Description, [Master Part List].MaterialCost, [Master Part List].Inventory, [Master Part List].Update, [MaterialCost]*[Inventory] AS [Total Cost], [Master Part List].Warehouse"
[Code] ....
When I get the report, the textbox is empty, instead of containing the text value for the warehouse location.
I have a subroutine that successfully builds a SQL statement "strSQL", which is a public variable.
Using msgbox, I can read that the value is correct - SELECT * from tblIncidents WHERE [Nature] = 'Hover';
(The select statement may be complex, e.g. [Nature] = 'hover' AND [COLOUR]= 'Blue' AND [GRADE] = 'High')
I want to pass the variable strSql to my report rptIncident in the following command:
Private Sub CmdPrintReport_Click() If Right(strsql, 1) <> "'" Then 'check if statement was built Else strsql = strsql & ";" 'add trailing ; to statement MsgBox strsql DoCmd.OpenReport "tblincidents", acViewNormal, , strsql End If End Sub
I get a flashing error, then runtime error 3075 - |1 in query expression '|2'.
What I have is a single table that I need to create a report from. It has vehicle unit numbers, dates of service, repair details and costs. I am trying to generate a report where I can select a unit from a combobox and enter a date range.
After years and years of working with Jet, I am now trying to use SQL Server backands and am having problems passing parameters to a stored procedure.
How can I build a query which where I can say "WHERE ([AField]=""" & Forms!BlarBlaretc.value & """)" as I can in access because as I understand it you can not look at front end information from the SQL backend.
Any help (and start at the beginning) would be much appreciated.
I would like to transmit several parameters from my main form to the subform when I open it.
Parameters could be used in the subform by onload event, for eample, to define form caption, hide or show buttons, enable or disable edit boxes, give them a color, ... It could also allow to use the same form in different application contexts, which would reduce so the development time.
More than one technique might be possible. I used the following one : - I put the parameter value I want to pass into myParameter - when click on the button to open the subform : DoCmd.OpenForm myDocument, acNormal, , , , , myParameter - when loading the subform, retreive the parameter value in Me.OpenArgs and using it (eg disable a button)
It works fine. But I can pass only a single value with this technique and it's not enough. I tried to give an array as myParameter and fill it with my parameters values, but Access refuses an array to be in the DoCmd statement for OpenArgs option.
Any idea about passing several parameters to the subform ? Or may be an alternative to the DoCmd technique ?
I have made one form for report purpose. When I enter Start date and End date, I get report of the date range. What I am looking that I want to print the start date and end date in my report. How can I reference date text box in my report?
I have a matrix variable (ex: test(7,3)) define as private in a form's code vba. In this form, I open a report in which I would like to show the values of my matrix variable. How can I do this efficienly ... what i use now is a public buffer variable in a module(it eats memory for nothing)
OK, I am trying to build sql strings from a form buy the user selecting criteria from boxes on a form.
I was given some code which I have adapted very slighty
Dim strCrit As String
If IsNull(Me.server) Then ' proceed Else strCrit = strCrit & " And [Server type] = '" & Me.server & "'" End If If IsNull(Me.RAM) Then ' proceed Else strCrit = strCrit & " And [Ram] = " & Me.RAM End If
The input form of the database have one common table containing data for all parameters for row source. As all data was in one table had one ID. My problem is I am trying to run reports from a form after selecting dates and parameters.
For selecting parameters I created dynamic combo box. For dynamic combo box I needed ID and description of the parameters. So I created separate tables for each parameter with ID and Description column. Which ran me into the problem that when I run report based on main input form which has different row source table and my report form has different row source tables and reports doesn't run when I base my queries on the report form.
I am trying to pass parameters to my qury thru my combo selection. I keep getting this error "Data type mismatch criteria expression", does anyone have an idea why? WHERE (((fShiftWorked([tblTimeLog].[timeStart])=[Forms]![frmOperatorWorkDone]![cboShift] Or IsNull([Forms]![frmOperatorWorkDone]![cboShift]))=True));
I have spent so much time onthis already and i am sick of it :mad:
