Assign batch # to rcds so report can be recreated.

Hi,

I'm running Access via Office XP Pro on Windows 7 Pro.

I've cross posted this to the Report group (I hope) and I'm trying to 
develop a couple of SQL statements to help me recreate previously printed 
forms / reports.



Goal / Background   ------------------------

I want to print the actual invoice for all transactions that have NOT been 
previously been printed.  The invoice numbers were assigned when the 
transactions were created. I just want to “group” all the transactions into a 
single batch so that I can re-produce that given batch at any given point in 
time.  

Our customers must be members of the organization.  Therefore, each year 
they must pay a membership fee to renew for that year.  We will print a 
membership card for them once we receive payment.  We only want to print that 
membership card once (even though we can re-print it if they loose it).  We 
also want to know when we printed that card.  These cards are printed once a 
week in a batch, to which we will assign a batch number.

I already have my reports up and working.  Currently, I have to manually 
select the transaction I want to print by manually modifying my query.  So 
not a good solution, but I was to develop, test, and debug my report.

To do this, I want to:
1.  Generate a batch number to be used.  I already have this code working.  
It goes out to the tblBatchRpt and uses DMax to get the current highest batch 
number in the table.  It adds 1 to it and writes a new batch ctl record out 
with date, time, report name, and some other stuff.

2.  My report will then select all unprinted transactions and update the 
batch number field on those transaction records with the new batch number. 

3.  I also want to create an central audit trail file that will track each 
record that was assigned to a batch, regardless of which report it was on, or 
in which table the data record resides.

4.  My report will then set it Filter property (I’m not sure I used the 
correct terminology  -   My.Filter = “xxx” or is it My.ReportFilter = “xxx”) 
to select just the record with the current batch number.  It will then print 
the data.

5.  My “Reprint” option will have a form with a combo box what will list all 
of the dates and time the report was run and as the user which report they 
want.  This combo box will work off the tblBatchRpt table and list just those 
batches that were created by the current report.  When the user selects the 
batch they want to re-print, the report will set it current filter to that 
batch and re-print that batch.


Database --------------------------------------------------

tblBatchCtl:   1 rcd per batch

Key:   BatchNo   -  Autoassign number by Access
Print Date
Print Time
Report Name 
Comment

tblBatchAudit       I will use this file to have a centralize place so that 
I can see all of the records that have been assigned to a batch.  Is it 
possible (with future planned upgrades) that one record would be printed on 
different report in different batches at different points in time.  This 
table will allow me to see all of the batches in which a single record has 
been included / printed.

I will also create a query table / logical table that will only show the 
records for a given table (tableNo).   This way I can join the tblRenew to 
the qtBatchAudit_Renew and see which renewal record had been printed and when 
they were printed.  I will be able to do the same thing for the tblInvoice 
and tblCustomer tables.

Key:    AuditId    - Autoassigned number by Accss
BatchNo
TableNo              - 1 = tblRenew, 2 = tblInvoice, etc
RcdID                 - This will be the key to the record that was assigned 
the batch number.



TblCustomer:   1 rcd per customer
CustId
Name
Etc.

TblRenew:	Each customer is a member and they must renew each year.
1 rcd per customer per year.
RenewalId    Auto assigned number by Access
CustId
RenewalYear


tblInvoice     1 rcd per invoice  multiple rcds per customer.

Key:  Invoice Number – auto assigned number by Access
CustId
InvoiceDt



tblInvoiceDet  - 1 rcd per transaction – Multiple rcd per invoice.
Key:  TransId  - autoassigned  assigned number by Access
Details field for each line on the invoice.



Question ---------------------------------------------------

A:  What would be the SQL statement to:

1.  Select all unprinted records and assign a batch number to them.
     The select criteria would be WITH IsNull(BatchNo) or BatchNo = “”


2.  Select all of the records that have been assigned the batch number by 
the above step and write out a record to the tblBatchAudit table?


B.  How do I execute those SQL statements within a report’s VBA code?


Are there any suggestions for how to make this a simpler or cleaner process?


Thank you so much for you assistance.

Dennis

-- 
Dennis
0
Utf
5/28/2010 5:45:02 PM
access.queries 6343 articles. 1 followers. Follow

4 Replies
644 Views

Similar Articles

[PageSpeed] 46

Dennis, that's a fairly involved question. The simplest answer I can give is 
to point you to a sample database that does something like what you ask, and 
let you figure it out from there.

See:
    Has the record been printed?
at:
    http://allenbrowne.com/ser-72.html

It assigns a batch number to those that have not been printed, and then 
prints the batch (or allows you to print that batch again later.)

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Dennis" <Dennis@discussions.microsoft.com> wrote in message 
news:7391B22D-B556-4AA3-BD22-B58EDFFD8086@microsoft.com...
> Hi,
>
> I'm running Access via Office XP Pro on Windows 7 Pro.
>
> I've cross posted this to the Report group (I hope) and I'm trying to
> develop a couple of SQL statements to help me recreate previously printed
> forms / reports.
>
>
>
> Goal / Background   ------------------------
>
> I want to print the actual invoice for all transactions that have NOT been
> previously been printed.  The invoice numbers were assigned when the
> transactions were created. I just want to “group” all the transactions 
> into a
> single batch so that I can re-produce that given batch at any given point 
> in
> time.
>
> Our customers must be members of the organization.  Therefore, each year
> they must pay a membership fee to renew for that year.  We will print a
> membership card for them once we receive payment.  We only want to print 
> that
> membership card once (even though we can re-print it if they loose it). 
> We
> also want to know when we printed that card.  These cards are printed once 
> a
> week in a batch, to which we will assign a batch number.
>
> I already have my reports up and working.  Currently, I have to manually
> select the transaction I want to print by manually modifying my query.  So
> not a good solution, but I was to develop, test, and debug my report.
>
> To do this, I want to:
> 1.  Generate a batch number to be used.  I already have this code working.
> It goes out to the tblBatchRpt and uses DMax to get the current highest 
> batch
> number in the table.  It adds 1 to it and writes a new batch ctl record 
> out
> with date, time, report name, and some other stuff.
>
> 2.  My report will then select all unprinted transactions and update the
> batch number field on those transaction records with the new batch number.
>
> 3.  I also want to create an central audit trail file that will track each
> record that was assigned to a batch, regardless of which report it was on, 
> or
> in which table the data record resides.
>
> 4.  My report will then set it Filter property (I’m not sure I used the
> correct terminology  -   My.Filter = “xxx” or is it My.ReportFilter = 
>  “xxx”)
> to select just the record with the current batch number.  It will then 
> print
> the data.
>
> 5.  My “Reprint” option will have a form with a combo box what will list 
> all
> of the dates and time the report was run and as the user which report they
> want.  This combo box will work off the tblBatchRpt table and list just 
> those
> batches that were created by the current report.  When the user selects 
> the
> batch they want to re-print, the report will set it current filter to that
> batch and re-print that batch.
>
>
> Database --------------------------------------------------
>
> tblBatchCtl:   1 rcd per batch
>
> Key:   BatchNo   -  Autoassign number by Access
> Print Date
> Print Time
> Report Name
> Comment
>
> tblBatchAudit       I will use this file to have a centralize place so 
> that
> I can see all of the records that have been assigned to a batch.  Is it
> possible (with future planned upgrades) that one record would be printed 
> on
> different report in different batches at different points in time.  This
> table will allow me to see all of the batches in which a single record has
> been included / printed.
>
> I will also create a query table / logical table that will only show the
> records for a given table (tableNo).   This way I can join the tblRenew to
> the qtBatchAudit_Renew and see which renewal record had been printed and 
> when
> they were printed.  I will be able to do the same thing for the tblInvoice
> and tblCustomer tables.
>
> Key:    AuditId    - Autoassigned number by Accss
> BatchNo
> TableNo              - 1 = tblRenew, 2 = tblInvoice, etc
> RcdID                 - This will be the key to the record that was 
> assigned
> the batch number.
>
>
>
> TblCustomer:   1 rcd per customer
> CustId
> Name
> Etc.
>
> TblRenew: Each customer is a member and they must renew each year.
> 1 rcd per customer per year.
> RenewalId    Auto assigned number by Access
> CustId
> RenewalYear
>
>
> tblInvoice     1 rcd per invoice  multiple rcds per customer.
>
> Key:  Invoice Number – auto assigned number by Access
> CustId
> InvoiceDt
>
>
>
> tblInvoiceDet  - 1 rcd per transaction – Multiple rcd per invoice.
> Key:  TransId  - autoassigned  assigned number by Access
> Details field for each line on the invoice.
>
>
>
> Question ---------------------------------------------------
>
> A:  What would be the SQL statement to:
>
> 1.  Select all unprinted records and assign a batch number to them.
>     The select criteria would be WITH IsNull(BatchNo) or BatchNo = “”
>
>
> 2.  Select all of the records that have been assigned the batch number by
> the above step and write out a record to the tblBatchAudit table?
>
>
> B.  How do I execute those SQL statements within a report’s VBA code?
>
>
> Are there any suggestions for how to make this a simpler or cleaner 
> process?
>
>
> Thank you so much for you assistance.
>
> Dennis
>
> -- 
> Dennis 

0
Allen
5/29/2010 1:40:36 AM
Allen,

Once again, thanks.  I looked at your code and it does pretty everything I 
need to do and how to do it.

I saw on your web site that you suggested comp.database.ms-access usenet.  
Is that were you will be "hanging out"?


Thanks

Dennis
0
Utf
5/30/2010 3:56:04 AM
"Dennis" <Dennis@discussions.microsoft.com> wrote in message 
news:EA998FE0-2368-4393-95E2-D965AFDB6CAC@microsoft.com...
> Once again, thanks.  I looked at your code and it does pretty everything I
> need to do and how to do it.
>
> I saw on your web site that you suggested comp.database.ms-access usenet.
> Is that were you will be "hanging out"?

That's one of the groups I've been involved in since 1994. C U there.

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org. 

0
Allen
5/30/2010 8:35:41 AM
Allen,

Thanks one again.  I'll c u in comp.database.ms-access usenet.

Thanks for all of your help.

Dennis
0
Utf
5/30/2010 4:15:01 PM
Reply:

Similar Artilces:

Fixed Aseet report
We have a fixed asset report printing differently from all other reports. seems like it's been stretch out. any idea why this happened? Thanks for your help. No, but that's the way the report prints for everybody. -- Charles Allen, MVP "hlv" wrote: > We have a fixed asset report printing differently from all other > reports. seems like it's been stretch out. any idea why this happened? > > Thanks for your help. > > ...

Can I add a domain to the Blocked Senders list?
I am just getting used to Outlook 2003 and trying to make use of the SP{AM filter. I see that when I right click on a message that one of the options id to add a sender to the "Blocked Senders List" Is there a way that I can also include the option to block a domain? Thanks Dunc Dunc wrote: > Is there a way that I can also include the option to block a domain? There is an easy way, if you're running Outlook 2003+SP1. Dunc wrote: > Is there a way that I can also include the option to block a domain? Right-click on any e-mail, choose (menu) Junk Mail, Junk Mail...

Can I embed a clickable link that includes command line arguments?
I 'm familiar with the process of embedding a clickable link to a file located on a share accessible to the recipient, and do so regularly by using a UNC formatted path: \\ServerName\Path\to the \location\of the\file (Interesting ... the "<" ">" I wrapped the above line in dissappeared as soon as I entered the close bracket. In Outlook [plain text] I'm not used to that happening.) My question: is it possible to do the same thing to launch a .exe (or ..cmd) with command line arguments? Something like: \\UNC Path\Executable /argument1 /a...

How can I asign a number value to a text line in Excel?
tI have a Backgammon Club with Internal Club Rankings that are in Text. I want my members to report their wins - 1st, 2nd, and 3rd place - in tournaments to my web site . I would like them to imput: their nickname, date of tournament, tournament Room #, tournament points played for, and host of the tournament. I would further like the calculation to display any change in Ranking as a text cell. Additionally, I want to show for the member, how many more points are needed to get to the next levle and what that (Text)level is. If there is a "what if" for this please advise. Ri...

Report will not print
RMS2.0 SP2 When I print any report, 90% of the time, it will only print the report header and NO other information. When I print to a PDF file it will ALWAYS print the entire report properly. I spoke to MS tech support and they said to change the store logo to the BMP which I did, still no good. Any suggestions? I am using a USB Canon printer, the reports have been printing correctly in the past. I already uninstalled and reinstalled the printer drivers. I am completely lost. Haik, Can you print any other reports? Maybe something is wrong with the report formatting, so the field does...

users can't login on one PC
I have 1 PC that was just upgraded to GP 10 SP 2 and now only the SA can login. When a regular user account tries to login they get the error: "The login failed. Attempt to login again or contact your system administrator" That same id is able to login on every other PC so I know it is not SQL DB security and the SA can login from this PC so i am pretty sure it is not the ODBC. Any help would be appreciated. Fliehigh OK So I figured it out this DSN was setup using the IP address of the SQl server and the rest where using the DNS name. I changed it and it worked great. Fli...

Control can't be edited; it's bound to a replication system column 'TableName'.
I have an Access 2007 accdb database. It hast a table in it with field names TableName and FieldName and a bunch of others. This table has had a small number of records added to it (37) over the life of the database. Suddenly, the two fields TableName and FieldName cannot be edited. That is even if the table is open as a table and you try to type a new record. When you do that an error message is briefly displayed in the left hand end of the Access main window's status bar. For example if you try to input anything into the TableName field you will be blocked and you will briefl...

