Using a form to input update query criteria

[Sarah]

I use a query to collate sales invoices for each customer each month in order
to produce statements for them to pay on. So for example, if a customer had
2 invoices in the month, one for 100 and one for 50, the statement would tell
them they have 150 to pay. The query uses a form to collect the parameters to
select a customer, month and year, and produces the information on a report.

When the customer pays, I would like to be able to use an update query to
select the same invoices that were on the customer's statement, and update
fields to show "Date_paid", "cheque_number" and "amount_paid" for each
invoice. The 2 issues are:
1. Can I use a macro or command on the original parameter query form to
input the update criteria, ie. the "Date_paid" etc?
2. Although some of the update criteria will be the same for all invoices
on the statement ie. the "Date_paid" and the "cheque_number", the other
update criteria ("amount_paid) will be different for each invoice, and I
wonder whether I can somehow update the "amount_paid" by copying the orginal
"invoice_amount" field?
Any suggestions would be gratefully received - I have spent so long trying
different things and not getting anywhere! Thanks. sarah

 

[Tom van Stiphout]

On Fri, 24 Jul 2009 06:14:01 -0700, Sarah

I see some problems with this. What if another invoice for the same
month is added after you generate the statement? Probably best to do
what accounting systems would do, which is to store the information
that was the basis for the $150 statement (summary invoice). Then when
a payment is received you know what to apply it to.
Also consider partial payments.
Also consider that some companies have the habit of paying the oldest
invoices, regardless of what the payment was for.

It sounds like you have payment-related fields in your invoice table;
that is very peculiar from an accounting point of view. For example it
locks you in the box of "one invoice, one payment" which negates a
whole host of other options most companies need.

Wouldn't it be better to leave accounting to QuickBooks or some other
couple-of-hundered-bucks solutions?

Once all of the above has been considered, I would probably create a
new "Apply Payments" form, and indeed run some Append and perhaps
Update queries.


-Tom.
Microsoft Access MVP

 

[Sarah]

Yes, a very valid point about accounting software. It's just that I've spent
so much time on it, I wanted to try and make it work! My other thought was
that if I could maybe use a form filter to list the invoices relating to a
particular statement, then the user could actually input the amounts paid
against each invoice, which could then allow for the fact that the occasional
one may not be fully paid. I would need the filters to be set up as an input
form though, rather than using the tool bar at the top of the screen, and so
far not managed to work out how to do that?