Make Auto Entry

[Nylex]

I have a form which is all the details on my Cleaning Contracts - some
clients have more than one property that is cleaned
When the client books a new workorder I call up the property and enter the
next date it is to be cleaned
The price and any special instructions on on the form and when date is
entered I then print out a workorder for the staff
I have another Table which has in it the WorkorderID, Date Cleaned and Price
and Payment which I use to print an Invoice from
Is there any way that when the date to be cleaned is entered can you open a
table and make entries direct into it - only want WorkorderID Date and Price
as in the past I have forgotten to open the Invoice form and make the entry

 

[Tom van Stiphout]

On Sat, 19 Jul 2008 03:46:00 -0700, Nylex

I am surprised you want to make the entry BEFORE the cleaning actually
takes place.
You could of course open that second form in the
CleaningDate_AfterUpdate event. If you want fully automated the
elegant way to do this is to run an Append query that would take a
parameter WorkorderID. Something like:
insert into tblInvoices(WorkorderID,DateCleaned,Price)
select WorkorderID,DateToBeCleaned,Price
from tblWorkorders
where WorkorderID=[parWorkorderID]
Then write a few lines of VBA to run this query.

-Tom.
Microsoft Access MVP

 

[Nylex]

Tks
That did it fine
The reason I want to do it this way is because I would prefer a client to
say you charged for a job you didnt do rather than miss invoiceing 2 Jobs i
did early this year - the monthly statement is produced from this invoice
file - did not appear on statement - did not get paid

On Sat, 19 Jul 2008 03:46:00 -0700, Nylex

I am surprised you want to make the entry BEFORE the cleaning actually
takes place.
You could of course open that second form in the
CleaningDate_AfterUpdate event. If you want fully automated the
elegant way to do this is to run an Append query that would take a
parameter WorkorderID. Something like:
insert into tblInvoices(WorkorderID,DateCleaned,Price)
select WorkorderID,DateToBeCleaned,Price
from tblWorkorders
where WorkorderID=[parWorkorderID]
Then write a few lines of VBA to run this query.

-Tom.
Microsoft Access MVP

I have a form which is all the details on my Cleaning Contracts - some
clients have more than one property that is cleaned
When the client books a new workorder I call up the property and enter the
next date it is to be cleaned
The price and any special instructions on on the form and when date is
entered I then print out a workorder for the staff
I have another Table which has in it the WorkorderID, Date Cleaned and Price
and Payment which I use to print an Invoice from
Is there any way that when the date to be cleaned is entered can you open a
table and make entries direct into it - only want WorkorderID Date and Price
as in the past I have forgotten to open the Invoice form and make the entry

 

[Ken Sheridan]

You don't really need the Invoices table at all. In fact having one
introduces redundancy and the possibility of inconsistent data. You can
simply base the invoice report on a query which returns the relevant columns
from the WorkOrders table, though I'd have thought that in reality this would
be joined to other tables such as Clients and Properties to include data from
those tables in the invoice.

For an example of an invoice generated in this way take a look at the
Invoice report in the sample Northwind database which comes with Access.
You'll see how this is based on a query which joins various tables to the
Orders table to pull in data on the customer, products etc.

A single invoice could be printed with a button your work orders data entry
form say using:

Dim strCriteria As String

' ensure current record is saved
Me.Dirty = False

strCriteria = "[WorkOrderID] = " & Me.[WorkOrderID]

' print invoice for current work order
DoCmd.OpenReport "rptInvoice", WhereCondition:=strCriteria

This assumes WorkOrderID is a number data type, If its text data type use:

strCriteria = "[WorkOrderID] = """ & Me.[WorkOrderID] & """"

Or if you want to batch print invoices for a specified period you could have
an unbound dialogue form with unbound text box controls: txtStartDate and
txtEndDate, and a button to print the report with:

Dim strCriteria As String

strCriteria = "[Date Cleaned] >= #" & _
Format(Me.txtStartDate,"mm/dd/yyyy") & _
"# And [Date Cleaned] <#" _
="#" & Format(DateAdd("d",1,Me.txtEndDate),"mm/dd/yyyy") & "#"

' print invoice for all orders within date range
DoCmd.OpenReport "rptInvoice", WhereCondition:=strCriteria

To record payments you simply need to add columns to the WorkOrders table,
e.g. DatePaid, AmountReceived. You might also include a DateInvoiced column.
This could be automatically be given the current date when invoices are
printed if you wish. In the case of printing a single invoice from the work
orders form this would simply require the following line of code to be added
to the button's Click event procedure:

Me.DateInvoiced = VBA.Date

In the case of batch printing for a date range it would be a case of having
the code execute an update query to update the DateInvoiced column for those
rows within the date range to the current date. This is a little risky when
printing, however, because the report might not always print correctly. I've
done similar routines where one approach has been to make the updating of the
column conditional on the user confirming, via a MsgBox function call, that
the report has printed successfully that the report has printed successfully.

With these additional columns in the table it is a simple task to identify
outstanding debtors of course by means of a query WHERE InvoiceDate IS NOT
NULL And AmountReceived < GrossPrice. Note that the AmountReceived column
should not allow Nulls (Required property = True) and have a default value
of zero, something which is generally advisable for currency columns.

Ken Sheridan
Stafford, England