Can't download bank statement to Money
I've not had this problem until today. No bank data is getting downloaded to MONEY and I don't know if it's the program issue or if there is any virus going on to affect this. Thanks for your help. Without more information from you, I can only tell you that I've not had any problems downloading bank data. Some useful information would be the bank you are using, the version of Money, and the error you are receiving. If you don't get an error, you may not have any transactions to download at this time. "Hannah" <anonymous@discussions.microsoft.com> wr...

Report in HQ
I would like a report that compares differnt stores cost and price. I can get it to run a report that shows everyitem in both stores but i would like one that just displays the diffenrt ones so i dont have to look at all 8,ooo items. Not sure if i need to export it out to excel but even if that is the case not sure how i would make it only display the ones that have differnt prices and cost per store. Been there. Done that. Have the t-shirt. I can send if you will leave your e-mail address. "Doug Pic-N-Pac" wrote: > I would like a report that compares differnt stores co...

getting sql report 2005 web service to work
The error I am getting in sql reporting service 2005 is 'The selected report is not ready for viewing. The report is still being rendered or a report snapshot is not available. (rsReportNotReady)'. There are lots of answers on the web and I have tried alot of them including obtaining extract space. The error I am getting is: " The report is still being rendered or a report snapshot is not available. (rsReportNotReady)". I am using using windows-authentication to the datasource, and I am wondering if I am not setting up the pass-through properly on t...

Sort and report category items
I'm an Excel rookie using Excel 2008. And this is probably a very beginner question. Example of problem: Sheet has three(3) columns (A, B, C) with the first two being text cells. A: Organization type B: Organization name C: Organization $ The rows contain the information for each year and then within each year a row for each organization reporting that year. I'd like to create a report with: A List of all organizations by each type and not repeat the names. This would look like: Type 1: Company X Company Z Type 2: Company Y Type 3: Company M Compa...

Can't delete a personal folder
Hello. My problem is that I somehow ended up with an extra personal folder and I simply can't delete it. I've tried moving the .PST file and creating a different one. No luck. I've even uninstalled outlook 2000 three times to no avail. After I uninstalled it I looked in "My Documents and Settings" where Outlook and other applications leave data behind and I deleted everything that I found but that didn't work either. I reinstalled Outlook 2000 and there was my Personal Folder again! When I click on the properties and I try to close it it says "Ob...

cannot modify reports in Report Writer
Whenever I tried to put some text in a report, these text turned to be "String Missing" two words in the report. How to solve this problem? Please help. Thanks. stien Sounds like the reports.dic is corrupted. Use Tools >> Customise >> Customisation Status to export all the working reports, then rename the reports.dic and import the packages back. This will lose any format changes, so you might need to re-make changes to formats. David Musgrave [MSFT] Senior Development Consultant Escalation Engineer - Great Plains Microsoft Dynamics Support - Asia Pacific Micor...

Can't open some mail after upgrade
Hello.. We upgraded our server to Exchange 2003. After the upgrade our clients using Windows 2000 and Outlook 2000 do not seem to be able to open mail that has been forwarded more than once and contains attachments. I can forward the same piece of mail to someone using an Outlook 2003 client and they can open it, but the client using Outlook 2000 cannot view the mail, preview the mail, forward the mail or even see or save the attacment. The error is: "Can't open this item. Could not complet the operation because the service provider does not support it." Any help...

can't edit or delete rules in Outlook 2002
Hi All, In the Rules Wizard, some rules are shown, but I cannot Edit Modify or Delete them or see the conditions for them. Nor if I click the RUN NOW button do the rules show in the rules list box. Other rules are OK. Why does this happen? Can this be fixed? Does it make a difference that this pst file was viewed in Outlook 2003 (but not converted to Outlook 2003)? Many thanks in advance ...

Can not create mailbox on new volume
Hi, I installed new volume and attached through SCSI to Exchange 2003 server. I created new database on new volume and started to move mailboxes in order to free space. When I try to create new mailbox in new volume it,s fault. i can create account , but not mailbox.i can create on old volume and after to move. I do not know if this is active Directory problems or new HD. Please, help >>When I try to create new mailbox in new volume it,s fault. What does the error say? Is the new store mounted? Vlad wrote: > Hi, > I installed new volume and attached through SCSI to Exchang...

