Track Form/Query/Report Usage
Jul 25, 2007
Hi
About 10 years ago I wrote an Access application which is still in use. Over the years certain parts have become superfluous and new parts have been added by a variety of users. Thankfully they have stuck to the original ideal and only used macros and queries - and not modules of badly written code.
However, the system is beginning to creak and I have been asked to rewrite the system from scratch with a shiny new interface, cutting the dead wood and incorporating those new features that did not have a half life of one report.
I would like to be able to monitor the use of the system in order to be able to determine just which tables, forms, queries & reports are being used. I'm not interested in auditing who added what to which table, so a 'normal' audit system is not required. Can anyone tell me how I would record which of the above are being used and how regularly?
I have asked the users and (of course) they don't know.
TIA
View Replies
ADVERTISEMENT
Dec 13, 2014
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:
Date--------Time----------User-----Form------RecId------Action------Field-----From-----------To
Dec12,14---15:12:35-----Paul-----Frm1--------235------Changed---Color----Green-----------Yellow
Dec18,14---07:05:19-----George--Frm2---------14------Changed---Size-----Big--------------Small
Dec22,14---09:23:59-----Sean----Frm2--------116------Deleted
Dec23,14---11:07:03-----Paul-----Frm2--------321------Added
etc...
By sorting this output by date, form and field it will easy to check/audit for user processing mistakes.
I know enough to build tables, queries, forms and reports, but don't know how to structure such thing. What kind of form event should I setup?
View 2 Replies
View Related
Sep 26, 2005
Over the last 12 months my database has grown in size.
I'd like to delete queries that are no longer used. Is there a utility I can use that will list where all my queries are used, ie used in other queries + reports.
Does anyone know?
Thanks
View 2 Replies
View Related
Jul 9, 2005
hi Guys,
need help in using query, plz let me know that how to use designed query in Reports ?
thanks in advnce.
View 1 Replies
View Related
Feb 19, 2008
So I have a query which prompts the user for a username to search by, GetUsername.
Im used to TSQL usage which would be something like:
exec GetUsername 'blah'
My goal is to generate about 10 reports based on this query but with different (hardcoded) input. I do not want to prompt the user while generating these reports.
How would I invoke this query with a hardcoded parameter? (Id rather not save 10-20 versions of it).
View 9 Replies
View Related
Jul 29, 2013
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.
View 5 Replies
View Related
Aug 14, 2006
Hy, I have this db where a user can purchase and item and total stock number needs to be updated.
I can calculate how many items are left but how to update the warehause table?
I can live without table updating, but if the same item is purchased multiple times my query return incorrect values as the number from which the formula subtracts is never changed.
See attached database.
View 1 Replies
View Related
Sep 27, 2006
Hi folks,
This is my first posting to this forum and I hope someone will be able to help me out with this.
I have a table called FILETRANS in a DB.Essentially this table is to track the in-flow and out-flow of files in a documentation unit.
The schema of the table is as follows
Filetrans_id Pk int Auto
Files_id int
Filetransdate date
Filestatus_id int
A filestatus can either be 1(IN) or 2 (out)
I need a query that will give me a list of all files whose last recorded status is 2(out) and vice versa.
Any help will be greatly appreciated.
Regards
Charles
View 4 Replies
View Related
Dec 28, 2007
I would like to track the revision history of table records. To do so, I have created tables with two-key fields. A typical table is tblPO_Data. The first key field is PO_Number and the second key field is PO_Revision. Data fields follow.
A key element of this table is that each record may have any number of data fields left blank AND later revisions may only show the "changed" field values.
To Illustrate tblPO_Data:
PO_Number PO_Revision Length Width Height Weight Quant UPrice
100 _________0___________10_____15___________________2 ____20
100__________1_________________________30
100__________2_________________________35____100
200 _________0_______________________________________1 ____10
NOW I would like to create a query that lists one record for each PO number, showing the most recently revised data. The desired result would look like:
PO_Number PO_Revision Length Width Height Weight Quant UPrice
100_____________2_______10_____15_____35_____100__ ___2____20
200_____________0_________________________________ ___1____10
Please realize that this is an example. There would be numerous one to one tables for each PO number to reduce the number of blank fields (ie. only tables with changes would be get revised records).
Can someone suggest a way to create a query to accomplish the above requirements, thanks in advance, Jeff...
View 3 Replies
View Related
Aug 16, 2014
I want to keep track of when data in a form was last changed.I only need to track this when the form is closed. not every time a record is changed.
View 5 Replies
View Related
Nov 20, 2012
I have a form based on a query. I've disabled "Navigation Buttons" on the form and am trying to recreate their functionality in a little more user friendly way. I've created next / previous record buttons and have those working great.
I'm now trying to re-create the record counter / tracker. I'd like to setup a box that shows which record I'm on (this can simply be based on the order the query returns them, the same way the navigation buttons does it when they are enabled. ), and how many records there are total in the query.
I tried messing around a little bit with =DCount but wasn't able to make it work I suspect because I'm counting the number of records returned in a query, not in a table.
View 14 Replies
View Related
Mar 29, 2006
How do you calculate usage? For example...
If I've got a field named Time, then a drop-down box with "Morning", "Afternoon" and "Evening"
How do I make a query so it displays the number of each time...
For example...
There's two times for Morning, three for afternoon and one for evening.
I tried using the 'Sum' total and the criteria of "Morning" but it says data mismatch. That was merely a guess, so I would like to know how to do it.
Like...
Time
Morning: Afternoon: Evening:
2 3 1
Thanks.
View 2 Replies
View Related
May 26, 2005
I have a continuous form with an unbound text box (for search critera) and a command button to kick off the search, which simply applies a filter.
When a user enters text in the unbound text box and hits "Enter", I want the search to be kicked off (instead of having to tab over or mouse over to click the "Search" command button. This is the default functionality of an HTML <form>, so users kind of expect it. But I'm having trouble implementing it in MsAccess.
I've tried using the KeyDown property of the unbound textbox, but no luck, since the KeyDown method occurs before the data in the box is saved. I'm guessing this is related to detecting what key has been pressed but my issue seems to be the order of events occurring on the form.
Any suggestions? This *must* be common situation, but I've found no related topics in searching the forum.
View 3 Replies
View Related
Mar 31, 2014
I have two tables (one is a query)
Table 1 (query based)
EMP_ID
Prev_Emp_ID
EMP_ID_DDSK
332-123
1
500
332-133
1
501
332-144
0
332-156
1
502
332-654
1
503
332-456
1
504
332-967
0
Table 2
Res_Numbers
Num_Of_Employees
500
10
505 - after 5 numbers are placed
Will be added for next hire
My goal: to place employee numbers into new employee field "EMP_ID_DDSK" (table 1), Numbers will come from (from "Res_Numbers" field (table 2)
Example: If field "Prev_Emp_ID" = false
Get the next number in line from table 2 "Res_Number" and place it in table 1 field "EMP_ID_DDSK".
Example: if "Prev_Emp_ID" = false for 5 new employees. Take the next set of numbers and place into field "EMP_ID_DDSK".
View 4 Replies
View Related
Jun 21, 2005
I have a form from which people view records out of a db.
I would like to log the date/time and the key record they view into a table
The usage table has a date/time field & a string field for the viewed record.
I can run an append query to update the Usage table but can't figure out how to get the date/time into the table ?
Is it easier to write the whole lot out to a file ?
View 1 Replies
View Related
Jun 7, 2013
I have a Db to track workload document inventory.
I have a data entry form that updates a table - the table tracks workload inventories.
Beg Bal Inventory, plus Received, Minus Processed, equals the End Inventory
Users data enter these document counts every day, click a SAVE button and those counts are saved on the table.
The next day the user wants to open the form for that day's document count for data entry, and wants the End Inventory result from yesterday's record to appear automatically prepopulated in the beginning balance data entry field on the form.
View 10 Replies
View Related
Mar 29, 2006
Hello All,
Is it possible to generate a query that can calculate the 'TotalMins' that an event covers our peak times?
I.e. Our peak times are 0700 to 1000 if we have a start time of 0600 and a finish time of 0900 is it possible for the query to work out that we have covered 120 mins of the peak period?
Thanks for any help provided!:)
View 1 Replies
View Related
Dec 11, 2007
This is very strange. I have converted two computers from 2003 to 2007. I have also installed 5 2007's on to new computers. All of these are Professional XP sp2 setups. I copied over a database to woek on those computers. Originally the performance was very good, on all of the computers (but not the lowest specified converted laptop ) exitting from a form containing a subform takes 37 seconds. The word Calculating... is on the status bar. The laptop takes 3 seconds par with what it used to do.
I have transferred the 'Data' database, the 'Apps' database, Utility.mda and another .MDA. I have also transferred all the Reference executables to one of the other computers. This has not made a jot of improvement.
Has anyone any pointers. I have exhausted all the resources trying to find a solution to this one.
View 2 Replies
View Related
Jul 21, 2013
I have written a large database for the school I used to work for. In the main table there is a field named "Active" which is text with yes or no choices.Is there a way to find in what objects this field is being used in the database?
For the life of me I can't remember why I put this field in or if its even used now.This database has had many versions over the years.
I have searched many of the queries but can not find where this field is used.If there isn't a way I plan to backup, remove field from table and test all the features of the program to see which crashes. However I'm hoping there might be a quicker way.This was one of the first DB I wrote and didn't pay much attention to mapping or keeping notes back then.
View 5 Replies
View Related
May 1, 2013
I have build a database with which I can create quite a lot of reports. When creating them I run out of memory with run time error "2004".
When investigating I detected that a lot of memory is used when creating the reports in PrintPreview mode. When closing the reports the memory is not automatically freed.
Is there a way to enforce garbage collection? Local variables of called routines I set to Nothing.
View 3 Replies
View Related
May 27, 2014
I have a split database made in Access 2007. Each user gets their own copy of the frontend from a script. I wanted to be able to edit the design view of the backend tables even if people were using the database so I made all the forms use snapshot source and only allowed data updates through VBA macro update queries. Having any form open locks the backend source table from being edited. In fact, I've found that just having a normal snapshot query open causes the message "Either an object bound to table 'whatever' is open or another user has the table open. Do you want to open the table as read-only?"
Is there some way to have a table be the source for a form or query, but still have it designable under most circumstances?
Attempted to late-bind a recordset on form load; result was the same:
Code:
Set rs = CurrentDb.OpenRecordset("Select redacted as ft from tblRedacted ", dbOpenSnapshot, dbReadOnly)
Set Me.Recordset = rs
Set rs = Nothing
View 3 Replies
View Related
May 5, 2014
I am trying to create a database that will keep track of the orders placed for a given part number by month. Currently, my table houses the part number, and the ordered amount for the past three years by month (there are thirty-five columns for every part). My column headings are ORDER_MAY_2013, etc. I would like to set a query up that will look at the column headings and pull the amounts ordered for each part for the past twelve months. In other words, I have three years of data in my table. In my query, I just want one year. However, I don't want to have to rewrite the query every month so that it will pick up the new data. Is there a way to accomplish this?
Is there a better way to build this database? I thought about just have four columns in my table - PART_NUMBER, ORDER_MONTH, ORDER_YEAR, ORDER_AMOUNT. The only problem there, is that every part (there are about 450 parts) would have to be listed 35+ times. That seemed too redundant to me, so I built the table this way. However, now I am having trouble querying against it.
View 2 Replies
View Related
Apr 23, 2007
Can someone help me do this in a better way? I have built the two queries below to give me a montly sum of some church contributions. On the "Reportsfrm" form I have two combo boxes to choose the month. One of the combo boxes is setup to choose the months of the year names. This is used merely to put the name of the month on the Monthly Report. The other combo box chooses a number from 1-12 which is used in the first query below to choose the month for the query. This works fine, but makes the DB user use two combo boxes. Does anyone know of a way that they can just choose the month by name? Thanks in advance for your help.
SELECT MemInfotbl.ContribDate, MemInfotbl.ContribAmt, Month([ContribDate]) AS ContribMonth
FROM MemInfotbl
WHERE (((Month([ContribDate]))=[Forms]![Reportsfrm]![Monthcbo]));
SELECT Sum(Monthlyqry.ContribAmt) AS SumOfContribAmt
FROM Monthlyqry;
View 8 Replies
View Related
Oct 27, 2006
So far this forum and everyone here has been a good help!
Now, I want to have a form with one button. (others of course but this one button is special) that when someone clicks it, it prints multiple reports at once.
BUT each report has a query obviously that searches out what records to pull between two dates. Now, ive somewhat tried this before but what ends up happening is when they click on it.. (there is 4 reports printing) it asks the person 4 times the dates its between. Which is a hastle and people would not be happy about.
What i want is when someone clicks this button, it comes up with one prompt. This promt will then print out the 4 reports with the date provided.
On each report im using the "between" statement in the query to pull results.
This is open to you! PLEASE help! Thanks so much,
Jon
View 3 Replies
View Related
May 20, 2005
I am trying to design a report for my police department. It is for racial
profiling.
I have designed a data entry form that lists numerous items using the
circle options, i.e. Moving violation (choose) speed, lane violation, fail
to signal, etc., Result of stop (choices). each of these
options is defined as the Frame number and the title in the frame is the
control source.
I have a report which must add up all the selected options individually,
so that I know how many stops there were for speeding, lane violations,
race options, sex options, etc. This appears to work fine.
I have each of the options assigned a control source as
=DCount("[StopType]","Cop Stops","[StopType]='1'")
with a number assigned to each option withing each group.
( I am really new at this and just guessing at what I need to do)
I want to be able to pull a report by a date range. I set up a query
by using the between criteria. I put a cmdreport button on my input form
and that works. click the button and a form comes up requesting the dates.
I made my query the source for my report. When I put in the date range
and look at the query, the query has the correct records in it. But those
records do not go to the report.
WHAT AM I MISSING? Am I doing something totally out of whack? Have
I made this more difficult than necessary?
Thanks for any help.
View 5 Replies
View Related
Nov 17, 2004
I need some serious help! I am an Access newbie and I think I am trying to do something more complicated than my skills. Any help will be extremely appreciated!
Here is the situation:
I have a table of volunteer records that record each volunteer's availability and areas of interest, which are checkboxes. The volunteer information area of the form basically looks like this:
Monday Tuesday Wednesday etc.
Morning O O O
Afternoon O O O
Evening O O O
O "Trails" O "Greenhouse" etc.
The "O" designates the checkboxes saying "yes" they are available at that time and "yes" they are interested in that area. I did it in checkbox form because it is the most visual and simplest way for my users to understand the record. My users' thought process is going to be this: I am having a greenhouse clean up this Tuesday evening. I need to run a report of all our volunteers that said they are available Tuesday evenings to work AND said that they want to work in the greenhouse. What I would like next to happen is they load the database I'm designing, click the switchboard to a search form that has the same checkbox layout as the volunteer record. They check Tuesday evening, check Greenhouse, then click run report. Report appears on screen that they can view, which they can choose to print so they can call the volunteers.
I have seen samples of listboxes and dropdown boxes as search criteria on a form, but the additional problem is that my end users are over the age of 65, scared of computers, nice ladies. They wouldn't understand the listbox of fields, and it would be a disaster trying to get them to understand AND/OR statements and the entering of yes/no on a list of search criteria, especially if they have more than one time availability and area of interest that they want to run in one report. Hence, sticking to the easy checkbox format for the never-used-a-computer-before ladies to run the reports they need, spitting out the information to the question they are asking, like "Who are all the volunteers that said they are available weekends to work special events? I need to call them to see if they would work the special event coming up in 3 weeks."
I am completely lost about how to go about doing a checkbox form to run a query of checkboxed data that spits out a report with the results :[ I would appreciate a simple sample or an explanation in layman's terms of the steps involved to achieving the results I would like. I am the type who would like to try to figure this out, so that I learn, but I am completely in over my head with this one :[
All patience and help with this will be greatly appreciated!
Thanks in advance,
Newbie Volunteer Coordinator
View 4 Replies
View Related
