Replies

Thank you Charolette and Wendell… I appreciate the input… I look forward to attending…

Thank you both, this is where i stand now…

I ran the adaware… it found 716 objects LOL… i removed them… – I ran spybot, it found 71 items and i removed them – I also ran the HackThis and it found nothing unusual. After removal and restart, the system was much more stable and the speed had picked up. I had one shut down of IE and two reboots using YIM. At least this is progress. I ran the info on my puter and the stats are like this: 128 MB RAM, 999 megahertz intel celeron processor… computer is about 3 years old… however it was not off the shelf, we’ve built it. I haven’t upgraded the memory in a while. The mouse, i clean regularly cause i noticed they stop wheeling correctly. I delete cookies and temp files and clear history regularly. No devices had a flag in the device manager. I tested the system, by turning on the YIM, running the camera, running a voice connection and IE. Bingo… that made it lurch and reboot itself… So, at this point i think getting more memory will definitely help and I need to eliminate that as a possible cause. If I still have problems after installing the memory, I will repost.

Thank you so much for the suggestions. Your assistance is much appreciated.

Thank you both, this is where i stand now…

I ran the adaware… it found 716 objects LOL… i removed them… – I ran spybot, it found 71 items and i removed them – I also ran the HackThis and it found nothing unusual. After removal and restart, the system was much more stable and the speed had picked up. I had one shut down of IE and two reboots using YIM. At least this is progress. I ran the info on my puter and the stats are like this: 128 MB RAM, 999 megahertz intel celeron processor… computer is about 3 years old… however it was not off the shelf, we’ve built it. I haven’t upgraded the memory in a while. The mouse, i clean regularly cause i noticed they stop wheeling correctly. I delete cookies and temp files and clear history regularly. No devices had a flag in the device manager. I tested the system, by turning on the YIM, running the camera, running a voice connection and IE. Bingo… that made it lurch and reboot itself… So, at this point i think getting more memory will definitely help and I need to eliminate that as a possible cause. If I still have problems after installing the memory, I will repost.

Thank you so much for the suggestions. Your assistance is much appreciated.

Thanks. I had resolved it by taking off the same as previous on the first three pages and typing the number manually, however, pages 4 and 5 are a table of contents, which were automatically generated and update together. So I numbered those pages as one section and it seems that the page setup liked the fact that I was starting my page numbering from v. as opposed to i. The second page of the TOC was vi., as I had hoped. Paperclippy hasn’t won yet…

Thanks. I had resolved it by taking off the same as previous on the first three pages and typing the number manually, however, pages 4 and 5 are a table of contents, which were automatically generated and update together. So I numbered those pages as one section and it seems that the page setup liked the fact that I was starting my page numbering from v. as opposed to i. The second page of the TOC was vi., as I had hoped. Paperclippy hasn’t won yet…

Thanks Echo and jscher!

It was the kiosk thingy… And an extra thanks to Echo… it was from an older post of yours and your site that I was able to be successful at making the autorun cd with my presentation on it. It was near perfect, save the kiosk thingy. Many thanks. Jenn.

Thanks Echo and jscher!

It was the kiosk thingy… And an extra thanks to Echo… it was from an older post of yours and your site that I was able to be successful at making the autorun cd with my presentation on it. It was near perfect, save the kiosk thingy. Many thanks. Jenn.

Ok, i think that the subreport idea isn’t working…

because there are various business rules that warrant how the transmittal report letter needs to be structured… how about using a transmittal id field in tbCampaign. this field could be updated to a specific number based on these rules and that field can be used in the query to choose the report to open for that legislator. the issue is how to write the code…

rules are basically as follows:

if the [camptype] is “other”, OR if the [count of lobbyid] >1, update field to 1

if the [camptype] = “federal”, update the field to 2

if the [lobbyINI] = [CurrentINI] where [CurrentPos] = “PACStaff”, update the field to 3

if the [count of lobby id] =1, update the field to 4

report “1” would put the director, staffer, (as dlookups) and the additional lobbyist (from the underlying query) in pos 2, 3 and 4 respectively

report “2” would have the same layout yet have a different message in the text boxes and this camptype doesn’t have lobbyists

report “3” would have the staffer (dlookup) in position 2 and the signature field would be removed. (so he can sign his own)

report “4” would have the staffer (dlookup) in position 2 and the additional lobbyist in position 3.

reports 3 and 4 are very similar… perhaps its possible to combine them and simply disable the results of the lobbyist field when the value = the staffer and disable his signature… then turn everything back on when the lobbyist field is someone other than the staffer…………….this signature thing leads me to think there should be a yes/no “signown” field in tblLobbyist

with this solution, there will be 3 or 4 reports each with different recordsources.

anyone have concerns with a method like this one?

Ok here’s the db. let me know if you find other possibilities. thanks a bunch.

HAHAHA! so much for resting Wendell!

the subreport is never blank.

there is at least one lobbyID for every LegID. so technically, position three is always filled with someone. i simply make the subreport invisible when the lobbyID expressed = the staffers lobby ID, cause he’s a dlookup in position 2.

sometimes there will be two lobbyID’s for each LegID… and because the subreport is 2 columned, the two records fill position 3 and 4. the catch is that when there are two lobbyid’s, one of them is always the director… and because of that… he needs to be in position 2 and the staffer moves to position 3… (this is the case where I tried to use a vertical subreport… (checked the down then across box in the page set up) and tried using positions 2 and 4 for the subreport and put the staffer in position 3. – inorder for this solution to work,, i would need to additionally change the page layout of the subform at runtime… and for all that work… i might as well make all individual reports, subreports and formats… i am resistant to the option because i prefer to change one letter rather than three. anyhow, i will post back with the db… it’s the version i have at home so it’s alittle different but close enough. it’s in 2000 now but i’ll convert to 97.

thanks Wendell, that old version is out… but yes you are very right in your interpretation of what I need…

1 2
3 4

there are 4 signature spaces on each letter… two rows of two… just like in a real letter. the first space is occupied by someone who doesn’t factor into this… his info comes from dlookup in tblcurrent…. the 2nd position is usually occupied by the staffer… cause he signs all letters and his info can stay in position 2 in all cases except when the subreport has the director listed as a lobbyist. positions 3 and 4 are used by the subreport. i’ve tried another variation for when the director is signing… that’s turning the subreport to be vertical first and use positions 2 and 4 but the best results came with the former. the report is simply a transmittal letter… concatenating several fields into paragraph format and turns off and on some fields based on values. anyway, i’ll get back to this tomorrow… i have an Access headache right now…

if you think it’s warranted, i can attach a scaled down version of the new db tomorrow… thanks.

Thanks for agreeing with Hans, Wendell!

Anyway, I need to understand your solution more… I can’t quite visualize it yet. I’ll give alittle more background…

tblLobbyist has all the lobby info, including signature field. jctblLegLobby is a junction table, autonum, with LegID and LobbyID… (many-many). the subreport’s recordsource is based upon a query that joins these two tables. the LegID is used as the linking field back to the main report… the main report is based on a query that joins alot of info about the legislator.

The problem to overcome is this:
there is an employee (staffer) who must always sign the letter. his sign, name, title and initials are on the main report via a dlookup to an unrelated table tblCurrent. this person is also listed in tblobbyist and so one sql needs to filter him out if he shows up in the recordset. another situation is that someone else in tblLobbyist is higher in the chain of command (director) so when he shows up, the signatures need to be shifted around to accommodate the hierarchy. AND for each record where the director shows up, there will be a second entry in jctblLegLobby. mind you, this LobbyID will not be the guy from the first circumstance and should it ever be, well, someone else can figure that out. So the best I could come up with is the three reports with the shared subreport… fyi, the subreport is a two column report so the signs are side by side. what i’ve done in report1 is dlookup the staffer’s name and turn the subreport invisible when he shows up in the recordset. report2 is a dlookup of the directors and staffers name and filtered them out of the recordset with subreport containing any additional lobbyist. then a third report that is setup like report1 and filters for the staffers own records and removes the signature cause he likes to sign certain letters personally. So having said that… should you offer an entirely different approach… I’ll scream first… then be happy to hear you out… thanks to you both.

oh, and I predict that very soon, someone will want to use a different signature based upon who the letter is being sent to… (for instance… first name only, versus full name… there are so many exceptions… i’ve forgotten the rules…

Oh Hans, I am truly saddened by your news yet I can see the hope in your message

the subreport is based on a stored query which was one of the Cases I presented in the original post and is as follows:

qryLobbyistTransmittalSubreport
SELECT DISTINCTROW jctblLegLobby.jctblLobbyLeg, jctblLegLobby.LegID, jctblLegLobby.LobbyID, tblLobbyist.LobbySign, tblLobbyist.LobbyTitle, tblLobbyist.LobbyName, tblLobbyist.LobbyIni
FROM tblLobbyist INNER JOIN jctblLegLobby ON tblLobbyist.LobbyID = jctblLegLobby.LobbyID;

If I understand you correctly Instead of changing the recordsource of the subreport at runtime, I should change the sql of the underlying query that is the current recordsource of the subreport. I have not ever done query definitions so I will search for some posts in the forum on that topic.

In terms of procedure assembly… a query needs to be defined, then the report opened, printed, closed, choose the next query definition, open the proper report, print, close, etc til all 3 reports have been processed. In using this method, how can I open a specific report on the report tab and have the proper sql inserted?

Thanks. Jenn.

Thank you all for the input… in the end, a combination of the two approaches worked… except for the darn error #2105 msg at the end of the recordset – for now, i simply addressed that error specifically. I have not tried Jim’s suggestion yet but will look into it… This db is coded to my particular computer setup and the day will come that i will have to change my system… I like the integration of microsoft fax and outlook… but it’s not used much anymore… so finding other “options” for handling faxes is necessary, perhaps the tried and true print it and fax it yourself method??? Anyhow…

here’s the code used for sending the emails:

Private Sub Command26_Click()

Dim appOutlook As New Outlook.Application
Dim lst As Access.TextBox
Dim strsql As String
Dim msg As Outlook.MailItem
Dim strBody As String
Dim strEMailRecipient
Dim strSubject As String
Dim strTo As String
Dim rstMyForm As dao.Recordset

On Error GoTo ErrorHandler

Set rstMyForm = Forms!qryEmailApprovalsOver500.RecordsetClone

If rstMyForm.RecordCount = 0 Then
MsgBox “There are no approvals to send”
Else
DoCmd.GoToRecord acActiveDataObject, , acFirst

End If
Do Until rstMyForm.EOF

Set lst = Me.[txtEmail]

strEMailRecipient = lst.Value
strSubject = Me.[txtSubject].Value
strBody = Me.[txtBody].Value

Set msg = appOutlook.CreateItem(olMailItem)

With msg
.To = strEMailRecipient
.Subject = strSubject
.Body = strBody
.ReadReceiptRequested = True
.Display
.Send
End With

DoCmd.GoToRecord acActiveDataObject, , acNext

Loop

ErrorHandlerExit:

Exit Sub

ErrorHandler:
If Err.Number = 2105 Then
MsgBox “You are finished sending approval emails”, vbOKOnly, “Sending Complete”
Resume ErrorHandlerExit

Else
MsgBox “Error No: ” & Err.Number & “; Description: ” & Err.Description

Resume ErrorHandlerExit
End If
rstMyForm.Close

End Sub

And Stewart, you are right on about the combo of experiences and techniques, the lounge provides me so many ideas and solutions. It is truly a valuable resource.

P.S. what’s the difference between Do Until… Loop and Do…Loop Until ???

Thanks Pat and John… let me clarify alittle more.

one email, one name… i used to string together the email addresses but cannot do so anymore… everyone views the TO: field and there is poor error handling when using the BCC: field (see my post in the outlook forum). therefore… the form that i am using to create the msg is really several fields concatenated together to create a document as the body of the email… I cannot use send object as several of the recipients REFUSE to open attachments… they are used to recieving the text and they vehemently resist changing that… fine… so… each record in the form is a unique email made specifically for an individual… there is some repitition of subjects and msg bodies and even recipients but these really can’t be grouped in any way.

currently, i have to press the “send email” button that runs this code, on each and every record in the recordset. I would like it to loop through each record on the form (by itself).. just as if I was pressing a navigation button then reclick the send email button til it gets to the end… (IS THERE A WAY TO LOOP THROUGH THE DATASHEET OF THE FORM AS OPPOSED TO THE UNDERLYING QUERY SEEING AS THERE ARE SEVERAL CALCULATED CONTROLS ON THE FORM THAT NEED TO BE INCLUDED IN THE MSG TEXT)

now i understand my thought process may be wrong here… which is why i’m reaching out for advice.

REGARDING THE FAX: the way outlook and microsoft fax work together is that if I put a number in the TO: field of a msg… outlook “knows” that it’s a fax. I send groups of emails regularly, some have email addresses, some fax numbers – outlook does it’s job of resolving which is which and sending appropriately. so my question is really about what the best method is for getting the number. the current recordset for the form’s underlying query does not include the fax numbers… so i only need to pull it from another table, (the link is the field DrID), when email is empty.

But as I’m thinking more about this… i could probably go a different route with the faxes… they can be grouped by recipient with the msgs being sent as attachments. In this case… each msg for the one recipient would have to be constructed, attached to an email and sent to the respective fax number. not sure how to accomplish that but i welcome comments and suggestions.