Report Display Data linked to two separate entries

[ZJGMoparman]

I am trying to create a customized database for our NFL Football Pool. Each week a pick sheet will be sent out to everyone in the pool in PDF format. I have several things I would like to do with this report:



1. Is it possible to pull the team logo from my "tblTeams" table and display beside each team on the report. I can get either the home team logo or the visitor team logo, but I cannot get both.

2. Is it possible to export this report in a PDF format and make the empty box shown in the report "clickable." In other words, when the user opens the report, they can click on the box of their pick, and an "X" will be placed in the box.

3. Is it possible to send this report (in PDF format) to each user's email that is participating in the pool. The "tblParticipants" table stores an email address for each person participating.



I am attaching the database I have so far, as well as the report saved in a PDF format.

rptWeeklySchedule.pdf

NFL.zip

[ranman256]

1 yes, in your tblTeams, make a field of OLE type. Paste your icon in the field. (i use this)

2. no, once in pdf format nobody can change it. Instead of a report, I would send the data as XLS and let users mark an X in the column
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, vQry, vFile, True, "Football"

3. yes, in a list box show the users emails , put a button on the form, when clicked it will scan the list and send each 1 an email


The btnSend_click will then scan the list of people and send them the report.

sub btnSend_Click()
vQry = "qsFootballData"
vFile = "c:\folder\football.xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, vQry, vFile, True, "football"


  'scan items in listbox
For i = 0 To lstClients.ListCount - 1
   vTo = lstClients.ItemData(i)
   lstClients = vTo
        
    Call Email1(vTo,"subject","body", "c:\folder\football.xls"


      ' but to send the pdf rpt:
    'DoCmd.SendObject acSendReport, "rReport", acFormatPDF, lstPerson , , "subject", "msg body"
next
end sub

'here is the email part. You must load OUTLOOK in the tools, references

Public Function Email1(ByVal pvTo, ByVal pvSubj, ByVal pvBody, optional ByVal pvFile) As Boolean
Dim oApp As Outlook.Application
Dim oMail As Outlook.MailItem


On Error GoTo ErrMail


    'NOTE  BE SURE YOU ADD OUTLOOK APP VIA  VBE menu:TOOLS, REFERENCES


Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(olMailItem)


With oMail
    .To = pvTo
    .Subject = pvSubj
    .Body = pvBody
    
    if not isempty(pvFile) then  .Attachments.Add pvFile, olByValue, 1
    .Send
End With


Email1 = True
endIt:
Set oMail = Nothing
Set oApp = Nothing
Exit Function


ErrMail:
MsgBox Err.Description, vbCritical, Err
Resume endit
End Function

[ZJGMoparman]

1 yes, in your tblTeams, make a field of OLE type. Paste your icon in the field. (i use this)

2. no, once in pdf format nobody can change it. Instead of a report, I would send the data as XLS and let users mark an X in the column
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, vQry, vFile, True, "Football"

3. yes, in a list box show the users emails , put a button on the form, when clicked it will scan the list and send each 1 an email


The btnSend_click will then scan the list of people and send them the report.

sub btnSend_Click()
vQry = "qsFootballData"
vFile = "c:\folder\football.xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, vQry, vFile, True, "football"


  'scan items in listbox
For i = 0 To lstClients.ListCount - 1
   vTo = lstClients.ItemData(i)
   lstClients = vTo
        
    Call Email1(vTo,"subject","body", "c:\folder\football.xls"


      ' but to send the pdf rpt:
    'DoCmd.SendObject acSendReport, "rReport", acFormatPDF, lstPerson , , "subject", "msg body"
next
end sub

'here is the email part. You must load OUTLOOK in the tools, references

Public Function Email1(ByVal pvTo, ByVal pvSubj, ByVal pvBody, optional ByVal pvFile) As Boolean
Dim oApp As Outlook.Application
Dim oMail As Outlook.MailItem


On Error GoTo ErrMail


    'NOTE  BE SURE YOU ADD OUTLOOK APP VIA  VBE menu:TOOLS, REFERENCES


Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(olMailItem)


With oMail
    .To = pvTo
    .Subject = pvSubj
    .Body = pvBody
    
    if not isempty(pvFile) then  .Attachments.Add pvFile, olByValue, 1
    .Send
End With


Email1 = True
endIt:
Set oMail = Nothing
Set oApp = Nothing
Exit Function


ErrMail:
MsgBox Err.Description, vbCritical, Err
Resume endit
End Function

Thanks for the reply ranman256.

As far as my first question, unless I am missing something, having an OLE field will not fix my issue. I currently have a "logo" field as an attachment. The problem is, when I create the report, it is only pulling one logo instead of the one for both teams. The logo field is linking to only the home team or only the away team. I can't get it to work for both at the same time.

See the attached example. Schedule With Logos.pdf