How to print POS receipts in MS Access

Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc used Ask the Experts™
on
Hi

My point of sales receipts is compiled from queries that gather the following:
(1)      Taxes
(2)      Quantity sold & prices
(3)      Other calculations
So in short it is not possible to use the current form to act as a receipt report. These queries are the one providing data for the receipt report, that is why currently to print a receipt report you need to close the current input form and open the parameter query form.
Now I’m wondering if there could a better way of binding the parameter query using the input form so that people do not need to close the current input form but can just invoke the printing of a receipt from the same input form.

I Thought MS Access save data into the table immediately after leaving a control, if yes how can I use the current primary key of the current receipt form as part of the parameter query so that the document printed is the current document showing on the input form screen?
I want to maintain the current form parameter query because sometimes the document may fail to print, then with the current parameter form query available I’m able to preview the document and reprint it.

I know it is possible to attach a macro to either print directly to the printer or preview the document by using the built in macro wizard, but the problem is how to the current document I’m not sure here because this macro wizard will only ask for the report to be printed, this means that I will end will all receipts being printed.

Could it be that there is VBA function to use here?

See how you can help here.

Regards

Chris
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
John TsioumprisSoftware & Systems Engineer

Commented:
Without inside knowledge of your application is hard to speculate but if many calculations are involved probably s good idea would be to consolidate all your desired output data to a temp table that Will act as your report recordsource .
I know its possible to print a report using VBA but the problem is how to select the required invoice?
ste5anSenior Developer

Commented:
I would go a step further:

Receipts are fiscal relevant. Thus they must be materialized in a normal table before printing. So everything printed on it, should be calculated and stored in that table after the sale is done. And then you print it just from this table.

Another benefit: You get a second auditable trail in your system for free.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
We do as Stefan does. Writing to the final table is where the invoice/receipt number is applied.
The details (invoice lines) are written to a subtable.

Then, all you need to do is to print the receipt (report) based on that single number.
All what you have said is already in both parent receipt table and receipt detail line table , all what I'm saying is how to fine tune the code below, so that I'm able to print the current receipt number after creation:

Private Sub SalesPrintDoc_Click()
DoCmd.OpenReport “rptSalesInvoice”, acViewPreview, , “invoiceID = ” & Me.invoiceID
DoCmd.PrintOut , , , , 1
End Sub

I do not know why it is not working?

I want also to be able to mark the printed copy as copy receipt and the first time print as original. Is there a way to amend the above code?
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
All you need to print the receipt is:

DoCmd.OpenReport "rptSalesInvoice", acViewNormal, , "invoiceID = " & Me.invoiceID.Value"

Open in new window

For printing a copy labelled as such, the easy method is to have a separate report for that:

DoCmd.OpenReport "rptSalesInvoiceCopy", acViewNormal, , "invoiceID = " & Me.invoiceID.Value"

Open in new window

ste5anSenior Developer
Commented:
I want also to be able to mark the printed copy as copy receipt and the first time print as original. Is there a way to amend the above code?
This is pretty hard, cause you don't have that much control over the printer as you need to do it fool proof.

Use a print log. When a row exists, then it is a copy. To capture printing, you need to print directly without preview:

Private cmdPrintReciept_Click()

  On Local Error GoTo LocalError

  DoCmd.OpenReport "rptSalesInvoice", acViewReport, , "invoiceID = " & Me.invoiceID.Value"
  SqlExecuteFmt "INSERT INTO PrintLog ( InvoiceID, PrintDateTime, StatusText ) VALUES ( {0}, Now(), 'Success.' );", Me.invoiceID.Value
  Exit Sub

LocalError:
  SqlExecuteFmt "INSERT INTO PrintLog ( InvoiceID, PrintDateTime, StatusText ) VALUES ( {0}, Now(), {1} );", Me.invoiceID.Value, Err.Description

End Sub

Open in new window


But this can lead to wrong success messages depending on the printer used. So you may need user interaction to gather the "printed ok" state by using a dialog.
Thank you all it has worked!

Regards

Chris

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial