Code help please

[Tom]

Hi

Any ideas/suggestions on how to achieve the following would be appreciated:

Have a Search form that has various tick boxes which list client criteria &
on clicking on a command button the code behind searches a client table for
clients that meet with the selected criteria & produces a recordsource which
is then used as data source for a mailmerge to a selected document. That
field list is ClientID, Company, Address, Town, County, PostCode. The max
number of records in this recordsource is unlikely to exceed 300.

Within the clients form of that db there is a subform which lists any
contact notes with that client. The recordsource of that subform is a table
called log whose fields are: LogId, ClientID, Date, Note.

What I want to be able to achieve is some code that would take the ClientID
from the 1st recordsource and for each ClientID enter it into ClientID field
of the Log table, today's date in the Date field and the mailmerge document
name in the Notes field.

Using Access 2002

TIA

Tom

 

[Graham Mandeno]

Hi Tom

You need to run an append query for your Log table, using the filter string
that you have created to do the mail merge.

Insert Into LogTable (ClientID, [Date], Note)
Select ClientID, Date(), "merge doc name" from Clients
Where <same filter string you used for the mail merge>;

BTW, I recommend you change the name of the field "Date" to, say, "LogDate".
"Date" is a reserved word (the name of a function) and you can run into
problems using reserved words as object names. Note that this is why the
square brackets are necessary in the SQL above.

 

[Tom]

Graham

Many thanks - works a treat

Tom

Hi Tom

You need to run an append query for your Log table, using the filter
string that you have created to do the mail merge.

Insert Into LogTable (ClientID, [Date], Note)
Select ClientID, Date(), "merge doc name" from Clients
Where <same filter string you used for the mail merge>;

BTW, I recommend you change the name of the field "Date" to, say,
"LogDate". "Date" is a reserved word (the name of a function) and you can
run into problems using reserved words as object names. Note that this is
why the square brackets are necessary in the SQL above.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi

Any ideas/suggestions on how to achieve the following would be
appreciated:

Have a Search form that has various tick boxes which list client criteria
& on clicking on a command button the code behind searches a client table
for clients that meet with the selected criteria & produces a
recordsource which is then used as data source for a mailmerge to a
selected document. That field list is ClientID, Company, Address, Town,
County, PostCode. The max number of records in this recordsource is
unlikely to exceed 300.

Within the clients form of that db there is a subform which lists any
contact notes with that client. The recordsource of that subform is a
table called log whose fields are: LogId, ClientID, Date, Note.

What I want to be able to achieve is some code that would take the
ClientID from the 1st recordsource and for each ClientID enter it into
ClientID field of the Log table, today's date in the Date field and the
mailmerge document name in the Notes field.

Using Access 2002

TIA

Tom