MailMerge Problem from Access Query

[txacoli]

​Hi, I hope someone can help me here! I´ve posted this in a couple of online forums already and no-one has got back to me so I thought I would try my luck here, where I've had success before (I lost your URL so didn't come here first!!) I'm still pretty new to Access and don't do any of that coding stuff, so please bear this in mind if you write back to me. I am a simple soul and SIMPLE is the key word here



I have just started my own business and have a database of customers set up in Access 2010. I also have a receipts form and I created a Receipts Query, linking the customer ID's and making the Receipt ID the primary key. Finally, I have a receipt letter in Mail Merge, which links to the Receipts Query. Pretty basic I know, but it works for me until I learn more about Access. The problem I have is this............

Until a few days ago, the Receipt query worked fine in Mail Merge. If I entered a receipt in line 100, I could go to Mail Merge and ask it to print record 100 and it would do so. Now it doesn't. It does this - and I hope I can explain it well.......

If customer number 1 had 6 receipts and his receipt numbers are 1, 22, 25, 67, 69 and 94, where before I could put in 94 and it would print that out for customer number 1, now it doesn't. He now has receipts from numbers 1 - 6. It appears to have bunched all his receipts together numerically. If he buys something today and he should have (the next) receipt number 101, instead it will add it to the end of the 6 he already has and will make it receipt number 7, bumping all the other receipt numbers up, even though he is entered into receipt number 101. Similarly, if customer number 60 - a new and unique customer - comes and buys something, he will have the next receipt on the block - 101 in this example. As he is the latest customer, it will let me print that out at 101, but if an earlier customer then comes back and buys something else, then despite now being receipt number 102 on the Receipts list, it will slot it in after his previous set of receipts, making the last (unique) customer receipt number 102 and not 101 as I would have printed it out earlier.

I can't see what I've done wrong. It was working fine until a few days ago and now it just appears to have done this. I'm sure that it's just a button I've inadvertently hit or something, but I can't for the life of me work out what I've done. If anyone can help, I would be extremely grateful for, while I can still print out receipts, it's getting more and more complicated to do so as each person buys something. I have to ask Mail Merge to create receipts for all people and then work out what number receipt I need to print!!!!

Here´s the SQL Code from the Receipts query if you need it

SELECT Receipts.Date, [Personal Details].[1st name], [Personal Details].[1st Surname], [Personal Details].[2nd Surname], Receipts.Course, Receipts.[Amount Paid], Receipts.Month, Receipts.Year, [Personal Details].Current
FROM [Personal Details] INNER JOIN Receipts ON [Personal Details].ID = Receipts.CustomerID;

Thanks in advance and please remember........I´m simple!!!

[Robeen]

Could you post a copy of your database here? You could take out sensitive data if you wish - just leave enough for us to see what happens . . .

[txacoli]

Could you post a copy of your database here? You could take out sensitive data if you wish - just leave enough for us to see what happens . . .

Hi
I've taken out all the sensitive data (all of it!!) and replaced just a few lines with made up names, but it won't let me attach it as it says it has to be 500kb or less. Is this correct?? Surely this is a very small size for am accdb file?

I have to go into class now, but will be out in an hour and will see if there is a response then.

Mark

[Robeen]

You can zip the database. That's what I've seen on most occasions on which someone has posted a db here.

[txacoli]

You can zip the database. That's what I've seen on most occasions on which someone has posted a db here.

Good grief, if I had half a brain I'd be dangerous!! I zip things all the time to send them and never considered it here - anyway, here it is. Hope you can make sense of it.
As I said before, all the sensitive information has been taken out and only a few lines left in with made up names, which means that the receipts are all out of sync as there are loads of customers missing. I've added one new one at the end, so that you can see what happens to their receipt number.

I have absolutely NO idea what I've done wrong. Before, the receipt numbers flowed in order and the date was in UK format. Now, the receipts are in blocks by customer and the date has reverted to a US style. It's basic stuff, yet it's thrown me completely and is making me crazy!!

Fingers crossed on this one!

[Robeen]

I'm still trying to understand what you're describing . . .

1.

If customer number 1 had 6 receipts and his receipt numbers are 1, 22, 25, 67, 69 and 94,

Your 'Receipts Query' doesn't currently have a field in it for 'receipt number' [is this the ID field in the Receipts Table?] - or Customer ID.
Was that always the case - even when your Mail Merge was working like you needed it to?

