Modifying Report Changes Information...
Mar 16, 2008
I have a report I am generating with the design wizard. It uses 1 Query and all the information I want and order is correct when its finished. However, there is more information then what I want to display (I choose it to sum up everything, but I want to get rid of the thing that says 'summary of blah : 3 records) and I want to move the sum label to the far right and change it to total.
However I am finding out that any attempt to modifying the report in any way is changing my information. When its correct and working the report is 24 pages, each client has 3 sub types that come up and shows totals. However, if I make something invisible, move a label, change the text in a label or ANYTHING, then my report is completly screwed up, it drops to 3 pages, and loses all but 5 clients and only 1 sub type appears.
Why is modifying the report doing this?
Edit: its access 2003
View Replies
ADVERTISEMENT
Feb 20, 2006
Hello all:
Every week I download new information into a table. The download has all the information, but the table will spill out any information from the download that is already existing in the table.
How do I print a query or report right after the download, that will provide a list of the new information just downloaded.
Thanks for all your help!
View 2 Replies
View Related
Jan 18, 2006
I have a form with a list box that shows a number of reports that are ultimatly built from queries.
Depending on what information I have put in, some of the reports will not show any information, which is fine. Is there a why that I can tell the report to open only if there is information present or maybe change the colour of the name of the report in the list box.
This sounds a bit complicated but hopefully I have explained enough for someone to help, thanks in advance.
View 4 Replies
View Related
Apr 2, 2007
Hi all - I'm new here and am very glad to find this forum for some help!
I've been using Access for several years and I do the same type of reports over and over and all of a sudden I'm having trouble.
I typically modify existing queries - changing the name of the department in order to pull up their info - and then I format a report and modify that same report over and over with the "Properties" key where I pull in the new query info and then use SAVE AS to save the report with that department's information.
For the first time ever it will not let me SAVE AS with the new info. Sometimes it just does nothing at all - appearing to save but then the report isn't there - sometimes giving me a pop up box that says it couldn't find the search key - I don't even know what that is!
I can SAVE AS with queries, but not reports. HELP!
Thanks!
View 1 Replies
View Related
Dec 11, 2007
I have a patient information database which uses a filtered form to show records for only one patient at a time. All the records in the database are linked together using one number, the patient's unique identifier.
I have designed a report which draws information from separate tables and generates a complete summary of information. This report depends on having the ID number of the patient to generate the report.
Originally, I had envisioned having a command button on the data entry form, which would then automatically take the ID number from the form, and generate the report. Though good in theory, it never worked out in practice, as I could never figure out how to make it happen.
Is there a way to design a macro or some other thing that would take the ID number from the field on the form, and use it to generate the report? Currently, I have it configured to prompt me for the ID number. When I enter it manually, the report is generated perfectly.
Thank you in advance,
View 2 Replies
View Related
Oct 9, 2014
I need to split out information in a report and I'm not sure how to do it.
The report is based on the following query:
Code:
SELECT tblHazardClass.HazardClass, Product.ProductName, Product.Package, Product.Size,
tblStoreProducts.Cases, Product.Units, Product.ReportUnits, tblStoreInformation.StoreName,
(([tblStoreProducts].[MaxUnits]*[Product].[Size])/[Product].[ConversionRate]) AS QOH, tblStoreProducts.StoreKey
FROM tblStoreInformation INNER JOIN (tblHazardClass INNER JOIN (Product INNER JOIN tblStoreProducts ON Product.UPC = tblStoreProducts.UPC) ON tblHazardClass.HazardKey = Product.HazardKey) ON tblStoreInformation.StoreKey = tblStoreProducts.StoreKey
WHERE (((tblHazardClass.HazardClass)<>"NON-HAZARDOUS"))
ORDER BY tblHazardClass.HazardClass;
My issue is that the products can have a PhysicalState of 'L' (liquid), 'S' (solid) or 'G' (gas). As of right now, only one of the HazardClass entries has multiple physical states (L or S).
My report details each product in the hazard class and totals the amount of that hazard class. Obviously, one cannot add gallons to pounds and come up with an answer that has any meaning whatsoever. How to have the report total the S and the L within a hazard class separately.
Do I do this within the query, or within the report itself? I've already considered making 2 separate Hazard Classes for the one in question (Corrosive). However, since these Hazard Classes are official classifications per the International Fire Code, that's not really an option.
View 1 Replies
View Related
Oct 10, 2014
I am trying to get some totals on a report, but the totals are from a table that the form is not related to as such.I am trying to get a textbox to show how many records in a query match the given criteria and show me the number of records that match. I was thinking DCount, but not sure if it will work?
I have a query called 'VehiclesAllocatedToday' which is already filtering records to show only those allocated today. On a report that shows Unfit Vehicle (and is related to a different query), I would like a text box that shows me how many records there are where the text "Type One" is in the TypeRequired field.
View 8 Replies
View Related
Mar 16, 2005
Hi there
This im sure is an easyone to most of you but for me I cant find away around my problem!!
I have a form I use to enter infomation and I have also made a report that I am trying to get the infomation from the form onto the report.
I would like to have a command button on the form that once I click it, it then prints out the report.
How do I get the information from a form to a report as when I have tried doing this myself it only allows me to use information from a query or table.
Any suggestions would be great for this newbie!!!
Thank you
View 2 Replies
View Related
May 28, 2013
I have a Table with Emp_ID and Details of my Employees. I have created a query and set parameter [Enter Emp_ID]. When I pull up a report, I get a pop-up and it asks me for the Emp_ID. When I input same, I get the information I need. Everything is good so far...
My Question... I have designed a report when user will need to enter Emp_ID and click on a button and they will get the report "EMPReport" without having the pop-up window asking for EmpID.
I have been using the below code but I get the pop-up again and I need to enter the Emp_ID again to view the report
See below Code:
Code:
Private Sub Image11_Click()
If IsNull(Me.Emp_ID) Or Me.Emp_ID = "" Then
MsgBox "You must enter an Emp ID.", vbOKOnly, "Required Data"
Me.Emp_ID.SetFocus
Exit Sub
End If
DoCmd.OpenReport "Rpt_HR1", acViewPreview, , "[Emp_ID]= " & "" & Me!Emp_ID & ""
End Sub
View 13 Replies
View Related
Apr 2, 2013
I have a popup form that prompts for a value and has a button defined with an OnClick,OpenReport with a WHERE clause. The value the user enters is placed in the WHERE clause.
The report is generated using a Query and the WHERE clause.
I was very surprised -- it works!
Now I'd like to add a chart to the report that represents the information on the report. How can I do this without calling the query again? Can I somehow reference the information that's in the report?
View 1 Replies
View Related
Sep 17, 2013
I have a template report (R_CurrentJobs) with fields from database.
Fields: FirstName, LastName, Email, JoB Number, TechID, TechName, JobLocation etc...
The query Q_CurrentJobs is a list of jobs to send to the tech for today. (These are open jobs queried).
I need to run this query on the report (R_CurrentJObs) and send the report to each tech based on their individual jobs. The jobs are connected to each tech by its TechID.
The report list the job information for each tech (JOb number, Location details etc.)
When I click the button to send the email. I want to email each tech individually based on the Q_CurrentJobs query using R_CurrentJobs. (DoCmd.SendObject)
View 11 Replies
View Related
Dec 2, 2013
We have a huge report in our company that includes all our customers, all the visits done by our sales reps, all the products they have and if a product was out of stock in the visit or not.
I want to create a querry that exports a table with 3 columns:
1-->customer
2-->date of last visit
3-->average of out of stock products on the LAST visit
I tried to make a pivot table, managed to grab the last visit (setting the date to maximum) but the average of out of stock products were aggregated totally and not for the last day.
How to handle this in MS access 2010?
View 2 Replies
View Related
May 29, 2015
I am trying to create a report that will show me any record that does NOT have a specific field met. I have a basic query set up to display the information desired, but need it to refine the search to just the records missing a specific part in a different table. The missing information does not have to be shown in the report.
View 7 Replies
View Related
Apr 7, 2015
I have a question related with a report i`m using to print labels. I am not familiar with Access and this is quite a challenge to do it alone.
In the attached file there is a report called "MICRA", when started it asks that you want to select (default is 1 and special select in this case is 11) and next it asks "SPS", the value entered there is printed in the bottom right corner of the report.
My question is is it possible if in the SPS field is entered a special value (for example "MASS") to print 30 labels of each selected label with text in this fiels = "val.1"; 15 with text in the field = "val.2"; 10 with "val.3" and 10 with "val.4" and after that to print next label with same rules...
And if not entered "MASS" to print just 4 copies of every label.
View 6 Replies
View Related
Jul 1, 2014
The database I have created is to produce product description sheets. From the switchboard, the user clicks a button that opens a form on which they can enter about 5 bits of information , then select the product they wish to produce the product sheet for and when they click a button, the product sheet is produced. The product sheet is a report with a subreport. The information entered on the form is included with more information from the database on the main report and the subreport is based on a union query which combines information from 2 tables to list the items included in this product. (the information the user inputs into the form is information that changes depending on the situation and is not stored in the database.
Now comes the next step in this process. Just as the information input into the form is temporary information, there is also one bit of information that needs to be input on a temporary bases for the items that are listed on the subreport. Each of the items on this subreport needs an assignment of AAA or BBB that needs to appear in the subreport on the line with that particular item. There can be one item in an product, or there can be many and the listing of AAA or BBB can be different from one item to the next or they could all be the same. The assignment needs to be made on an item basis.
My thoughts on this were to have a subform appear once the user selects the product on the form which would list all of the items and allow them to select either AAA or BBB in a column on this form. Then when the click to open the report, those assignments would carry through to the report.I have been able to create a continuous form displaying all of the items based on the union query but that's as far as I can get. Not even sure if that is the way to go.
Is there a way to reference the AAA and BBB input on the subform I described so that it displays in the report.
View 7 Replies
View Related
Apr 19, 2013
I have a question about summing information on a report. I am developing a report to see have percentage of time Staff work within a certain area. On the data base the time percentages entered as ranges e.g. 75-100%, or 11-25%. We needed to figure out the total percentage of time so I created a report to add the total time. I used a text box which it titled FTE_Total if embedded iif functions to display the total time. Here is the function I used:
=IIf([FTE_Percentage]="76-100%",1,IIf([FTE_Percentage]="51-75%",0.75,IIf([FTE_Percentage]
="26-50%",0.5,IIf([FTE_Percentage]="11-25%",0.25,IIf([FTE_Percentage]="1-10%",0.1,0))))).
This worked perfectly however I cannot get the FTE_Total fields to sum. I tried the following function in the group footer: =Sum([FTE_Total]) but everytime I try to look at the report in report view I get a message asking for the FTE_Total Parameter.
View 1 Replies
View Related
Dec 2, 2013
we have a huge report in our company that includes all our customers, all the visits done by our sales reps, all the products they have and if a product was out of stock in the visit or not.
I want to create a querry that exports a table with 3 columns:
1-->customer
2-->date of last visit
3-->average of out of stock products on the LAST visit
I tried to make a pivot table, managed to grab the last visit (setting the date to maximum) but the average of out of stock products were aggregated totally and not for the last day.
how to handle this in MS access 2010?
View 9 Replies
View Related
Jul 21, 2005
I am still new to Access and am loving the learning process. I am stuck on a problem though. I have serached the forums here and various other places and haven't found the info I am looking for. I was wondering if there is a way to have a relpica (or a seperate database that can syncronize) that has a modied design. I want the main one to have everything on it, reports/forms/ add/delete/ect, but I want another one that is simply a data entry form, with only the option to input data. I would love to do this and avoid system security measures with usernames and passwords. From my reading it doesn't seem like I can do this, but I still hold in my heart a glimmer of hope. Please help, and if you can explain the process to me, all the better (its how I'm learning).
View 1 Replies
View Related
Sep 8, 2006
Hello, So far you guys have been helping me through my database that i am building. Thanks so far!But at the moment i need help on one more aspect of this Database Job. Currently i have a database that supports and holds records for jobs that are under contract. That means we have certain numbers and statistics of Jobs and there properties.what i have right now is a table with a couple fields. (there are more than this but this is just for example) project number "primary key" aerial units buried units planning units ICGS unitsThat part works fine with the current table. But since jobs are under contract the number of units might change further on during the design and construction. There may be more than one mod.Now the part that im stuck on is the most important. I need a table/query/form/report that i can input the changes to particular units. Like i said before there might be more than 1 mod. So i need to make it show ALL the mods ive done to a project.This is a huge part, and any help would be very much appriciated. ~Thanks so muchJon
View 2 Replies
View Related
Jan 16, 2006
Is it possible in access to modify user permissions with SQL?
View 3 Replies
View Related
Jan 10, 2007
I'm far from being an Access expert, so forgive me if I don't make perfect sense. I have a database where questions and pages are entered on a daily basis. I have a table with three columns: data, pages, and questions. I have a query where I can sumarize the data by Month. (I created a query by using the wizard and I chose to summarize by Month). This returns all of the data, summarized by Month. I want to limit it further by year, so that it would return 12 months of data. I am at a loss! I've tried typing [Type Year] but I get no responses. I was able to create a limiting one where I type in the month and year [Type Month and Year] that works great, but it doesn't translate into the year only.
Thanks for any help you can lend!
Michele
View 3 Replies
View Related
Mar 14, 2006
Hi to all. I need to modify some existing error messages in my form so that I can create messages that will be more user friendly. I know that it is possible but searching for this issue was a bit confusing. How do I do this?
Thanks in advance
View 1 Replies
View Related
Mar 21, 2006
I have an Access database split into FE/BE. For the front end, each user has his own directory on the lan. The directory is named with the UserName.
I've created an installation program in Access. It basically installs the icon on the user's desktop using Environ("UserName"). It also installs an Excel file the program needs on the hard drive.
Here's the problem...Right now, the installation program grabs the shortcut off the lan and installs it, but that shortcut has one user's UserName hard coded as the target. That one piece of information needs to change for each person. Is there a way for the program to modify the target based on the username of the person running the installation program? Without this piece, I need to visit every user after he runs the installation program and modify the properties of the new icon to point to his specific directory.
Thanks for any help you can provide!!
View 2 Replies
View Related
Sep 1, 2007
Hi guys, me again. Have a few functions that I would like to put into action.
1)
I currently have the following code that exports multiple queries to excel. All works great I am happy to say. Currently the date criteria are hard coded into the queries. Well now my users would like to enter date ranges instead. Currently when I click on my toggle button, the users are asked if they are sure they want to start export to excel. I still want that to happen, but how can I change my code to prompt for startdate and enddate before running the export. I would rather use an input boxes to enter the date range instead of using a form. Would this be possible?
2)
Right now excel is displayed on the screen while the export is in process, how can I hide excel and then have it appear after its done with export. If the user clicks within excel during export, it sometimes freezes. And how can I apply my module for the progress bar to display the progress of the export. I have provided the code of the progress bar that I am using, that I found on this forum.
Many thanks for any suggestions.
Option Compare Database
Option Explicit
Public Function ExportDataExcel()
Dim strFilePath As String
Dim strFileName As String
Dim strFileTemplate As String
Dim strMacroName As String
If (MsgBox("You are about to generate the LAR Monthly Report. Are you sure you wish to continue? You cannot cancel this procedure once started.", vbOKCancel) = vbCancel) Then
Exit Function
End If
'''''''''''''UPDATE THIS DATA WITH YOURS''''''''''''''''''''''''''''''
'Fill in the following with your files and path
strFilePath = "R:Call CenterCall Center DepartmentsMortgage DeptMortgage Statistics & Tracking"
strFileName = "BigLarOutput.xls"
strFileTemplate = "BigLarTemplate.xls"
strMacroName = "DeleteBlank"
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''
'This deletes the old file
Kill strFilePath & strFileName
'This recreates your file with the template
FileCopy strFilePath & strFileTemplate, strFilePath & strFileName
'This is a custom function I built to set Excel as an object and you can access/export
'to a workbook programmatically. 'openexcel' is stored in a module called Functions.
'This will open the new file that was created previously
openexcel strFilePath & strFileName
'''''''''''''UPDATE THIS DATA WITH YOURS''''''''''''''''''''''''''''''
'Export data is another function that will export your data.
'Update the query Names to your real Query Names
'Update the Sheet Names accordingly, with the Query it is assiged to
ExportData "qryHoeqDotApproved", "HOEQ DOT APPROVED"
ExportData "qryHoeqDotReceived", "HOEQ DOT RECEIVED"
ExportData "qryHoeqDotDenied", "HOEQ DOT DENIED"
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''
xl.ActiveWorkbook.Save
'The Application.Run will run the Macro(s) that you saved in your spreadsheet
xl.Application.Run "'" & strFileName & "'!" & strMacroName
xl.ActiveWorkbook.Save
'Uncomment/Comment these to close out the workbook
'xl.ActiveWorkbook.Close
'xl.Quit
Set xl = Nothing
End Function
Private Function ExportData(strQuery As String, strSheet As String)
Dim intR As Integer
Dim rs As Recordset
'After you open that Object/Workbook, you refer to that workbook now as 'xl'. You will
'use it later, but now you have to access your queries through this code and to do so
'you need to use a recordset.
'strQuery is the name of the Query that you passed with the Function. You can also
'use an SQL string.
Set rs = CurrentDb.OpenRecordset(strQuery)
rs.MoveLast 'moves to the last record
rs.MoveFirst 'moves back to the first record
'You can use record count to make sure there are records in your Query/Recordset
If rs.RecordCount < 1 Then
'There are no records
MsgBox "There are no records for " & strQuery
Else
'There are 1 or more records. Now Select the sheet that you will be exporting to
xl.Sheets(strSheet).Select
'Now you need to loop through the records. 'intR' was dimmed at beginning of this
'function and will now use it to create a loop or 'For, Next'
'Starts with record 1 and gets the count of records in the recordset so it knows where
'to stop.
For intR = 1 To rs.RecordCount
'Now we need to export the recordset/query to the workbook/object we opened earlier.
'Remember 'rs' refers to the recordset & 'xl' refers to the workbook
'xl.cells(ROW,COLUMN).VALUE = rs.fields(INDEX).
'This is how you will fill in the value of a cell on the workbook. For the ROW you
'will want to add + 1 if you have Headings on your sheet. The INDEX for rs.fields
'refers to the columns of the recordset/query. The first column of the recordset
'starts with the index of zero.
xl.cells(intR + 3, 1).Value = rs.Fields(0)
xl.cells(intR + 3, 2).Value = rs.Fields(1)
xl.cells(intR + 3, 3).Value = rs.Fields(2)
xl.cells(intR + 3, 4).Value = rs.Fields(3)
xl.cells(intR + 3, 5).Value = rs.Fields(4)
xl.cells(intR + 3, 6).Value = rs.Fields(5)
'If there are more columns to add than this just follow what I have above
'Moves to the next record
rs.MoveNext
Next intR 'Loops back to For and enters data for the next row
'Once the export is done, this just puts the cursor to A1 on each sheet
xl.range("A1").Select
'Clears the recordset
rs.Close
Set rs = Nothing
End If
End Function
' Code inside progressbar module
Option Compare Database
Option Explicit
Function RunProgressBar(lLth As Long)
If IsLoaded("frmSplash") Then
Forms!frmSplash!Box20.Width = lLth
End If
End Function
Function IsLoaded(ByVal strFormName As String) As Boolean
On Error GoTo Err_IsLoaded
Const conObjStateClosed = 0
Const conDesignView = 0
If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> conObjStateClosed Then
If Forms(strFormName).CurrentView <> conDesignView Then
IsLoaded = True
End If
End If
Exit_IsLoaded:
Exit Function
Err_IsLoaded:
MsgBox Err.Description, , " Service Operations"
Resume Exit_IsLoaded
End Function
Public Function Pause(NumberOfSeconds As Variant)
On Error GoTo Err_Pause
Dim PauseTime As Variant, Start As Variant
PauseTime = NumberOfSeconds
Start = Timer
Do While Timer < Start + PauseTime
DoEvents
Loop
Exit_Pause:
Exit Function
Err_Pause:
MsgBox Err.Number & " - " & Err.Description
Resume Exit_Pause
End Function
View 7 Replies
View Related
May 6, 2013
I have a bunch of charts in my forms, and my boss wants to be able to adjust axes. I have added a context menu that brings up a little form and takes values that are then thrown into the graph by way of a function. URL....Here's the current code:
Code:
Public Function AdjustTREFAxis()
'See form fTREFaxis
Dim objChart1 As Object
Dim objAxis1 As Object
[code]....
notes:
-ignore the global vars, they are a temporary measure.
-"Graph19" I know, not good. I never expected to call it in code though , I'll fix it later
-the while loop keeps the rest of the code from running until the axes have been submitted.
Ok, so this code works for the x and y1 axes. I assumed that Axes(3) would iterate to the Y2 axis, it does not...I also found that after you change the axis the changes are persistent for the graph even upon repaint/requerying it. This is problematic and I need a way to set an On Load event that resets the graph to "autoscale"
Lacking that, how to access the Y2 axis, or how to reset the axes to autoscale that would be keen.
View 1 Replies
View Related
Mar 17, 2006
Is it possible to modify data values in a cross tab query?
I tried and it won't let me delete or type anything.
Thanks.
View 1 Replies
View Related
