Stepping through reports for transmission 1

[ChrisTheAncient]

First - due to commitments to my 'real' job, I may not get to see the reply (replies) until this evening (UK time) - so I'm not really ignoring you! I'm out earning money!

I'm using A97 to create a call logging system. There is a range of customers who may get any number of calls (including zero) each day which are logged and message(s) taken.

I have created a nice looking report based on a query that gives the calls received per customer. The Report has a Header per customer with all the messages listed in the detail section.

If I was to only be printing these reports, there would be no problem - they come out great.

However, each customer should receive a personal report via e-mail. (I've seen loads of threads on this - and I'm not too worried (yet) on that score - but watch this space!)

What I feel I should be doing - with my limited knowledge of vbasic - is stepping through the customers, e-mailing their specific report (though in the experimental stages it's all being down using "acpreview&quot and then moving to the next customer.

So... I used the Help system and decided that the Move commands looked ideal for the job and have been trying to work my way round that. I can see the necessity for MoveLast before MoveFirst and some of those sorts of bits to get a count going. I am also able to follow what the Next and Previous are supposed to do. Trouble is, I've yet to make it call up one customer at the time! All I've achieved so far is being able to count the total messages - not the customers! And I seem to have been going round in circles for quite a while now.

Obviously, I'm making a serious fundamental error of understanding here, but being of limited brain power (all down to age) I seem to be losing the plot a bit! If I could get this stage sorted, I would then feel more confident in moving on to the sendvia stage (some through OE6 and some through WinFax Pro).

Help would be very much appreciated so...

TIA

Chris

*************************************
OK, I'm stoopid. But I'm good at it!
*************************************

 

[DBAMJA]

This won't be as hard as you think.

You might want to do something like this:

Public Sub Report_Generation()
Dim db as Database, rs as Recordset
Set db = Currentdb()
set rs = db.Openrecordset("Select Distinct Customer_ID From Call_Log_Table&quot

rs.MoveFirst

Do While Not rs.EOF
DoCmd.OpenReport "Report_Name",acPreview,,"Customer_ID = " & rs!Customer_ID
Do what ever you want to do with the report
DoCmd.Close acReport,"Report_Name",acSaveNo
rs.MoveNext
Loop

rs.close
End Sub

This should put you on the way to what you want to do. I hope this helps.




DBAMJA

It is said that God will give you no more than you can handle. I just wish God didn't have so much faith in me.

 

[ChrisTheAncient]

DBAMJA

You're right - it does look easier that I suspected!

It looks my original thinking wasn't quite right in the Select statement - but further looking and experimenting on those lines will help. (I'm the world's worst for playing and experimenting (and then mucking it up!), but I sometimes learn a lot from doing that). I'm also pretty good on the plagiarism front as well!

Haven't actually got time to play at the moment - I'm just 'in passing'. But I shall look a little deeper as soon as I get a chance later. But I thought it right to let you know that I've seen it and I'm not ignoring it.

Chris


*************************************
OK, I'm stoopid. But I'm good at it!
*************************************

 

[ChrisTheAncient]

DBAMJA

Very, very sorry. I'm not ignoring you - or the thread. A couple of domestic crises have kept me off the database for a while - but hope to have more goes with your ideas during the day tomorrow.

Then, hopefully, I can feedback so everyone else can do the same as me and get good ideas from other threads.

Chris


*************************************
OK, I'm stoopid. But I'm good at it!
*************************************

 

[ChrisTheAncient]

DBAMJA

Thanks for the help. The task is now cracked!!!

You get a star for that!

I used a 'temporary' form with a button 'Command0' to initiate the sequence.

I did find with the code that, in the end, I didn't need to use the 'where' in the report because that seems to have been picked up in the underlying source query. (?)

At the moment, the code looks like...

Private Sub Command0_Click()

Dim db As Database, rst As Recordset
Dim strNumber As String


Set db = CurrentDb()
Set rst = db.OpenRecordset("select distinct CompanyName from qryEndOfDayReportsEMail&quot

rst.MoveFirst

MsgBox "Record Count = " & rst.RecordCount

Do While Not rst.EOF
DoCmd.OpenReport "rptEndOfDayEMail", acPreview

MsgBox "Hold"

DoCmd.Close acReport, "rptEndOfDayEMail", acSaveNo
rst.MoveNext
Loop

rst.Close

End Sub

...in case it can help someone else on their way. The MsgBox bits just stop things so I can check what's going on.

BTW - and slightly related...

When the report pops up in preview mode - and because of the way it is designed - I only see the header and the preview window isn't big enough to see the records in the detail bit. When I resize a preview window in AccessXP, it remembers that size and uses it all the time. A97 forgets! Is there a way round this?

Again Megathanks for the help

Chris

*************************************
OK, I'm stoopid. But I'm good at it!
*************************************

 

[ChrisTheAncient]

Whoops!

Now I've got the preview working properly, I can see that I get the same record displaying the number of times that there are records.

So I shall have to investigate the filtering a bit deeper!



*************************************
OK, I'm stoopid. But I'm good at it!
*************************************

 

[DBAMJA]

Sounds like you are well on the way. Thanks for the star.



DBAMJA

It is said that God will give you no more than you can handle. I just wish God didn't have so much faith in me.

 

[ChrisTheAncient]

Hmmmm....

And I thought I was doing so well!

I did find that I got the wrong field name in the select distinct statement and corrected that.

Because I was using a MsgBox to hold things still to see if all was OK, I couldn't scroll through the preview to see what was going on. So I did some printing......

What was going on was that the record count was good - and correct - and the complete report for all customers (based on the same query as in the select distinct statement) was printed the number of times of the record count! Not a good idea!

I hope you can see what I'm trying to say.

I have very little hair at my age and I've none left to pull out. So what do I have to do (to the report?) to make it that when I run my code the report prints out the report of the first customer only, pauses at the Msg Box until clicked, prints the report of the second customer only etc. etc...... through until all customers (temporarily three) have had their report printed?

I hope that makes sense.

Then I can go on to the next stage? Please?

TIA

Chris


*************************************
OK, I'm stoopid. But I'm good at it!
*************************************

 

[DBAMJA]

I think by making a few changes to the code that you have above you can get this to work for you.

Private Sub Command0_Click()

Dim db As Database, rst As Recordset
Dim strNumber As String


Set db = CurrentDb()
Set rst = db.OpenRecordset("select distinct CompanyName from qryEndOfDayReportsEMail&quot

rst.MoveFirst

MsgBox "Record Count = " & rst.RecordCount

Do While Not rst.EOF
DoCmd.OpenReport "rptEndOfDayEMail", [red] acViewNormal ,,"[CompanyName] = " & rs!CompanyName [/red]

MsgBox "Hold"

DoCmd.Close acReport, "rptEndOfDayEMail", acSaveNo
rst.MoveNext
Loop

rst.Close

End Sub


Adding the [red] ,,"[CompanyName] = " & rs!CompanyName [/red] will allow each company's report to print alone and changing the acPreview to acViewNormal will send the report directly to the default printer.



DBAMJA

It is said that God will give you no more than you can handle. I just wish God didn't have so much faith in me.

 

[DBAMJA]

Also...

Sorry it took so long to get back to you on this. I was part of the Blackout 2003 and I am just getting back to looking at things.



DBAMJA

It is said that God will give you no more than you can handle. I just wish God didn't have so much faith in me.

 

[ChrisTheAncient]

DBAMJA

Thanks for coming back!!

Very tied up tonight getting an article to an editor for press - but have a spare couple of hours to play tomorrow - so I'll catch it then. I must admit, I've been scratching my brain cell during 'the blackout' trying to think laterally. Not successfully, I might add. I almost started wondering if I needed to think of a very completely different approach! But couldn't think of one!

Thanks for the help and I'll feed back asap.

Chris

*************************************
OK, I'm stoopid. But I'm good at it!
*************************************

 

[ChrisTheAncient]

DBAMJA

You are a flaming genius!!!

I couldn't resist it, I took 10 minutes out as I was so desperate to try it.

IT WORKED!!!

Very many, many thanks for all that. I have regained my sanity (what there was left of it!) and i might be able to talk to my wife in a civil manner instead of just grunting!

Chris

*************************************
OK, I'm stoopid. But I'm good at it!
*************************************

 

[DBAMJA]

Glad to hear that it worked. Good luck.

DBAMJA

It is said that God will give you no more than you can handle. I just wish God didn't have so much faith in me.