Reports :: Track Data Changes - Highlight Modified Values
Jan 22, 2015
I'm building a report which should highlight/change text color in field values in a modified table which have changed relative to field values in an original table. I'm using the same conditional formatting on all my fields: e.g. on the field named [Primary Instructor] in the report on the modified table, I have "Value <> Reports![F2015original]![Primary Instructor]", where I reference an identical report on the original table. Both are loaded.
My problem: it's reading all values as changed/different, even when both visual inspection and copy/paste indicate that the values are identical. Thus my report highlights all values for the field, rather than only those which have changed. The reports have identical non-conditional formatting and data type.
I modified some reports in my music library database to have the composer first name and last name appear as one field. I'm not sure what is wrong with them, and request another pair of eyes look at them to see if the error(s) can be found. Please bare with me, this is my first post to this forum so I'm not sure if I am following the rules correctly, yet I have posted to the Java area before. I'm not sure what I should include for help, since when I try to include my database in *.zip format, it is saying it is too big to send. Any ideas on what I should send?
Is there a way to only highlight the last row of the detail section in a report?
I tried the following code in the "Format" but could not get it to work in Access 2010.
If Me.ClaimStatuses = "Total Potential Recoverable" Then Me.Section(acDetail).BackColor = vbYellow Else Me.Section(acDetail).BackColor = vbWhite End If
Where "ClaimStatuses" is the control text box and "Total Potential Recoverable" is the value I want to equal so this row which is the last row will be yellow.
I am exporting a report from access 2010 as PDF to email and the email attachment is coming highlighted. I need it to be white like the report when seen on screen with alternate rows to NO. if I export it to PDF and view it from within access it comes perfect, but when exported to an email, its coming shaded.
I have a query called "Program Status Count" that keeps track of the current status of various programs. There are three statuses: Closed, Deficient, and WIP.On the Query, I used "Total:" row and have two fields: StatusID field set to "Group By" then in the next column I have the same StatusID Field set to "Count" (it auto-renamed itself to CountofStatusID)...which on the query does what it is supposed to do:
Closed, 2 Deficient, 1 WIP, 1
When I go to the Report Design and try to make a Pie Chart (or any graph) it asks me what query I want to use, so I point it to the Program Status Count Query and select the only two fields I have in the query.This is the Row Source Code:
SELECT [StatusID],Count([StatusID]) AS [CountOfStatusID] FROM [Program Status Count] GROUP BY [StatusID];
Sometimes, it will show the appropriate fields but then it will disappear in Report View, switching to Print Preview does not fix the issue. Actually, it often deletes the correct information in the chart and use the PK autonumber instead of the words.Othertimes, the default data shows up and nothing I do changes it. I have even done the copy/paste of the query table in the report (doesn't work when the graph needs to update.
I'm a novice Access user and I've been put in charge of changing this. The database is on a shared device. I copied a working frontend (access file) to another machine and used the link table manager tool to find the new database. Now when I open the table view mode I can see the table data, but when I open the form, it doesn't show the data at all and it only lists 1 record. I know this sounds like a really stupid question, but is there one more step or am I in insert mode?
I'm completely new to Microsoft Access. This project was thrown my way. I have an accounting database to track payables, receivables, financials, and deliquencies/collections. Is there a way to generate a report for any of the items in the previous sentence that haven't been completed to keep track of workload.
I have created a database that I am happy with, and I am about to receive information to populate it with. I know I am about to be asked to about security and logging of changes made to the data, which I know nothing about!!
As a result before I get into this part of my project I would like to know myself what is and what is not possible!! Currently I have 2 tables and 3 forms, one of which is a sub form........ All changes will be done via the forms, adding new records, editing existing records or even deleting obsolete records.
•Is it possible to record any changes made to the live data? oFor this to happen I presume that I need to setup users and access rights? •What type of information can be saved if any? oWhat form would this be saved in and where (a temp table?)?
While typing this a change of plan – if it is possible I am going to start building it regardless, as it will enhance my database.
I have to join multiple values into a string for summarizing data on reports and exports. This process in vba is taking up to 10 minutes to process and will get worse as the size of these reports grow.
My method so far is to query the individual items into a recordset, loop through the values, adding them to the string then return the string in the query.
Here is an example:
Public Function SO_Description(intSO As Integer) As String Dim db As DAO.Database Dim rs As DAO.Recordset Dim strSQLSelect As String SO_Description = "Profiles: " Set db = CurrentDb
One more question... I'm loving Access... much more than Lotus Approach!! :-) Here's something I've wanted to do in Approach... how do I do this in Access???
REPORT showing all customers
1. John Doe, owes 9.00 2. Jane Kane, owes 10.00 3. Rob Johnson, owes 30.00 4. Albert Icar, owes 22.00 5. Johnny James, owes 5.00
Query all customers owing more than 10.00 (in this example it would be Rob and Albert).
In the report, I want Rob and Albert's name to be red font only, while everyone else is just regular black font.
Thanks to all posters who have assisted me thusfar, and anyone that can help with this.
I have built a table off a form that is being used for record keeping and my question is.... This table will be constantly updated with new information so how can I make it to where when new info is added the old info will not be removed or written over?
Im working in MS Access 2003.Im creating an audit trail for users actions in a form, to monitor and keep track of what fields were updated/entered/removed from the database, these actions are essentially done from command buttons (and the audit trail, is coded on the button click)
However, I have a few search screens, that return a subform listing results. These datasheets returned are effectivly like opening the raw table and hence are editable, (permission to change is granted as they are admin only searches, yes, they can change the raw table)
How can I track the change to a raw table field, ie, if someone updates the field SURNAME from 'Smth' to 'Smith', is there something that triggers this (obviously if it was in an interface textbox, you could audit this) is their a field change. The only way i can suggest it make a recordset of the intial results, then compare that to the results in the table as they leve and compare the two.
I am re-designing a database for 2008 and trying to eliminate my Make Table Queries as I have found them to be somewhat consistant over the last year, particularily when the users do not open the database on a given date. It seems there should be a simple way to accomplish what I want but I am struggling and need some assistance.
I have attached a sample of a few tables from my database, Open Cases, Closed Cases, and Date Today. The Open and Closed tables change daily due to a Corporate download and contain several date fields which have different meanings. As new cases are opened, they go on the open table, and as an open case is closed, it moves to the closed table. The tbl_Date Today is pre-populated with dates of working days only. I have a query called "Count Of Shelf Comb" that counts the number of open cases as of today, which in truth is for all activity through the previous business day. What I want is to have a query that will show each date on the tbl_date today as well has what the total count of open cases was for that date......a permanent history of the amounts.
How can I accomplish this without using a "Make Table Query".
I am looking for a data model to define and track proficiency/currency.
Example:
You earn a licence to do X. You need to renew your licence every 2 years or so. But also, to maintain currency, you need to do what the licence gives you the right to do often enough, or else you need to do a test.
Specifically, you need to do X a specified number of times N within, say, last 3 months at any one time to maintain currency. If you have lost your currency, you need to do a test/check. So at all times, you need to have N times on record within last 3 months, or a test.
In addition, to do X legally, you also need to document that you have done X at least 10 hours within, say, last 6 months, or that you have another test within those last 6 months. This is independet of the previous requirement (concerning the number of times you did X).
Both requirements must be fulfilled at all times for you to do X legally.
So, any suitable model out there? Essentially, something capable of defining and tracking currency for various ratings and license privileges of pilots would do just fine.
I have a table that track monthly data for several thousand products.
The idea is to grab the first month and the first three months of sales for each product.
So I simply need to have a statement that scans through a predefined set of fields and seeks the first none zero value. This become the first month of sales (even if it is the 5th month of the year). It then needs to be able to grab the proceeding 2 months to create a sum of sales in the first 3 months. (however that would be for a second field, so presumably that is just a slightly more complex version of the same formula used to find the first month of sales).
In the case of a system that keeps track of checking in and out (e.g. library books), where is the archive data stored? If a person makes 30 trips to the library, obviously one record will be the current visit - but where do you keep the 29 other visits?
Do you create a separate table for old check-outs: Current_Check_Out and Archive_Check_Out?
Or do you just leave the data in place and as-is: All_Check_Outs?
How to generate either a form or report able to show me the history of data changes made by users in the fields of Access Objects. I'm more interested in Forms, since the users will use Forms to change data in the db.
Let me give an example:
My db has, for instance, a form named Frm1 and a form named Frm2. Both were built including several fields such as text boxes, combo boxes, etc.
So, if the user Paul first changes the field Color of the record Id 235 in the Frm1 from Green to Yellow, and then, down the road George changes the field Size of the record Id 14 in the Frm2 from Big to Small, I need to know what changes were made, by who, and when changes happened. Also, it is important to know if a record Id is added or excluded.
My final intent is to have a report/form that shows some sort of user activity log which I think could be like this:
I have a report in which a textbox generates numerical values and letter values. I want to...On report load - if textbox = numbers then hide otherwise show if it contains letter values.
I have a Access 2003 file and I want to filter anywhere where there last name is "expired" and change the column first name to say "no". How do I do that?
I am trying to get calculated fields in my report to work but zero values in the data are throwing up #Div/0! and #Error! and #Num!
The main data fields I am trying to work with are:
VehicleReading PreviousReading VehicleLitres
My aim is to track mileage and consumption for a fleet of vehicles and show daily and weekly averages in mileage and consumption and then to flag 20% increase in consumption.
I have a calculated field in my report named Kilometers,
= [VehicleReading]-[PreviousReading]
and also a calculated field named Consumption,
=[Kilometers]/[VehicleLitres]
My sum and average calculation fields in the group footers are only working where there are values above zero in the Kilometer and VehicleLitres and Consumption fields.
In reality there are some days where there has been no travel, so some records will have a zero for the Kilometers field and there are also days when there has been no refueling so there is a zero for the VehicleLitres field.
I have tried using the expression builder to create an IIf function but to no avail.
I have tried copying the syntax suggested by the Expression builder:
I need to able to quickly find which the last records that have been modified. I have an invisible field "txtModified" on the main form and selecting when changes where made to the record on the main form is easy using a qry on that field. The problem I face is that the form with all details of a particular item contains 2 subforms. Changes to data on each of these subforms doesn't effect the time/date on the main form. However, each of the subforms is also time stamped upon change.The qry to select the last records changed therefore needs to look at 3 fields and select records with the latest date in any of the 3 "Modified" fields. How do I do that? I suppose I'll have to put an expression in the qry?Thanks for your advice.Walter
I have this code in a Query... [Type in your product, such as mtt20]
Once you type in the product it will go and find it and display it.
My question is : Is there a way to modify this so it will allow me to enter more than one product and then go and get the results. Preferrably the easiest way possible as I am a new user of access and no program training...
