Running tally on form

MS Access 2003 front end, Sql back end, multiple user environment.

Info:
I have a form used for auditing evidence.  It is based on a select query 
whose criteria is the storage location [STORAGE].  A barcode is scanned which 
inputs the unique identifier [ID] for the item.  VBA code then determines if 
the item is still active or needs to be destroyed based on the status of the 
evidence [DISPOSITION].  If it is active ([DISPOSITION]=0), several fields 
are populated with information to show that the item has been audited, who 
audited it, and at what time.  If it is to be destroyed ([DISPOSITION]=15), 
then several fields are populated to show that the item has been destroyed, 
who destroyed it, the time it was destroyed, and then the disposition is 
changed to 1.  

Question:
Is there a way to display the number of records remaining to be audited or 
destroyed in the current storage location?  The criteria for active records 
needing to be audited would be [AUDITTIME]<DATE()-180 and the criteria for 
items to be destroyed would be [DISPOSITION]=15.  The primary key for the 
table is [ID].


-- 
I'm not young enough to know everything.
0
Utf
5/19/2010 1:22:01 PM
access.formscoding 7493 articles. 0 followers. Follow

3 Replies
890 Views

Similar Articles

[PageSpeed] 2

Mackster66 wrote:

>MS Access 2003 front end, Sql back end, multiple user environment.
>
>Info:
>I have a form used for auditing evidence.  It is based on a select query 
>whose criteria is the storage location [STORAGE].  A barcode is scanned which 
>inputs the unique identifier [ID] for the item.  VBA code then determines if 
>the item is still active or needs to be destroyed based on the status of the 
>evidence [DISPOSITION].  If it is active ([DISPOSITION]=0), several fields 
>are populated with information to show that the item has been audited, who 
>audited it, and at what time.  If it is to be destroyed ([DISPOSITION]=15), 
>then several fields are populated to show that the item has been destroyed, 
>who destroyed it, the time it was destroyed, and then the disposition is 
>changed to 1.  
>
>Question:
>Is there a way to display the number of records remaining to be audited or 
>destroyed in the current storage location?  The criteria for active records 
>needing to be audited would be [AUDITTIME]<DATE()-180 and the criteria for 
>items to be destroyed would be [DISPOSITION]=15.  The primary key for the 
>table is [ID].


Try adding two text boxes to the form header or footer
section.  Set one with an expression like:
	=Sum(IIf( [AUDITTIME]<DATE()-180, 1, 0))
and the other:
	=Sum(IIf( [DISPOSITION]=15, 1, 0))

-- 
Marsh
MVP [MS Access]
0
Marshall
5/19/2010 2:24:33 PM

"Marshall Barton" wrote:

> Mackster66 wrote:
> 
> >MS Access 2003 front end, Sql back end, multiple user environment.
> >
> >Info:
> >I have a form used for auditing evidence.  It is based on a select query 
> >whose criteria is the storage location [STORAGE].  A barcode is scanned which 
> >inputs the unique identifier [ID] for the item.  VBA code then determines if 
> >the item is still active or needs to be destroyed based on the status of the 
> >evidence [DISPOSITION].  If it is active ([DISPOSITION]=0), several fields 
> >are populated with information to show that the item has been audited, who 
> >audited it, and at what time.  If it is to be destroyed ([DISPOSITION]=15), 
> >then several fields are populated to show that the item has been destroyed, 
> >who destroyed it, the time it was destroyed, and then the disposition is 
> >changed to 1.  
> >
> >Question:
> >Is there a way to display the number of records remaining to be audited or 
> >destroyed in the current storage location?  The criteria for active records 
> >needing to be audited would be [AUDITTIME]<DATE()-180 and the criteria for 
> >items to be destroyed would be [DISPOSITION]=15.  The primary key for the 
> >table is [ID].
> 
> 
> Try adding two text boxes to the form header or footer
> section.  Set one with an expression like:
> 	=Sum(IIf( [AUDITTIME]<DATE()-180, 1, 0))
> and the other:
> 	=Sum(IIf( [DISPOSITION]=15, 1, 0))
> 
> -- 
> Marsh
> MVP [MS Access]
> .

