Access Job and Customer Relations

[rmnmech]

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

[zmbd]

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.

[rmnmech]

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.

[zmbd]

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.

Dim zDB as DAO.Database
Dim zRS_Emails as DAO.Recordset
Dim zReport as string
Dim zSQL_emails as String
'
'
Set zDB = CurrentDB
zSQL_emails = '<now here you could just use the Query name or build your SQL string
Set zRS_Emails = zDB.Openrecordset(Name:=zSQL_Emails, Type:=dbOpenForwardOnly) 
'We could open dynamic; however, we're only going to go thru once w/o edits
'Another caveat... the forward only doesn't let us get an accurate record count... 
'so if you have a lot of emails... we should go the dynamic route
'Your users may not like it if there is a large number of emails to send!
'
'Not sure how to pull the report as you have not
'provided enough detail about the report itself
zReport = 'the report's name... hopefully you have this running properly
'
'Note ONLY the pdf format will preserve the report layout!
If zRS_Emails.RecordCount then
   '
' I would put another conditional here, maybe some
'additional code to build a list of emails to be sent
'something, just to allow the user to know what is 
'going on...
   Do Until zRS_Emails.EOF
    '
    ' I often send different reports for different
    ' people/reasons... and this is one way:
    ' ' zReport = zRS_Emails![reportname]
    '
     DoCmd.SendObject _
       ObjectType:= acSendReport _
       ObjectName:= zReport _
       OutputFormat:= acFormatPDF _
       To:= zRS![EmailAddress] _
       Subject:= zRS![Subject] _
       MessageText:= zRS![message] _
       EditMessage:= False
     zRS.MoveNext
'I have code here that will show records pending to send.
'Once you use the MoveNext you can get a record count
'and update a textbox on a form, system status bar, etc...
   Loop
Else
   msgbox "There are no clients to send reports"
end if
'
'Error trap
 
'If you open it, close it
zRS.close
'
'of you set it, clear it
if not is nothing zRS_Emails then Set zRS_Emails:= Nothing
if not is nothing zDB then set zDB:= Nothing