Mailshot tracker 1

[allenEd]

Hi,

I'm making a database for mailing customers, I make a table tblCustomers, with all address fields, and also a date field for when the data was entered. I then made another table tblActioned, with a date and notes field.

I have made a form frmActioned and put it in the customer form as a subform...(So I can track when I send a mail shot..). Linked by CustID field.

I do another form which just selects which dates you want to use as a criteria, and a small note field. A buttom with some code, opens a query and report....prints mail shot.


What I have been trying to do with little luck, is when I click the button ( In the date selection form), I would like the date to be entered in the frmActioned along with the note in all records which have been printed.

Any help would be appricated.
Thanks
Allen.

 

[PWise]

if what i understand you want to do is append recoreds to the tblActioned table and in the the blActioned table you have a custid in the on click event after you print the report enter this
docmd.runsql "INSERT INTO tblActioned ( custid,date,note )
SELECT tblCustomers.custid,#" & me.datetxtbox & "# as mydate ,'" & me.note & "' as note
FROM [tblCustomers]
WHERE (((tblCustomers.custid) In (select custid from tblCustomers as temp where date = #" me.datetxtbox "#)));
better yet in the close event of the form enter this
if msgbox("did letter print ok?,vbqustion+vbyesno)=vbyes then
docmd.runsql "INSERT INTO tblActioned ( custid,date,note )
SELECT tblCustomers.custid,#" & forms!selectdate.datetxtbox & "# as mydate ,'" & forms!selectdate.note & "' as note
FROM [tblCustomers]
WHERE (((tblCustomers.custid) In (select custid from tblCustomers as temp where date = #" forms!selectdate.datetxtbox "#)));
please let me know how this went


end if

 

[allenEd]

Hi pwise,

trouble is the form that I select the date has only 3 fields, "datefrom", "dateto" and "notes". The date fields set the criteria for a query that selects the customers (for which to send a mailmerge mail shot). So there is no CustID link on the date selection form. There may be a 100 or so results in the query, which are sent to a word doc.

Thanks for help.

Allen.

 

[PWise]

if i am right with my logic that you want to append records
that the are in the tblCustomers that are between datefro and dateto and record todays date change the sql to
docmd.runsql "INSERT INTO tblActioned ( custid,date,note )
SELECT tblCustomers.custid,#" & date() "# as mydate ,'" & forms!selectdate.note & "' as note
FROM [tblCustomers]
WHERE (((tblCustomers.custid) In (select custid from tblCustomers as temp where date between #" & forms!selectdate.datetfrom & "# and #" & forms!selectdate.datetto & "#)));
this will insert(append) records to the tblActioned table
custid date notefrom form where date in tblCustomers is between datefrom and dateto

 

[allenEd]

Hi Pwise,

Thanks for help, but I get a syntax error. The code is like this (with names as is)...

DoCmd.RunSQL "INSERT INTO tblActionDate ( custid,date,note )"
SELECT tblCustomers.custid,#" & date() "# as mydate ,'" & forms!frmDateSelectNote.note & "' as note
FROM [tblCustomers]
WHERE (((tblCustomers.custid) In (select custid from tblCustomers as temp where date between #" & forms!frmDateSelectMailer.datefrom & "# and #" & forms!frmDateSelectMailer.datetto & "#)));

error on: "SELECT tblCustomers" and &quot" expected on "In"

thanks
Any ideas?

 

[PWise]

all should be in one line
DoCmd.RunSQL &quot;INSERT INTO tblActionDate ( custid,date,note )&quot; <--- extra quote
SELECT tblCustomers.custid,#&quot; & date() &quot;# as mydate ,'&quot; & forms!frmDateSelectNote.note & &quot;' as note
FROM [tblCustomers]
WHERE (((tblCustomers.custid) In (select custid from tblCustomers as temp where date between #&quot; & forms!frmDateSelectMailer.datefrom & &quot;# and #&quot; & forms!frmDateSelectMailer.datetto & &quot;#)));<----- missing quote