How do I get a query to run upon opening a report. My report has a few fields from a query that once the user inputs information the query is obviously outdated. Since it needs to be as simplistic and user friendly as possible I won't ever have them run the queries themselves so I'm trying to have them run to be updated for the report when the user goes to open the report.
View RepliesI've been reading up quite intensively on using QueryDef's collection to try to pass a Parameter to a Query in order to then Open a Report. But can't seem to get it to work. I've been trying to write an SQL statement that the VB will accept in code but never got it to work. Depite copy/pasting the SQL from the Query Access creates. regardless.. I really need some help with this one. Thanks. Procedure follows. Get error that Report not found... The name is correct... I just want to pass the integer (index) from the ListBox into the Query then Open the Report... Thats all...
Private Sub previewReport_Click()
On Error GoTo Err_previewReport_Click
Dim intSelectMonth As Integer 'The Index of the Item Selected in Form List Box
Dim db As Database
Dim smReport As Report
Dim smQuery As QueryDef
Dim month As Parameter
Set db = CurrentDb
Set smQuery = QueryDef![GetReviewsMonthQuery]
smQuery.Parameters![rvMonth] = 9 'Using 9 as September just for Testing
Set smReport = Reports![Review Schedule by Month/Analyst]
DoCmd.OpenReport smReport, acPreview
Exit_previewReport_Click:
Exit Sub
Err_previewReport_Click:
MsgBox Err.Description
Resume Exit_previewReport_Click
End Sub
I built a database off of the access wizard and it gives me the capability to enter date parameters before running my reports, than displaying the revised report. I.E. I am prompted to enter start and finish dates, and then after entering this info and hitting a button to preview the report, the only data displayed is that that falls into that date range.
I have created a new report that wasn't built into the wizard. This report "hits" an underlying query. I have also added a button on the switch manager to preview the report. I want a user to be able to click that preview report item on the switch manager, then be prompted to enter start and finish date information, then hit an ok button, then have the report with the relevant information displayed.
Any and all help would be greatly appreciated. Thanks.
I have DB in access 2007. I have a report that is uses a select query to generate the information for the report. It has been working great, But however lately like maybe with in the last month, it has been causeing Access 2007 to crash. I am having the same issue with another DB that uses the same information but that information is imported in. both Databases have worked great up until two months ago. Microsoft states that it is because of the program. I have tried to repair the DB by using the Repair option. I am confused as to why this would be happening. I can create a new report and it seems to work. but I do not want to change all the DB on everyone's computer just for this reason. I also have two buttons on my report that utilize macros to close or print the report.
View 6 Replies View RelatedI have a query that uses the input from a form as criteria, which is then used in a report. The form input is a drop down based on another table. This is a sales pipeline report, and the list is a list of sales people. The report works perfect for all sales people except one. When I run it for the one, I get the following error:
"This expression is is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables".
I DO NOT get the same error when running the query by itself - so assuming there is something in the report causing this. I do have some sum formulas in the report.
Again, no other salespersons selected cause this error -- so I am assuming there is something in the dataset for this person that is causing the error.
I have a query that searched thru records based on a person's last name. It runs fine and returns the results I want. However, the view of the results is in record-view. I'd like the results to be in a report format. Is this possible? The macro that calls the query is set to display in report view, but the query always come back showing the records. What am I missing here? Thanks a ton for your time!
James
Dear All:
I have created a database to keep track of transcripts that are requested. This database has their names, ID number, date requested(With a pop-up calender), transcript type requested(This is a combobox where the choices are "Official Copy" and "Student Copy") and address.
So far, it works great. What I wish to do is run a report that I can choose the begining date and ending date for a specified month to see the total "Official Copies" and "Student Copies" for a given month.
Any ideas out there?
Hats off to Colm!
Regards,
Dion
I posted this within the Reports as well, but since this has a little bit of VBA decided to ask this here as well.
I have a report that is created from the following query (qryTotalProjectHours). What I am trying to do is get the total hours spent on Tasks within a given time period. A given Task can we worked on by multiple individuals and hence I need to find a way to aggregate the hours spent.
Code:
SELECT TasksEntries.Project, TasksEntries.Task, Sum(TimeTracker.WorkHours) AS TotalHours
FROM TasksEntries INNER JOIN TimeTracker
ON (TasksEntries.EmployeeId = TimeTracker.EmployeeId) AND (TasksEntries.TaskID = TimeTracker.TaskId)
GROUP BY TasksEntries.Project, TasksEntries.Task
I accept the start and end dates in a form and pass it like shown below. WorkDate is a column in the TimeTracker table and is not present in any other table.
Code:
strWhere = "WorkDate BETWEEN #" & txtMgrRptStartDate & "# AND #" & txtMgrRptEndDate & "#"
DoCmd.OpenReport "rptTotalProjectHours", acViewPreview, "qryTotalProjectHours", strWhere, acWindowNormal
When the report is invoked, I get a box where it says
"Enter parameter value" for Workdate..
Is there anyway I can get rid of the prompt? I never thought you needed the column name in the SELECT statement to be able to run this.
I should add the I tried the query with the WorkDate hardcoded in there and it worked fine and returned the correct results
I have attached the cut down version of the database that I am working on.Open up the frmManagerReport Form
Leave the Employee drop down empty
Enter the start and end dates ( I have used 4/1/2015 and 4/30/2015)
Select the 3rd option "Generate Tasks by Total Hours"
click on Run
When you do that you will see the prompt come up. Enter any date and you will see the report. The report generated uses the results from the query and does not filter on the date selected in the form. This is what I have been struggling to fix since yesterday but have reached nowhere..
I have a report that is created from the following query (qryTotalProjectHours). What I am trying to do is get the total hours spent on Tasks within a given time period.
Code:
SELECT TasksEntries.Project, TasksEntries.Task, Sum(TimeTracker.WorkHours) AS TotalHours
FROM TasksEntries INNER JOIN TimeTracker
ON (TasksEntries.EmployeeId = TimeTracker.EmployeeId) AND (TasksEntries.TaskID = TimeTracker.TaskId)
GROUP BY TasksEntries.Project, TasksEntries.Task;
I accept the start and end dates in a form and pass it like shown below. WorkDate is a column in the TimeTracker table and is not present in any other table.
Code:
strWhere = "WorkDate BETWEEN #" & txtMgrRptStartDate & "# AND #" & txtMgrRptEndDate & "#"
DoCmd.OpenReport "rptTotalProjectHours", acViewPreview, "qryTotalProjectHours", strWhere, acWindowNormal
When the report is invoked, I get a box where it says
"Enter parameter value" for Workdate..
Is there anyway I can get rid of the prompt? I never thought you needed the column name in the SELECT statement to be able to run this.I should add the I tried the query with the WorkDate hardcoded in there and it worked fine and returned the correct results
My database is slowly coming together. For the final part of this phase i would like to create a shortcut on each users desktop which runs a summary report of information within my database.
I have criteria set so upon opening the report the user is asked for which address they wish to see information from which works great. But I do not want users to have access to the database, I just want them to be able to click a shortcut, be asked what address they are looking for and for the report to ping up in a 'Print Preview' type layout so information can be seen and displayed but not altered. All users have the access program.
is this right?
Code:DoCmd.OpenReport "3rd Party Denial, Report, , [Claims Header].[Claim_ID]=[Forms]![claiminformation]![ReportForm]![Report_ClaimID], Normal"
I get an error stating you entered in either the property sheet or macro is misspelled or refers to a report that doesn't exist.
Hey there, if anyone could help me with the code for opening a report please. I have a main page with buttons, and need a button for opening reports.. thx
View 1 Replies View RelatedIs it possible to open a report using a combo box. At the moment all my reports are opened using cmd buttons. This is just a question of curiosity, I will continue to look into it myself of course.
View 8 Replies View RelatedI have a form in Datasheet view and when you click on a record in that form I want it to take you to a report with all the details of that record number. I have the On Click event set to run a Macro. The Macro is as follows:
OpenReport
Report Name Incident Report
View Report
Filter Name Open
Where Condition =[Event ID]=[Reports]!Incident Report]![Event ID]
Window Mode Normal
This Where Condition works for me open another form from the form with the same Event ID. Why does it come to a "new" Incident on the report?
I have a database and one of the functions is to track the purchase of fuel. Part of purchasing fuel is registering what the current odometer reads. My query gets the Vehicle ID, Date of Transaction and Odometer reading. I want to show a report that shows how many miles the vehicle is has traveled for a certain amount of time. If it's for 2013 then I'll put the criteria for 2014 in the date. This will give me a list of all vehicles and their odometer readings. I then sort the date by ascending.
In the report I group on vehicle ID and then I want to show all their transactions and add up how many miles were driven.
Vehicle 1
1/2/201
1/5/2013
1/15/2013
ETC...
Total Miles Driven in 2014: XXXXXX
Is there any way to put a shortcut on someone's desktop that will open a specific report in an Access database? I'm thinking back to the days of DOS when one simply added an argument to the command. I don't want to put it in startup and have it always go to that report. I just want non-Access users to go right where they need to without menus, etc.
:confused:
Thank you.
I want to create a report using the data currently held in a form. I found this bit of code somewhere:
DoCmd.OpenReport "report", acViewPreview, , "[job number] = " & txtFilter.Value
txtFilter is the name of textbox containing the data I want for the report. This works if in the table for txtFilter's data the field is set to a number. But if I set this field to text it comes up with a data type mismatch error. How do I solve this? (sorry new to access and vba). The reason I want to set it as a text field is so that I can limit the number of characters entered.
Thanks
Chris
Im sorry about the stupid questions!
How can I open a report in a view which will let me test my buttons? I only seem to be able to open it in print preview!
I have looked through the threads and have not found an answer to my question, so I post it hoping there is an answer as well as documenting useful information for other individuals.
The following code is what I am using to 'pull' data in order to print a certificate. It functions the way I designed it (verified by debug.prints and msgboxes). My question is how do I pass data to a defined report (certificate) based upon the results of a built recordset. When the report opens, the values come up as "#Name?". I'm guessing that the issue is syntax, but I just don't know. Here is the code I have so far (I've even commented it for y'all)...
Looking forward to your comments...
-BT.
Dim RSAgg As Recordset
Dim RSsrc As Recordset
Dim DB As Database
Dim strAgg As String
Dim strSQL As String
Dim strCert As String
'If there is no week number set, drop out
If Not IsNumeric(txtWeekNum) Then
MsgBox "You Must Specify a Week Number.", vbInformation + vbOKOnly, "Required Input"
Exit Sub
End If
' tblAggDesc contains the field names
' that have scores I want to evaluate.
' If someone makes a perfect score,
' then they get a certificate. Fields are
' a1, a2, a3, b1, b2, b3, c1, c2, c3.
strAgg = "SELECT tblAggDesc.AggCourse, tblAggDesc.AggDesc FROM tblAggDesc;"
Set DB = CurrentDb()
Set RSAgg = DB.OpenRecordset(strAgg)
RSAgg.MoveFirst
Do While Not RSAgg.EOF
If (Right(RSAgg!AggCourse, 1) > 1) Then 'rounds 2 & 3 contain additional information that is printed on certificate
strSQL = "SELECT tblScores.HEDR, tblRoster.Fname, tblRoster.Lname, tblScores.WeekNo, tblScores." & RSAgg!AggCourse & ", tblScores." & RSAgg!AggCourse & "X AS AggCourseX " & _
"FROM tblRoster LEFT JOIN tblScores ON tblRoster.HEDR = tblScores.HEDR " & _
"WHERE (((tblScores.WeekNo)=" & [txtWeekNum] & ") AND ((tblScores." & RSAgg!AggCourse & ")=100));"
Else
strSQL = "SELECT tblScores.HEDR, tblRoster.Fname, tblRoster.Lname, tblScores.WeekNo, tblScores." & RSAgg!AggCourse & " " & _
"FROM tblRoster LEFT JOIN tblScores ON tblRoster.HEDR = tblScores.HEDR " & _
"WHERE (((tblScores.WeekNo)=" & [txtWeekNum] & ") AND ((tblScores." & RSAgg!AggCourse & ")=100));"
End If
Set RSsrc = DB.OpenRecordset(strSQL, dbOpenDynaset)
If Not (RSsrc.BOF And RSsrc.EOF) Then
RSsrc.MoveFirst
Do While Not RSsrc.EOF
If (Right(RSAgg!AggCourse, 1) > 1) Then
strScore = RSsrc!AggCourseX & "X"
Else
strScore = ""
End If
'MsgBox RSsrc!Fname & " " & RSsrc!Lname & " " & RSsrc!WeekNo & " " & RSAgg!AggCourse & " " & strScore & " " & RSAgg!AggDesc
strCert = "Fname='" & RSsrc!Fname & "' AND Lname='" & RSsrc!Lname & "'" & " AND WeekNo='" & RSsrc!WeekNo & "' AND XCount='" & strScore & "' AND AggDesc='" & RSAgg!AggDesc & "'"
'Debug.Print strCert
' this is the point that I have problems.
' I want to pass RSsrc!Fname,
' RSsrc!Lname, RSsrc!WeekNo,
' RSsrc!AggCourseX, RSAgg!AggDesc to
' the report.
DoCmd.OpenReport "rptCleanTarget", acViewPreview, , strCert
RSsrc.MoveNext
Loop
End If
RSsrc.Close
RSAgg.MoveNext
Loop
RSAgg.Close
Set DB = Nothing
I have two pop up forms called "frmRepair" and "frmPaid" with a button that opens another popup form called "frmLabels". This final form has a button to print a report with the following code:
Code:
DoCmd.OpenReport "rptlblCombined", acViewPreview, , "CallID=" & Me.CallID
The problem is that when this report opens, it is always behind frmRepair or frmpaid and frmLabels so it can't be seen.
I tried to use the popup and modal properties on the report, but this does not seem to do what I need.
My next step was to hide frmRepair or frmPaid when frmLabels loaded and then hide frmLabels when I was printing the report, but the problem is that when the report closes I need to also close frmLabels (easy) but then I need to make visible either frmRepair or frmPaid, depending on which one was used to open frmLabels. How can I do that? How do I know which one of the two forms opened frmLabels to make it visible again?
What are my options to have that report open on top of everything?
I want to create a query that says starting on a specific date, every 6 months a reminder will pop up when a certain report opens (or form, preferably a report). So if I said the starting date is 10/16/2013, once April 16th, 2014 hits and this user opens the report, a message pops up.
Another way would be to add that message to the report itself, so it's not technically a pop up but its built-in to the report.
I have via macro that displays the main interface to my database; frmMain (Maximize). This form frmSelectUIC (Minimize) allows me to select a department number of the data imported for analysis.
All is fine, as shown here;
http://members.cox.net/mustang31859/access/before.gif
However, after opening then closing any report the frmMain no longer is displayed as before.
http://members.cox.net/mustang31859/access/after.gif
Why the shift?
Gunner....:confused:
I am building a faux Electronic Medical Records database for the purpose of training med students. I need a command button to pull up a report , but i want to delay the opening of the report (as if waiting for "tests" to come back or be uploaded) is there a way to do this with VBA? i read about the sleep api but i dont know how to get it to work or where to put the code
so what would i add and where would i add it to delay opening the report that is called EKG W/ Subreport ...
So I have a report that opens via Parameter. The SQL behind the query that runs the report is (I took out alot of lines that aren't necessary to answer the question)
PARAMETERS [Enter Your Box Id] Short;
SELECT DocumentsTable.OrganizationalID,
.
.
.
DocumentsTable.Status
FROM DepartmentsTable INNER JOIN (Year1 INNER JOIN DocumentsTable ON Year1.ID = DocumentsTable.RecordDateYearID) ON DepartmentsTable.ID = DocumentsTable.DepartmentID
WHERE (((DocumentsTable.Voided)<>'Y' Or (DocumentsTable.Voided) Is Null) AND ((DocumentsTable.ID)=[Enter Your Box ID]));
So if I click on the report, I get a little popup that says "Enter Your Box ID", and when I do, the report works just as expected.
However, I also want to be able to open this report via link from another report, and pass the Box ID #. I just can't get the syntax right. I would have thought it was this:
Private Sub ID_Click()
DoCmd.OpenReport "Find A Box", acViewReport, , "[Enter Your Box Id]=" & Me.ID
End Sub
However when that execute, I still get the pop up asking for "Enter Your Box ID"
I can open a report right with a wherecondition that opens a report based on an agent name..
DoCmd.OpenReport "SelectPSReport", acViewPreview, , "[PS_Agent]='" & Me.CboAgt & "'"
But it returns every record for that agent and I want to be able to specify the date that goes with the name.
For example Tom has a record for Feb 10, Jan 10, and Dec 13. I only want to see the record for Dec 13.
I am able to see this in my form by having a combo box for the agent and the date (the date box being based on the agent box). So now how can I add a condition to include the date combo box?
DoCmd.OpenReport "SelectPSReport", acViewPreview, , "[PS_Agent]='" & Me.CboAgt & "' And [PS_dDate]='" & Me.Cbodt & "'"
Adding the condition gives me and type mismatch error, which I think may come down to the date combo box on the form having 3 columns (only 1 is visible).
Basically when I click a button on a form I want it to run a report but only when certain criteria are met as entered in the 'Where Condition' box. I've managed to do this successfully when it is an equal to query but I now want to run a 'contains' query and don't really know where to start.
I have a text box on the form labelled [Desc] and I want the report to pull records where any text entered in the [Desc] text box is contained in the fields [Used Stock]![Model]