That works great except I left out an important part of the criteria for 
active records.  I need [AUDITTIME]<DATE()-180 AND [DISPOSITION]=0.  The 
following expression is exactly what I needed:

=Sum(IIf([AUDITTIME]<Date()-180 And [DISPOSITIO]=0,1,0))

Thank you very much for your help!


0
Utf
5/19/2010 3:37:01 PM
Mackster66 wrote:
>"Marshall Barton" wrote:
>> Mackster66 wrote:
>> >MS Access 2003 front end, Sql back end, multiple user environment.
>> >
>> >Info:
>> >I have a form used for auditing evidence.  It is based on a select query 
>> >whose criteria is the storage location [STORAGE].  A barcode is scanned which 
>> >inputs the unique identifier [ID] for the item.  VBA code then determines if 
>> >the item is still active or needs to be destroyed based on the status of the 
>> >evidence [DISPOSITION].  If it is active ([DISPOSITION]=0), several fields 
>> >are populated with information to show that the item has been audited, who 
>> >audited it, and at what time.  If it is to be destroyed ([DISPOSITION]=15), 
>> >then several fields are populated to show that the item has been destroyed, 
>> >who destroyed it, the time it was destroyed, and then the disposition is 
>> >changed to 1.  
>> >
>> >Question:
>> >Is there a way to display the number of records remaining to be audited or 
>> >destroyed in the current storage location?  The criteria for active records 
>> >needing to be audited would be [AUDITTIME]<DATE()-180 and the criteria for 
>> >items to be destroyed would be [DISPOSITION]=15.  The primary key for the 
>> >table is [ID].
>> 
>> 
>> Try adding two text boxes to the form header or footer
>> section.  Set one with an expression like:
>> 	=Sum(IIf( [AUDITTIME]<DATE()-180, 1, 0))
>> and the other:
>> 	=Sum(IIf( [DISPOSITION]=15, 1, 0))
>> .
>
>That works great except I left out an important part of the criteria for 
>active records.  I need [AUDITTIME]<DATE()-180 AND [DISPOSITION]=0.  The 
>following expression is exactly what I needed:
>
>=Sum(IIf([AUDITTIME]<Date()-180 And [DISPOSITIO]=0,1,0))
>

Great.  Now that you have the general idea down, there are
many other ways to get the same result and may be a little
faster.  For example, in increasing order of efficiency and
decreasing order of obscurity  ;-)

	=Count(IIf([DISPOSITION]=15,1,Null))
or
	=Abs(Sum([DISPOSITION]=15))
or
	=-Sum([DISPOSITION]=15)

I don't thing the speed differences are significant so pick
one that resonates with your way of looking at the world and
go with it.

-- 
Marsh
MVP [MS Access]
0
Marshall
5/19/2010 5:55:53 PM
Reply:

Similar Artilces:

Need only one DLL instance to run...
Hi all, 1: if two apps load the same DLL - LoadLibrary(...) - system will create to different instance of the DLL... Now, in my DLL i've a CList and i need it to be visible to all instance and all apps!!!! Is there a way!? 2: I need the dll to remain loaded till machine reboot!!! Is it possible!? Thanks Ale >if two apps load the same DLL - LoadLibrary(...) - system will create to >different instance of the DLL... >Now, in my DLL i've a CList and i need it to be visible to all instance and >all apps!!!! > >Is there a way!? It'll be difficult to share a ...

credentials to run this report are not stored
Hi all, I'm getting this problem when I try and create a timed subscription. Error: ...credentials to run this report are not stored..... ok I created another folder under the same folder where I'm having this problem. loaded the report and I have no problem. I even moved the current folder with that report in it. Creted another folder with the same name and still the same problem with that named folder. With a different name for the folder I have no problem??? I'm stuck here.. almost all post just talk about storing the credentials, Already done and works...

