Reports :: Passing Variable To Report Record Source?
May 7, 2013
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'.
View Replies
ADVERTISEMENT
Sep 16, 2014
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.
View 5 Replies
View Related
Jun 24, 2014
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.
Code:
DoCmd.OpenReport "rptSupplierInformation", acViewPreview, , "[OperationID]=" & [cboMoveTo], acWindowNormal
View 1 Replies
View Related
Aug 27, 2013
Everytime i make a report in Access, first thing i do is build a query and then use it as a record source. I try the other way, I go to create report design directly and do the drag and drop of fields.
View 2 Replies
View Related
Jun 13, 2014
How can I set the record source of a report to a saved query through VBA. I am trying to use the same report for a number of uses, all of the info on the report is the same, but the only difference is the query that the information is based on. I have this simple code below, how do I add a record source to it (if it can be done)
DoCmd.OpenReport "SellRPT", acViewReport, , , acNormal
View 2 Replies
View Related
Jul 24, 2015
I have a report that is based on nested (I think thats the phrase) query's.
Complicated Query based on another query (so I can't see a way to get at the the source SQL to change or use elsewhere)
This gives a list of say 20 records I generally want printed. I use the exact same query criteria with a separate update query to add the same to a table.
However I then wanted to just pick one with exact matching ID's I select on a form.
I could not see an easy way to apply this without making another set of nested querys which seems a little excessive
Anyway, an easy way for the printed report to do this is a simple filter added after, works great.
I can't see a way to do the same for an update query.
I was wondering if I could get the record source of this report and add to my table. I have tried with
' Dim db As DAO.Database
' Dim rs As DAO.Recordset
'Set db = CurrentDb
'Set rs = db.OpenRecordset(Me.RecordSource, dbOpenDynaset)
' Set rs = CurrentDb.OpenRecordset(Me.RecordSource)
And dozens of variations over some hours but a variety of errors mainly "too few parameters."
View 3 Replies
View Related
Jul 13, 2005
Hi
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)
can someone tell me a trick ...
tanks a lot
View 1 Replies
View Related
Mar 14, 2013
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.
Table_Employee_Detail_History
Form_Employee's Reports
Report_Attendance_Report
stremployee (variable
View 14 Replies
View Related
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.
View 4 Replies
View Related
Mar 12, 2015
Access 2007 Sub-Report "rptSubEmployeeProject" inside report "rptProgressReportDay".
I need to dynamically change the table in the sub-report's record source. I tried (line wrapped in code tags below for reading purposes)
Code:
SELECT tblProjectHistory_fldProjectID,
FirstOfHistory, [History Date], [Time Spent],
Employee, fldAssigned, TheFieldPriority,
fldTitle, employeeID, fldTimeSpent,
fldStatus, fldHistoryID, fldOrder
FROM " & [TempVars]![TempEmpTempTable] & "
ORDER BY fldOrder;
And I get the error of invalid bracketing of name and it refers to the [TempVars]![Temp part. Makes me believe that I cannot use TempVars in a Reports RecordSource, is that accurate? If So that leaves me trying to set a sub-reports recordsource via vba right?
View 9 Replies
View Related
Feb 27, 2015
I have a database that reports activities by region.
Each week, my regional volunteers report statistics on a number of club activities. This is in the form of zero to theoretically infinite activity reports that they enter on a website. I download the .csv from the website, add the activity reports to the activity table and send them a totals summary every now and then.
The summary report shows figures for every club in the region, even if no activity reports have been entered for that club that week or ever.
This works fine, including forcing the query to return zeros when no reports have been submitted for that club.
What I want to do is have the report also show (in brackets next to each figure) the position as it was X number of days previously.
I can make the query and report to show the figures now.
I can make the query and report to show the figures X days ago.
What I cannot work out is how to combine the two queries into one report source so that I can get
Club 1 100(50) 75(0) 45(45)
Club 2 0(0) 0(0) 0(0)
Club 3 20(19) 0(0) 200(50)
etc
If I try and make a third query that gets the sums from qryNow and the sums from qryXdaysago for each record in qryClubsByRegion, I get two lines for each club.
View 7 Replies
View Related
Jun 10, 2014
I am using vista and access 2010, what I want is to be able to email a report from access that was created by a form with DoCmd.OpenReport "ReportOrder", acViewReport. I have a button that when i click it it will send the report via email, but the email address has to be a variable so when the report is created i can use the email that is attached to the report data.
View 3 Replies
View Related
Jul 7, 2013
I have a report in access 2007, now i need to ask that i am creating new blank report and just like to to capture value from other report via textbox or any source (you may reccommend), for e.g in Report A i have months and their total amounts now i want to add both these fields in new Report B where i will do the same with other previous reports to create summary of accounts.
View 1 Replies
View Related
Oct 3, 2013
I have now a report that span around 2 pages. It has subreports in the report that depening how many rows there are they can grow or shrink.I have the problem that the second page the top margin is to close to the top of the pager. The paper has a logo on the right top side. I can make the first page look nice under the logo, but the second page prints to close to the edge that prints over the logo. I did add a page break, but when the page 1 has more data, it flushes this to the next page. The page 2 is now page 3. How can I avoid that, or make it so that the margin of the second page/next page is on the correct lenght of the top. I tried it with the page setup, but it does not work.
View 3 Replies
View Related
Apr 16, 2013
In the detail section of my report, I recently added a second row which has only 1 text field. When the value of the textbox in the 2nd row is null, I want the total row height equal to just the first row. When the value in the textbox in the 2nd row is not null, then the total row height is equal to row 1 and 2. I tried to make the textbox in the second row invisible when it had a null value thinking that when it was null, the report would shrink to the first row height, but that didn't seem to work.
View 1 Replies
View Related
Apr 6, 2013
I am trying to join a number of reports into one report. I have a generic report which displays a different dataset given the user's choice on a form. I created a collection where I can store multiple instances of this report (called mcolReportInstances) - this works just fine.
I was looking to combine all the reports in the collection into one report. To that end, I have created a report with a number of subreport controls but with no sourceobject. In the On_Open event of this blank report, I am trying to set the source object of the subreport to one of reports in my collection:
Me.Controls("Child" & i).SourceObject = mcolReportInstances.Item(strKey)
However, it keeps giving me the error 'Type mismatch'.
View 10 Replies
View Related
Oct 30, 2014
I am in the process of creating a training database that includes levels of proficiency with certain tasks for employees.
In one of my reports I would like to appropriately display with tasks the employee "Cannot Perform";"Can Perform with Assistance";"Can Perform Alone";"Trainer" (straight from the field list of the task). But I can't seem to get the hierarchy correct. Tried it in a PivotTable too as I thought similar to PTs in Excel you could get some kind of "count" of values. Couldn't make that happen either.
Each employee has a proficiency rating on about 20 different tasks. Proficiency input is controlled by a field list. I would like to structure this part of the report like so:
------------------------------------------------------------
EMPLOYEE PROFICIENCIES
--Cannot Perform Task
----Cutting
----Trimming
----Grinding
--Can Perform With Assistance
----Painting
----Fixing
----Drilling
etc. etc. So in this case the Field itself would become the value being grouped. I know there has to be some logic either in a query or SQL.
View 2 Replies
View Related
Jan 14, 2005
OK. I have a report that I want to use as a master and use with about 4 different querys. In the report properties I've bound it to a query. I've tried for ages to change the record sources with on click command bottons on another form. I think things have become complicated because there's a subreport on the report I AND a there's bunch of code to make things invisible in the on page event of the report.
anyway, here's what I'm using:
DoCmd.OpenReport "rpt_master", acViewPreview
Me.RecordSource = "qry_rptPrintRollClass"
Is this the right way to do it??
Any ideas on how I can clean it up??
Thanks
Damon
View 2 Replies
View Related
Sep 22, 2005
I'm looking to change the record source of a report and then print the report depending on what button the user clicks on.
Is there some way to do this?
View 8 Replies
View Related
Aug 3, 2015
I have a report based on a query. I want to populate 6 Text Boxes with Dates from fields in another query. The date fields I want to add will be headings for columns that represent weeks (they change all the time so can�t be hard figures). The two queries are not really related by any common field. I am not able to get this working because the fields I want are not part of the query that is the Record Source for the Report.
Is there any way that I can do this? Can I change the record source of just the text boxes?
View 5 Replies
View Related
Aug 21, 2012
I have an application written in Access 2007 and packaged using the Packaging Solutions for deployment with Runtime.My problem is that I've written an updated version, and after carefully saving my original Back End away from the install site, uninstalling Runtime and the FE, installing the new FE with a blank copy of the BE having the same name and Runtime, and copying the filled BE into the same folder with the FE (replacing the linked, but empty, BE) I find that SOME of my tables in the BE are not being recognized. This isn't true of all tables. I can open some of the forms and find everything there. In other cases I get the following error message "The record source "tblName" specified on this form or report does not exist."
View 2 Replies
View Related
Feb 6, 2006
I want to build an SQL statement in code which includes the value stored in a string variable where the variable name includes a loop counter.
This is a much simplified example of what I am trying to do:
Dim i as integer
dim Strtable1 as string
dim Strtable2 as string
dim Sqlstring as string
...
Strtable1 = "tblEmployees"
Strtable2 = "tblSales"
...
Sqlstring = " select * from ... where....."
for i = 1 to 10
DoCmd.RunSQL "INSERT INTO " & StrTable & i & sqlstring
Next i
I am not sure how to get the table names stored in the string variables into the SQL statement. When I try the above it looks for a variable named StrTable, not StrTable1, StrTable2 etc.
View 4 Replies
View Related
Mar 5, 2008
how can i pass a variable from one form to another
this works fine
Tracking_Number = Forms("Main").Control("mytext")
but if "mytext" was in vb (rather than from a text box as shown above) how can i do this
View 4 Replies
View Related
Aug 8, 2006
Hi Everyone,
I'm trying to pass a variable called MyFilter between forms but am having problems. I have created a Module and declared MyFilter as a public string.
The original code in my first form is:
Private Sub Command65_Click()
Dim MyFilter As String
If Me.Filter = "" Then
MsgBox "Please apply a filter to the form first."
ElseIf Me.Dirty Then
' Make sure the record is saved
RunCommand acCmdSaveRecord
Else
MyFilter = Me.Filter
DoCmd.OpenReport "Temp", acViewPreview, , MyFilter
End If
End Sub
How to I change this so that it now stores the value in the Public variable instead of the Private one which it is doing above?
Thanks,
View 5 Replies
View Related
Sep 13, 2004
I have an event on a form kicked off by On Delete and I have an event on the same Form kicked off by After Delete Confirm. I need to pass a variable from the On Delete Event to the After Delete Confirm Event. I have set up the variable as public, but it keeps getting reset inbetween the two events. Any ideas?
View 1 Replies
View Related
Apr 29, 2007
Hi all!
I have a college project were we have had to code a database for a fictional hospital to hold patient, doctor, consultant and appointment information. I am struggling with one particular problem.
I am trying to pass a variable from a combobox of results to another form to display a certain record.
I am trying to send:
stLinkCriteria = "[NHS_Number]=" & "'" & Me![lstSearch].Value & "'" & " AND [AppointmentID]=" & "'" & AppointmentID & "'"
to the form but it fails, yet if I "hard code" the variable it works:
stLinkCriteria = "[NHS_Number]=" & "'" & Me![lstSearch].Value & "'" & " AND [AppointmentID]=23"
Can anybody shed any light upon this please?
View 2 Replies
View Related