By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,516 Members | 1,183 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,516 IT Pros & Developers. It's quick & easy.

Access Job and Customer Relations

P: 3
I have a db that has a table tblBids and I want to assign multiple contractors to each Bid. I have a Proposal page that will create a proposal for that bid. I need to be able to send a report (the proposal) to all selected contractors for that particular bid. please help
Jun 23 '15 #1
Share this Question
Share on Google+
3 Replies


zmbd
Expert Mod 5K+
P: 5,397
rmnmech:

You don't mention how you are sending the reports, email - with or without outlook, snail mail, bike messenger, or carrier pigeon };-)

1) Normalization is your friend!
Please read thru the following article in our insights directory: Database Normalization and Table Structures

2) Sending multiple reports will be come easier once you have the database properly normalized and designed. You do not mention if you are sending via email, snailmail, hand delivery, or carrier pigeon ( :D ). If you are using outlook the docmd.sendobject method is the most straight forward for simple emails for sending reports. If you will use the bytes.com search tool using keywords EMAIL and include one of the expert/moderator names (NeoPa, Rabbit, Jforbes, Seth Schrock, ZMBD, etc...) you will turn up many threads covering this topic in great detail.

- if you have a specific question or sticking point we can provide you much better guidance.
Jun 23 '15 #2

P: 3
Well what it is is sending one report [ProposalID] using a Text box in a form so i figured I would use a query which would grab the ID number from the form and give me a list but the problem is I am trying to get a Sendobject command to send that specific report with the referenced ID to the list of emails that come up.
Jul 1 '15 #3

zmbd
Expert Mod 5K+
P: 5,397
Ok,
Basic concept will be to open the record set for the emails.
Set up for your report
then loop thru the emails attaching the report.

The following is just a framework it will NOT run "as-is" you will have to provide some more detail and hopefully, like habitat for humanity, some time investment to fill in the blanks.
Expand|Select|Wrap|Line Numbers
  1. Dim zDB as DAO.Database
  2. Dim zRS_Emails as DAO.Recordset
  3. Dim zReport as string
  4. Dim zSQL_emails as String
  5. '
  6. '
  7. Set zDB = CurrentDB
  8. zSQL_emails = '<now here you could just use the Query name or build your SQL string
  9. Set zRS_Emails = zDB.Openrecordset(Name:=zSQL_Emails, Type:=dbOpenForwardOnly) 
  10. 'We could open dynamic; however, we're only going to go thru once w/o edits
  11. 'Another caveat... the forward only doesn't let us get an accurate record count... 
  12. 'so if you have a lot of emails... we should go the dynamic route
  13. 'Your users may not like it if there is a large number of emails to send!
  14. '
  15. 'Not sure how to pull the report as you have not
  16. 'provided enough detail about the report itself
  17. zReport = 'the report's name... hopefully you have this running properly
  18. '
  19. 'Note ONLY the pdf format will preserve the report layout!
  20. If zRS_Emails.RecordCount then
  21.    '
  22. ' I would put another conditional here, maybe some
  23. 'additional code to build a list of emails to be sent
  24. 'something, just to allow the user to know what is 
  25. 'going on...
  26.    Do Until zRS_Emails.EOF
  27.     '
  28.     ' I often send different reports for different
  29.     ' people/reasons... and this is one way:
  30.     ' ' zReport = zRS_Emails![reportname]
  31.     '
  32.      DoCmd.SendObject _
  33.        ObjectType:= acSendReport _
  34.        ObjectName:= zReport _
  35.        OutputFormat:= acFormatPDF _
  36.        To:= zRS![EmailAddress] _
  37.        Subject:= zRS![Subject] _
  38.        MessageText:= zRS![message] _
  39.        EditMessage:= False
  40.      zRS.MoveNext
  41. 'I have code here that will show records pending to send.
  42. 'Once you use the MoveNext you can get a record count
  43. 'and update a textbox on a form, system status bar, etc...
  44.    Loop
  45. Else
  46.    msgbox "There are no clients to send reports"
  47. end if
  48. '
  49. 'Error trap
  50.  
  51. 'If you open it, close it
  52. zRS.close
  53. '
  54. 'of you set it, clear it
  55. if not is nothing zRS_Emails then Set zRS_Emails:= Nothing
  56. if not is nothing zDB then set zDB:= Nothing
Jul 1 '15 #4

Post your reply

Sign in to post your reply or Sign up for a free account.