Problems generating reports from a modal dialog
Hi, I have two related problems, when generating reports from a modal dialog box. The dialog box accepts report criteria and then either previews or prints the report depending on a choice made from the main menu. The main menu is used to enter the criteria applicable to all reports that can be generated and I thought I'd use a dialog box to accept the unique criteria applicable to this one report. Still with me? :-) Prob 1. When previewing the report. The report is generated and the dialog box closes ok but I want the focus to switch to the new tab containing the rep...

How can I create and display a chart dynamically on a UserForm wi.
I am trying to create charts dynamically using VBA and Excel. The data being plotted will change frequently, and my client wants to interface only with the user form, and not the worksheet itself. I know that you can use the MSChart control to do this in VB6, but I was wondering if there is a similar control for use with Excel and VBA. If so, any additional info on how to use it would be much appreciated. Thanks. I think it's an incredibly dumb idea to want to interface only with a userform (and sacrifice the flexibility of direct access to a chart). Maybe, you can convince your c...

I can not remove the read only from a file on a cd why
Can someone help me. When I store a file on a cd and then retrieve it, I can not edit it, it comes back as read only. When I go to the file and try to change the attribue within the properties and remove the read only access it is denied. If I save on the hard drive I can make what ever changes I want. Your help will be greatly appreciated. Jeff Smith (212) 929-8024 Jeff, as far as I'm aware, it is still not possible to save a file directly to a CD, as it needs to be "burnt" via burning software . This includes any changes to the file if you have opened it directly from...

CAN'T STOP GLOBAL FORMATTING
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Any number I enter is formatted to 1% of its value and rounded off. E.g. I enter 1485, Excel returns 1.49 Problem not in preferences; and Formatting palette won't cooperate. No macros running. Have re-installed twice. Answered elsewhere � please don't post the same question in multiple groups. Regards |:>) Bob Jones [MVP] Office:Mac On 3/8/09 12:38 PM, in article 59b6ebfd.-1@webcrossing.caR9absDaxw, "JungianPsy@officeformac.com" <JungianPsy@officeformac.com> wrote: > Version: 2008 >...

User Can't Send Mail
When a user has hit their quota limit and I increase it for them what do I need to do to allow the updated limit to be applied to the user so they can send Email again? wait...the default update time for this is 2 hours...or see this: http://support.microsoft.com/kb/327378/en-us -- Susan Conkey [MVP] "D303M" <D303M@discussions.microsoft.com> wrote in message news:563E524F-709F-40CE-A202-228944A920F2@microsoft.com... > When a user has hit their quota limit and I increase it for them what do I > need to do to allow the updated limit to be applied to the user so th...

Can't send email to our domain...
Hi -- We have a client who says they can't send email to us. Can someone decode this message for me? I asked him for the bounced message: The following recipient(s) could not be reached: PBen@mydomain.com on 8/10/2005 8:02 AM There was a SMTP communication problem with the recipient's email server. Please contact your system administrator. <IHA4.IHA-CENTRAL.local #5.5.0 smtp;553 Requested action not taken: mailbox name not allowed or chunk too large - on relay of: MAIL FROM:<Mike_O'Donnell@theirdomain.com>> sounds like h...

How can i insert multiple pictures in a word doc, simultaneously?
Hello! I need to put in multiple photos from a file in my reports, and i would like them to be sized to fit 6-10 per page. Is there any way to do that in one simple step? Thanks! It depends on your definition of simple. In order to control the layout of the images you need to insert them in a table of fixed cell width. The images will then adapt to the width of the cell. How many will fit of a page is determined by whether the images are portrait mode or landscape mode - or mixed modes. The following macro will insert a two column table at the cursor and proceed to en...

Reporting Synch jobs stuck in Waiting To be processed (Sleeping)
Hi, It seems that all my Reporting sync jobs are stuck in "Waiting to be processed (Sleeping) state. All other jobs are processed OK. I restarted the queue service, eventing service, SharePoint Timer, I even rebooted my 4 servers (2 WFE, 2 APP) more than once and it does not help. I tried a Full refresh of the Reporting DB (twice) and the queue fills up with over 800 Reporting sync jobs that stay there until the Queue Cleanup. There is nothing in the ULS logs, nothing in the EventViewer I have SP2 and the October 2009 CU. Any idea what else I could try ? Thank...

Cash Flow Forcast Report
I would like to print out my monthly cash flow in a report. I have noticed others referencing this on this group, but I'm not sure where to find this report in MSMoney 2004. I am able to forcast my cash flow by going to Acounts & Bills->Cash Flow Review, but I cannot print this information. Does anyone have a suggestion for me? You can right click on the chart to print it. But if you want the data, the best way to get at it is to export it to Excel (also by right clicking on the chart), but it only gives you the date and amount of the transaction -- not the Payee. -- "C...