Newbee with mailmerge and ticking a checkbox

[splreece]

Hi all,

I am a relative newbee when it comes to vba although I am very useful in that I have a good level of understanding of excel and access functionality.


I have a mailmerge query that pulls by date (-14days from a chosen field date).

What I need to do is to fill the date the query ran in textboxes on each of the query pulls to say they have been queried. I can't figure out how to automate it.

Therefore:

Say I have "mailmerge1date" textbo that is empty.

I run "mailmerge1". I need to populate "mailmerge1date" textbox with the date I ran "mailmerge1".

Because

I will run another merge query after that which will list all entries that haven't received a response in a defined time time (so "mailmerge2" date will need be put into textfield "mailmerge2date".


This is a must for all entries as it is debt package so I need to be able to guarantee if a cust received mailmerge1, then 2 weeks later, if no response, they are included on mailmerge2's list (and so on).


Any thought so recommendations would be appreciated.

I have the merges and the dates, I just need to know how to autofill each textbox with a date after each mergequery.

many thanks in advance.

 

[Minty]

I think you need to rethink your data structure. Assuming you have a unigue customer ID you probably would be better having a CustomersReminders table.
tblCustRemind
ReminderID - Primary Key - Auto Number (PK)
CustomerID - Your customers unique id (Foreign Key or FK)
ReminderDate - the date the reminder was sent.

You can then send as many or as few reminders as you need to , and a simple count will tell tell you how many reminders you have sent and when it was sent.

Your layout at the moment is spreadsheet thinking (Horizontal data layout) Access works and scales bets with a vertical data layout.

 

[splreece]

Thanks for the quick response.

Thats quite funny... I could imagine for expert users that the logic behind the problem solving shows which program i have more history using.

bang on...

so when structuring the table, how do i get the link between the mailmerge done and the interface recording the merge time.

I maybe thinking incorrectly, but it sounds as though the control and records and history are held in separate tables and imported into the main form.

This makes sense but how would i go about recording the merge data?

I have the query running which separates the listings. I am just trying to avoid having to click and enter the date in each entry?

 

[Minty]

I'm assuming your merge data will be a one off operation? If so what is the layout at the moment.
You possibly can use Excel to transpose the reminder 1, 2, 3 etc. into a vertical layout.

 

[splreece]

currently access is being used as a user input device only. so very flat (only 1 table)

a lot of accounting manipulation is done in excel vba i just needed a way for multiple locations to key in the same class of data in the same way which spits out the core data in the same format (currently everyone uses different locally built excel docs so no consistency).



i have only 1 table, 1 form (for input) and 3 queries (for mailmerge at 2wk debtor, 4wk debtor and 56day debtor).

The queries are all ran based on an outstanding balance and date of last letter sent (i.e "merge1" will only pull outstanding who havent received any reminders...."2" will pull outstanding who received 1 but no others...and so on.

I am happy to rethink the method of query as i have an excel background as you rightly pointed out so i am dictating query listings based on textbox contents (excel index/match type thinking).

 

[Minty]

Okay - so take a step back and look at what process you currently do , and where you might want to end up.

I suspect almost all the information you have could be split down into 3 or 4 related tables. Why not give us an idea of the fields and their purpose (If not blindingly obvious) from your spreadsheets?