Configureable Query or otherwise

[dustinbrearton via AccessMonster.com]

So I have experience (nothing real indepth) in programming in Excel, but now
that I am trying to apply it to Access nothing much seems to translate. Down
to the question. I have a DB that will eventually email customers about past
due invoices. I have the emailing part down from some borrowed code in
another DB we use internally. The DB will consist of the below. I need to
know if it is possible to build either querries or reports on a customer
basis and then export the querry/report in excel so that I can attach them
individually through the emailing code.

Link Table -
ARTable - An excel file that comes from an internal program. Contains
information on accounts receivable (ie. client name, client number, invoice,
invoice date, balance and invoice age.

Table
tblClientInfo - tbl that will be updated by the admin. Will contain client
name, client number, local contact, contact's email, payment terms.

Querry -
qrycustomerOutsideTerms - relationship between client number in tblClientInfo
and ArTable shows all invoices with client number name and age that the age
is greater than the terms.

I would like to be able to take qryCustomerOutsideTerms and have access
create one file for each customer that has invoices on
qryCustomerOutsideTerms. I already have the code to send the emails but am
stuck on trying to get the qry to be confirgurable to that level without
manually creating a seperate query per customer (200 + customers on the AR at
any given time). Thanks for any help.

 

[Michel Walsh]

Here a *possible* way to do it, at least, the great lines (not in full
details). I assume you can write a function, in a standard module, accepting
one argument, a client_ID. That function could then call your
qrycustomerOutsideTerms, "sending" to your query its argument, the client
ID, and launch the relevant actions to be taken, with what return your
query. Say this function is called Toto.

Next, write a query, which will identify all the clients to be considered,
but listing the client only once. Then, add, as computed column: Toto(
client_ID ). When you will run this top most query, the function Toto will
be executed for each client_ID your query has picked.


So, two independant steps:
- writing the function Toto which does the job correctly for one given
client ID (given as argument)
- writing a query which pick, once, no dup, all clients which are to be
'processed' by the function Toto.



I may have not understood what you want to do, though.


Vanderghast, Access MVP