Solved

Update query and report printing

Posted on 2004-04-13
6
328 Views
Last Modified: 2006-11-17
I would like to automatically fill a field (DatePrinted) with the current date in each record of a query after I print a report. This is a personal project for billing that I am working on as described in a previous question

www.experts-exchange.com/Databases/MS_Access/Q_20951916.html


I would like to print only the records that are marked completed and haven't been printed previously and then enter the date after the printing is done. Should I have a separate Update query that I run after printing? I am sure there would be several options in code but for now I would like to have an efficient but quick and dirty solution. Maybe with a macro? I also have these databases synched to a Palm with HanDBase which is where the charges will usually be entered so I need a method that will not corrupt that setup as well.

Pointing me in the right direction will be greatly appreciated.

--Karen
0
Comment
Question by:kcartwright
  • 3
  • 2
6 Comments
 
LVL 32

Assisted Solution

by:jadedata
jadedata earned 150 total points
ID: 10815770
Greetings kcartwright!

  This will require that you know which of the records in the database JUST got printed.  If this is based on a query this may be easy, as the query identfies which got printed.
  If there are ANY disruptions to the printing process, this accuracy of this process is then jeopardized.

  The basic code for the date field update is

  UPDATE TableName SET [PrintedOn] = Date() WHERE (< SAME CRITERIA AS REPORT IMMEDIATELY PRECEEDING >);

  If you are specifically requesting a macro, another Expert needs to draft that for you,... I don't use macros, only VBA and SQL
 
 
regards
jack
0
 
LVL 54

Accepted Solution

by:
nico5038 earned 350 total points
ID: 10815891
Hi Karen,

I see you switche to the dateprinted instead of the YesNo field :-)

Personally I never do a direct and automated update of the rows as printers might get jammed and output can be taken by another person. Thus I always display a confirmation window asking "Printing OK" and allow the user to select NO to activate the print again.
Only when responded "Yes" I issue the update query.

This can be done using a macro to execute an update query as Jack proposed.
When there is no adding of new rows after the print of the report the query can be like:
 UPDATE TableName SET [PrintedOn] = Date() WHERE IsNull([PrintedOn]);

And all new rows will be marked.

Need more info ?

Nic;o)
0
 

Author Comment

by:kcartwright
ID: 10816239
I think this will get me moving on just fine - next will probably be Report Design questions!

This is the first time I've used expert-exchange to ask my own questions - WOW, it sure is shortening my "development" time. It almost feels like cheating. :-)


--Karen
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 54

Expert Comment

by:nico5038
ID: 10816260
As long as it's no school assignment cheating this way is OK <LOL>

BTW the programmers motto always is "Better copied from a good sample as badly constructed by myself"

Nic;o)
0
 
LVL 32

Expert Comment

by:jadedata
ID: 10816291
That's ok, I'm using the questions I've answered as my list of test questions.  Is that cheating Nico??
0
 
LVL 54

Expert Comment

by:nico5038
ID: 10816399
For you that is "my pupil" <ROTFLMAO>

Nic;o)
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question