Repost: Error running Report in an Access 2003 db from Access 2007
Ok, clarification - ignore the code from my original post, some of the reports do work. The ones that don't are reports that I have being filtered. Here is the code from one of those buttons: Private Sub Ok_Click() On Error GoTo Ok_Click_Err 'using the customer sub form for customer state report to filter the report, clicking ok will open report for selected state Dim stDocName As String Dim stLinkCriteria As String If Not IsNull(Me.Search_Results) Then stLinkCriteria = "[StateOrProvince] = """ & Me![Search Results] & """"...

run time error 10-22-03
I am having a lot of trouble when I open up word I get run time error 52 in VB. I have tried uninstalling word and reinstalling it. WE have tried deleting the macro but still to no avail can someone help me please? ...

Automatically run macro
My name is Mike and i have a question about microsoft excel macro's. Attached is a copy of the excel sheet im working on. Below the excel sheet is the macro I built. Some of the cells contain given values and some cells are calculated from formulas. Cell (G4) is my given value...it is related to cell (C32). The point is, I plug a value into cell (C10) and it runs through the rest of the calcs in the other cells and gives me a value to cell (C32). I built a macro that works as a goal seek pretty much. The macro makes cell (C32) equal to cell (G4) and gives me the value for cell (C10). I wan...

customize contact form?
Have created custom contact form and want to make it my default and also let others use it. Don't know how to use VB. Is there any other way to implement it? http://www.outlookcode.com/d/newdefaultform.htm should provide some pointers. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without reading. After searching google.groups.com and finding no answer, Dave asked: | Have created custom contact form and want to make it my default...

Disable Tab and/or Section in form
Hi i have another question. May i disable by javascript a tab a/o section in a form? If it is possible can you give me a sample? Thank's Marco Rocca Marco, Here are instructions for hiding a tab: http://blogs.infinite-x.net/?p=3 Thanks, Mitch ...

SSL port removed form IIS autometiclly
HI I have one problem with my OWA I have enable the SSL on my OWA (running on Frount-end) and its working fine. Problem is this after every day SLL port seeting form IIS is removed autometically and OWA is not accessible by users aand i have to manually give SSL port in OWA virtual server the next day. In Exchange system manager HTTP virtual server SSL port setting is disabled and i can not configure SLL port from Exchange System Manager. I am not using the default http virtual server in owa. Can nay one help Try exporting the certificate. Then remove the certificate and them import...

display changing label caption on form as sub runs w/o screen flic
let's say i have this routine Sub Test label1.caption = "Starting ... " 'do events label1.caption = "Getting there ... " 'do events label1.caption = "Finished! ... " End Sub on my form, i have label1 right in the middle what happens is sometimes the message will change, and then sometimes it wont, or it will show the first one, skip the second and jump to the third etc etc etc so it is inconsistent. is there a way to make sure the label caption displays consistently, on time, wh...

Excel Continuous Running Total
I posted a message earlier and have received a partial solution. I want to keep track of how much stock prices go up or down with a running total of how much they go up or down over several days until the direction changes. For example, if price go up 10 on Mon, 20 on Tues, 30 on Thurs and down 10 on Fri I want my running total column to show a positive number of 60 and then a red number of 10 and continue adding the amount of the total of the down days until the market shows an up day. The formula I am now using total the first and second day but does not do a running total count if t...

Problem with xml result from the Fill Out Form
Hi to all, The Fill Out of my Form, create an xml into Shere Point. I adjusted my Template.xml to open the xml in Explorer with view1.xsl. When open the xml file, the results of a form is not a complete. I have, in the Form, a Drop-Down-List with key ({00000000-0000-0000-0000-000000000000}) and value (Offer...) populate from a Web Service. OnChage of this Drop-Down-List, get populate a Repeating Table populate from a Web Service that get in input a key from Drop-Down-List and in output a value from the offer (Product Name, Quantity and Price). In the xml results of Fill Out, not show the val...

Showing the results of a parameter form in a form!
Hi I have used Microsoft's help to create a form which runs a parameter query. After setting the parameter values in the form I click OK and the results are displayed in a list. How would I get the results to be shown in a pre-designed form? N.b. Using Microsoft help, I coded the On Click Event on the parameter form thus: Private Sub OK_Click() Me.Visible = False DoCmd.OpenQuery "Company Details by Postcode or Position QRY", acViewNormal, acEdit DoCmd.Close acForm, "Company Details by Postcode or Position FRM" End Sub Thanks On Mon, 10 Mar 2008 11:19:02 ...

Update subform field from pop-up form
I have a Form with 2 subforms. I need the ability to change the Child Field on the second subform to a value found in a pop-up form. I have the following structure: FrmMain Primary Key: ClientNo FrmSubVisit Record Source: q_frm_sub_visits_active Field 1: Account Field 2: AccDate Link Child Fields: ClientNo Link Master Fields: ClientNo Default View: Continuous FrmSubEvent Record Source: q_frm_sub_events Field 1: Account Field 2: EventNo Link Child Fields: Account Link Master Fields: txtRelayAccount (text relay on FrmMain from frmSubVisit) Default View: Single Form The “Account” on frm...

Money 2002 will not run
I had been using Money 2002 for approx. 3 yrs on my home pc (Dell Dim 2100, XP Home Ed. w/SP2). Last year, it simply would not launch. No error message, no splash screen, no app opening, no process listed in Task Manager. Just.... nothing.... The only change that was made to the system since M2k2 last ran was upgrading my a/v solution from Trend Micro PC-Cillin to TM Internet Security. I have tried disabling every aspect of the Internet Security product, as well as completely un-installing the app, and then attempting to run Money, but the same thing (nothing) happens. I was considerin...

Problems with MAPI when publishing outlook forms.
Greetings. I hope to get some help on this issue. Im currently running Exchange 2000 on my server. On my workstation I installed Outlook 2003 and I can create new forms and publish them to the organizational forms library. However - after a while, suddenly I cant publish an edited form. The error I get is "Unable to succesfully publish the form due to a MAPI error. The folder has not been enabled for use while offline" However - im not offline. Im working on the server and I can publish the form in another name. If I save the form to a .oft file and open it in a outlook 2000 - ...

Ways to Start a particular form?
1) I want to automatically open a particular form when the Access file (Access 2007) is opened. I would prefer doing this with a Macro. 2) I would also like to start a particular form what I click on a menu page to open it? Suggestions PLEASE? Question 1 - Create a macro named Autoexec with action OpenForm and the form as argurment. It will run every time Access is opened except when the SHIFT key is held down. Querstion 2 - What kind of 'menu page' do you have? -- Build a little, test a little. "BobC" wrote: > 1) I want to automatical...

Re: Workflow just wont run automatically, i have to run them manually
Yes, but i realized what i was doing wrong. I assumed [bad idea] that if i create a case and hit Save & Close the first time, taht the rule will run. In order for the rule to run automatically, it has to be Save, once it saves it, then Save & Close. Thanks for your reply. "Hi, Did you check the workflow monitor to see if the rules get triggered correctly and complete sucessfully ? Have a nice day, St=E9phane Dorrekens " --------------= Posted using GrabIt =---------------- ------= Binary Usenet downloading made easy =--------- -= Get GrabIt for free from http://...

Money 2005 & Transaction Form Budget Summary
I have recently upgraded to Money 2005 from 2003 and have noticed that I am missing the little budget/category summary that used to appear at the bottom of the transaction form when I review & categorise downloaded transaction entries in the Account Register. The summary used to show how much of my budgeted amount for that category had been used. Does anyone know how to get the little summary back? Was this functionality removed from Money 2005? I hope not, because I really liked it. Thanks, Tyler Nobody here. Yes. See http://umpmfaq.info/faqdb.php?q=166. "Tyler" <Ty...

Using forms in a shared powerpoint
Hi, I have a feedback form in a Powerpoint which stores the inputted data in a text file. It works perfectly and does what I want it to. EXCEPT I want to use the same form with a group of users and whenever I try this an error occurs. When they press Submit when someone else has it open a run-time error occurs. Does anyone know if I can get around this or should just give up! This is the kind of thing I am doing: http://cws.internet.com/article/4529-.htm Thanks Maybe try using FreeFile instead od #1? Not sure that even then you can have it open on more that one PC at a ...

Outlook Still Running
I am running XP Pro with Office 2003 Pro and sometimes when I exit out of Outlook, Outlook.exe and Winword.exe stay as processes still running. Does anyone else have this problem and what can do to make sure this does not happen? Thanks for your help -- Neil Remove ABCD from Email address to reply <neil154ABCD@earthlink.net> wrote: > I am running XP Pro with Office 2003 Pro and sometimes when I exit > out of Outlook, Outlook.exe and Winword.exe stay as processes still > running. Does anyone else have this problem and what can do to make > sure this does not happ...

Very strange problems while running Great Plains on workstations
I notice that a few workstations in an office I support are having problems when they run Great Plains. Excel, Outlook, Word and Dynamics.exe are showing in the Application event log as being Hanging or Faulting. I also see Fault Bucket errors, but when I search online I cannot find any information online. Here is one of the Fault Bucket errors: 3:15:35 pm 28-Sep-06 Application Hang None 1001 N/A Fault bucket 296734104. Also, these workstations are experiencing problems printing PDF files. Has anyone out there seen this behavior and if so, how can these problems be fixed? Thank you, ...

Publisher 2000 will not run
I had problems with Publisher 98 not running which we=20 never did solve so I installed office 2000 to see if=20 Publisher 2000 would function. Same problem: The flash=20 screen pops up and then disappears. No program. Microsoft=AE Publisher 2000 Version 6.0 has encountered a=20 problem and needs to close. We are sorry for the=20 inconvenience. Howard, hi again, Have you tried opening Publisher in safe mode? Publisher retains all printer information within its publications. If you can open Publisher in safe mode, either regress or update your printer and video drivers. -- Mary Sauer MS MV...

Macro automatic run at open
Is there a simple way to have a macro automatically started when a workbook is open? Thanks Francesco Francesco, use the workbook open event, like this, Private Sub Workbook_Open() MsgBox "It Works!" End Sub To put in this macro, from your workbook right-click the workbook's icon and pick View Code. This icon is at the top-left of the spreadsheet this will open the VBA editor to the thisworkbook module, then, paste the code in the window that opens on the right hand side, press Alt and Q to close this window and go back to your workbook, now this will run every time you ...

Running diferent query with one command
I would like to run queries with just one botton and a date dispalyed in a form. If Sunday March 09, 2008, is dispalyed I would like to click on a button and run a query that will select emloyees that are scheduled to work on this day of the week along with other pertinent information already preselected by that query. Currenlty I am using 7 diferrent buttons to run 7 differnrent append queries but it is too confusing and I am sure there is an better way. Thanks Charles -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200803/1 Why 7 butt...

Win 7 Pro running 64 bit with Outlook 2007. Problem: frequent cras
I have a brand-new computer with i-7 processors and 8 GB of RAM running Win 7 Pro 64-bit. At the time of upgrading to new computer, i upgraded office 2003 to 2007. For the first month, all was well. Suddenly, my computer started crashing regularly, as in every 30 minutes. The screen would go blank, the mouse and keyboard wouldn't work, and I would have to do a hard reboot. The store where i bought the PC has done everything imaginable to try to isolate the problem. They have swapped out every component, including the RAM, motherboard, power supply, video card and all ca...