2.
When you say 'Mail Merge' - are you talking about Mail Merge in Microsoft Word [that's the only Mail Merge I have ever used]?

[txacoli]

Your 'Receipts Query' doesn't currently have a field in it for 'receipt number' [is this the ID field in the Receipts Table?] - or Customer ID.
Was that always the case - even when your Mail Merge was working like you needed it to?

Yes, sorry, there was so much information to try and tell you and I wasn't sure how best to describe it. The Receipt number is the ID field in the Receipts Table. I did a one to many relationship between the ID in the customer table and the receipt table (one customer, many receipts). So, where customer 1 would originally have had his receipts in date chronological order, they are now all bunched together in numerical order. If he has 6 receipts, they will now be listed as 1 - 6. If he generates another receipt in a months time, even though there will have been loads of others generated between his last one and this new one, his new receipt will be Receipt ID #7. Customer number 2, who had receipts from numbers 7 - 10 (for example), would be bumped up to numbers 8 - 11 as Receipt 7 is then allocated to Customer 1. Is this making any sense? It's so difficult to describe.

When you say 'Mail Merge' - are you talking about Mail Merge in Microsoft Word [that's the only Mail Merge I have ever used]?

Yes, Mail Merge in Microsoft Word.

[txacoli]

I've attached a Word Document I've created with a couple of tables in that how exactly how the previous mail merge worked and what's happening now. I think this makes it much clearer and you'll see why it was such a pain to try and explain. Any more questions, get back to me.
Thanks in advance

[Robeen]

First - let me say that I've successfully done Mail Merge with Word and Access ONLY a few times - successfully - but I am by no means an expert at it.
Having said that . . . and got a weight off my chest!!! . . .

I went through your original post because something is still not clicking for me . . .

1. I think you are mixing together:
i. Entering a new receipt "in line 100" - [in Access speak, that would be 'Row 100' of the Table] and
ii. Where that new receipt shows up in your Query ['Receipts Query].

Since you've said your Mail Merge is based on the Query, I think you should focus on the Receipts Query.
Your Receipts Query - the way it is in the DB you posted - did not have CustomerID or ID [from the Receipts Table] in it.
Your Receipts Query - does not have any sort in it - but the results appear sorted by Date.

2. It looks like nothing changed in your Query as rows of data are still appearing in Receipt ID order [and therefore also Date order].
3. I think the problem could be inn the setup of your Mail Merge.

If this project fell to me today, I would:
Add the ID field from the Receipts Table to the Query
Sort the Query by the Receipts.ID field and
Do the Mail Merge setup again and make sure that it prints out by Receipt ID.
Have you already tried that?

One other question:
I've always used Mail Merge when I've needed to do a mass mailing - or mass producing of meiling envelopes - or both.
When I've needed to simply print the equivalent of your individual receipts, I've created some sort of Report for the print out - and then created a Form to pull up the individual receipt I've wanted to print and then clicked on a 'Print' button that prints the Report for the record that is on the Form.
Is there a reason you are using Mail Merge to print individual receipts - when you could quite easily print your individual receipts directly from Access?

[txacoli]

Is there a reason you are using Mail Merge to print individual receipts - when you could quite easily print your individual receipts directly from Access?

Yeah!! I looked into how to do this and all the instructions I found made no sense to me, so I did it this way instead. I understand that I can put a button in Access and print out the letter and while I'm sure it's simple, this simple guy couldn't work it out.

As for my DB problem, someone has managed to help me out of that particular scrape. Would you believe it was a simple matter of putting the dates in ascending order in the Receipts Query?!?!?!
Access drives me crazy, but I mean to expand what I do with it in the future as I learn more, so I decided to start with this basic db and move on from there.

Do you know of any really good (for good read 'simple!') instructions as to how to print from Access directly?

[Robeen]

Briefly:
1. Create a Report [you can start by using the Report Wizard & then modify the report to look the way you want] that has all the information you want your Receipt to have on it.
You can put Text, Labels and other 'permanent' stuff in there [Logo, Borders etc].
Your Report will also have fields from your Query in it. These will be different for each different Receipt you print.
2. Put a Button on your Form Receipts.
3. Cancel out of the Command Button Wizard if you get one.
4. With the Button selected - On the 'Event' Tab in the 'Property Sheet', Click in the 'On Click' row and then click on the elipsis [...] to the right of it.
5. Select 'Code Builder'.
6. Use Code something like this:

Dim ReceiptNum As Double

'This will print a Receipt for the current ID on Form Receipts when the button is clicked.
'Retrieve the Current ID from the Form - to pass to the Receipt Report.
'Open [Print] the report for that one particular ID.

'Get the Current ID from the Form into the variable 'ReceiptNum'.
Forms![Form Receipt].Controls!ID.SetFocus
ReceiptNum = Forms![Form Receipt].Controls!ID.Text

'During Testing - open report in Preview mode.
DoCmd.OpenReport "NameofYourReceiptReport", acViewPreview, , "ID=" & ReceiptNum

'On Deploy - just print the report.
'DoCmd.OpenReport "NameofYourReceiptReport", acViewNormal, , "ID=" & ReceiptNum

Hope this helps.
Let me know if you ever try this and want more help with it! All the best.

It really isn't difficult and you'll get plenty of help here if you run into problems.

[txacoli]

Robeen, I can't thank you enough for the time you've taken over this. You're a star. I won't have time to try this for a couple of days I don't think, but when I do, if I get stuck, I'll be sure to post back on here for some input. Thanks again
Mark

[txacoli]

Hi Robeen

No problems with the Mail Merge function, but I did find a little time to put the button on a new report. It works okay, but when I push the button, it's set to print the report and not an individual specified receipt. Two things:
1. Is there any way to get it to do this and
2. Should I now close this topic and open it elsewhere in the forum for you (or anyone) to help me with this?

Thanks in advance