Replies

All righty. Here’s the snippet.

Private Sub Report_Close()
DoCmd.RunSQL “INSERT INTO tblPrintedReports ( ClientID )” & _
“SELECT First(LogNotesQry.[Client ID]) AS [FirstOfClient ID]” & _
“FROM LogNotesQry”

DoCmd.SetWarnings False

End Sub

After I posted the question about SetWarnings, I read the help file again and realized that I had used 0 instead of FALSE so I changed that and it worked perfectly.

Just an update…

I decided to use RunSQL code because I didn’t want to open Append Query window each time the report closes. However, there are two new issues:

1) Since I use a parameter query to ask the user for the name of person that the user want to see log notes for. Now, each time I close the report, it asks me for the name of client. As far as I can tell, it is because I am using Append query based on the parameter query that I’m using to filter the reports on. How do I tell RunSQL to extract the name from the report itself so that it wouldn’t ask me for name again after closing?

2) It warns me that it would ‘append’ one new record to the table. I want to turn off this warning and it seems like using DoCmd.SetWarning 0 doesn’t work.

TIA

I’d need the report to append after printing. I’ll try both options and see which works the best.

By the way, I found this article in Microsoft KB: Q154894 that does practically the same thing for Access 97. I tried it out and it worked great, however, it only enters the name of the report. I’d love to modify that code to also enter the specific PERSON the log notes is for (In my report, there is a “field” that prints name of person). Do you want me to attach the code that is listed in that article if you think it would be a better way of doing it?

TIA again.

Charlotte, how do you automatically run an action query after printing a report? I have an append query that does exactly what I need it to do. However, I’m not sure how do I tell the report to run the Append query?

Secondly, do I add the log table to the query I use to prepare the Log Notes report in order to limit the records to those that has not been entered into the log?

Thanks for your help. I’ll try that.

Thanks for your suggestion. How do I get this information entered into the table automatically?

Mark, thanks for the warning, I believe in my situation, it isn’t mandatory that the method to be foolproof. As long as it works most of the time, it will do. Grin.

Yeah, I could use a button. Now, I need to figure out how to automatically insert the email address the form is supposed to be sent to. We wanted to prevent people from typing wrong Email address in the TO field.

Brent

Exactly what I needed. Thanks.

Sorry, our network connection went down for 24 hours. Just got back the access…

I checked the computer, it was running Service Pack 1 so I applied SP 2 and got some strange behavior, especially when it restarted, all I got was blank screen (on the desktop) and was forced to CRTL+ALT+DEL and log out which finally kicked it into desktop. Searching for files is still extremely slow and now we are receiving an error message that “CSC Notifications Window” is not ending properly whenever we log out or shut down Windows? Anyone recognize this?

Brent

Thanks! It did the trick.

Brent

My apologies for not replying any sooner. I was out for 4 days for the Memorial’s weekend…

SteveH: Your tip helped immensely! That was exactly what I needed. I never knew about manipulating indexes. Simply amazing.

For rest of you: I really appreciate your feedback and input. Most of you warned me about duplicate names, different person scenario. I’ve decided to add birthdate to the multiple index that SteveH suggested. The odds of person having exact same name and birthdate is astronomical. Since the mailing list only has 1,000 names and I don’t believe it will even hit 1,500 next 5 years, especially when we prune it every year, I don’t believe we will run into duplicate name, birthdate and different persons. grin.

Again, I appreciate your time and effort in helping me.

Ahhh…Welcome to the intrigue of my database world here at Lighthouse. Smile.

I had originally put the application status in the applicant’s table but problem is that not all applicants actually applied for the camp since the applicants comes from mailing list. So, I moved it to Camper table and Volunteer table, had problems with using Union Query in order to combine three different tables (Campers, Volunteers and Easy Volunteers, don’t ask what it means…grin). I simply found it much easier to set up a separate table that held application status. It’s working real well.

Thanks to everyone for their help. I appreciate it!

You didn’t miss anything here.

This table has unique ID using AutoNumber and Index set to Indexed (No Duplicate) but if an user adds a new person, the table automatically creates a new ID number which unfortunately, is different from the old record which has the exact same person.

I haven’t met a John Smith myself either.

I’ll try it again…What I didn’t mention was that I actually set it to “triple key” (ParticipantID, LastName and FirstName) Would this have made it happen that way?