turning a variable into Text for an email

[iankerry]

Hi All,

I have been wrestling with what i think is probably fairly simple!

I use my Events database to create an email which includes all the details of an event.

One variable I have is [owndvd], which is a tick box, and therefore holds a "True" or "False".

To create the email I use (there are obviously lots more lines):

strMessage = strMessage & arrSummaryData(i).strDVD & " " & vbCrLf

Now the email creation works a treat, but I get a True or False in the email, which isn't very user friendly.

So I added these two lines:

    If strDVD = "False" Then
arrSummaryData(intRecordCount).strDVD = "We will provide the DVD for this screening"
    Else
arrSummaryData(intRecordCount).strDVD = "You will provide your own DVD"
     End If

But this doesn't seem to work. Am I making a basic error here?

Full code for this routine here:

Dim arrSummaryData() As SUMMARY_DATA
  Dim SQL As String
  Dim strEmail As String
  Dim strTime As String
  Dim strPromoterName As String
  Dim intRecordCount As Integer
  Dim dblRecord As Double
  Dim i As Integer
  Dim lngPromoterId  As Long
  Dim StrSpEvent As String
  Dim strDVD As String
  
'Dim tmpowndvd1 As String
   If IsNull(Me.PromoterID.Value) Then
      lngPromoterId = 0
   Else
     lngPromoterId = Me.PromoterID.Value
   End If
  
  intRecordCount = 0
  strPromoterName = ""
  strEmail = ""
  SQL = ""

SQL = "SELECT dbo_EventsFlicks.cluster, dbo_EventsFlicks.specialevents, dbo_EventsFlicks.datefield, dbo_Films.[film name], dbo_EventsFlicks.owndvd, dbo_EventsFlicks.time, dbo_EventsFlicks.AdultTP, dbo_EventsFlicks.FamilyTP, dbo_EventsFlicks.ChildTP, dbo_EventsFlicks.other1TP,dbo_Venues.VENUE, dbo_Promoters.NAME,dbo_Promoters.email, dbo_Promoters.ID" & _
" FROM (dbo_Films INNER JOIN dbo_filmCopies ON dbo_Films.ID = dbo_filmCopies.tblFilms_ID) INNER JOIN (dbo_Venues INNER JOIN (dbo_Promoters INNER JOIN dbo_EventsFlicks ON dbo_Promoters.ID = dbo_EventsFlicks.promoterID) ON dbo_Venues.ID = dbo_EventsFlicks.venueID) ON dbo_filmCopies.ID = dbo_EventsFlicks.filmCopyID " & _
" WHERE (((dbo_EventsFlicks.datefield) > Now()) And ((dbo_Promoters.ID) = " & lngPromoterId & "))" & _
" ORDER BY dbo_EventsFlicks.datefield"
  If OpenConnection Then
        If rs.State = adStateOpen Then rs.Close
         rs.Open SQL, cn, adOpenKeyset, adLockOptimistic

         If Not rs.EOF Then
            dblRecord = rs.RecordCount
            ReDim arrSummaryData(1 To dblRecord)
            'create an Array depends on number of records and put all data with features
            '
            With rs
                 intRecordCount = 1
                 Do While Not .EOF
                      
                      arrSummaryData(intRecordCount).strPromoterName = IIf(IsNull(![NAME]), "", ![NAME])
                      strPromoterName = arrSummaryData(intRecordCount).strPromoterName
                      arrSummaryData(intRecordCount).strEmail = IIf(IsNull(![email]), "", ![email])
                      strEmail = arrSummaryData(intRecordCount).strEmail
                      arrSummaryData(intRecordCount).dtDatefield = IIf(IsNull(![datefield]), "", ![datefield])
                      arrSummaryData(intRecordCount).strTime = IIf(IsNull(![time]), "", ![time])
                      arrSummaryData(intRecordCount).strDVD = IIf(IsNull(![owndvd]), "", ![owndvd])
                      If strDVD = "False" Then
                      arrSummaryData(intRecordCount).strDVD = "We will provide the DVD for this screening"
                      Else
                      arrSummaryData(intRecordCount).strDVD = "You will provide your own DVD"
                      End If
                      arrSummaryData(intRecordCount).strFilmName = IIf(IsNull(![film name]), "", ![film name])
                      arrSummaryData(intRecordCount).strVenue = IIf(IsNull(![VENUE]), "", ![VENUE])
                      arrSummaryData(intRecordCount).curAdultTP = IIf(IsNull(![AdultTP]), "", ![AdultTP])
                      arrSummaryData(intRecordCount).curChildTP = IIf(IsNull(![ChildTP]), "", ![ChildTP])
                      arrSummaryData(intRecordCount).curFamilyTP = IIf(IsNull(![FamilyTP]), "", ![FamilyTP])
                      arrSummaryData(intRecordCount).curother1tp = IIf(IsNull(![Other1TP]), "", ![Other1TP])
                      arrSummaryData(intRecordCount).StrSpEvent = IIf(IsNull(![specialevents]), "", ![specialevents])
                     .MoveNext
                     intRecordCount = intRecordCount + 1
                 Loop
                .Close
            End With

        Dim strMessage As String

        strMessage = "Dear " & strPromoterName & "," & vbCrLf & vbCrLf & "Below are your requested bookings.  Please check the details:" & vbCrLf

        If Len(strEmail) = 0 Then
           strEmail = "terri@artsalive.co.uk"
        End If
        
         For i = LBound(arrSummaryData) To UBound(arrSummaryData)
                 strMessage = strMessage & vbCrLf & "Date: " & arrSummaryData(i).dtDatefield & " " & vbCrLf & "Film: " & arrSummaryData(i).strFilmName & " " & vbCrLf & "Time: " & arrSummaryData(i).strTime & " " & vbCrLf & "Venue: " & arrSummaryData(i).strVenue & " " & vbCrLf & "Adult Ticket Price: £" & arrSummaryData(i).curAdultTP & " " & vbCrLf & "Child Ticket Price: £" & arrSummaryData(i).curChildTP & " " & vbCrLf & "Family Ticket Price: £" & arrSummaryData(i).curFamilyTP & " " & vbCrLf & "Other Ticket Price: £" & arrSummaryData(i).curother1tp & " " & vbCrLf & "You are going to provide your own dvd:" & " " & vbCrLf
                 strMessage = strMessage & arrSummaryData(i).strDVD & " " & vbCrLf
                 strMessage = strMessage & "Special Event Info:" & " " & arrSummaryData(i).StrSpEvent & vbCrLf
         Next
         strMessage = strMessage & vbCrLf & "I hope the above booking details are correct, please let me know if you need to make any amendments. Regards" & vbCrLf
    
         Call SendAttachments("Booking information", strMessage, strEmail)
    Else
      MsgBox " There are no records for that Promoter.", vbInformation
    End If
  End If

CleanExit:

    If Not rs Is Nothing Then
       If rs.State = adStateOpen Then rs.Close
    End If
    Exit Sub

many thanks

Ian

 

[pr2-eugin]

Hello Ian, I am sorry I do not have an answer for your question (yet!), but I am very curious as I have never used/seen anyone using Array variables like

arrSummaryData(intRecordCount)[COLOR=Red][B].strPromoterName[/B][/COLOR]

What is the strPromoterName that follows the Array Variable?

 

[iankerry]

Hello Ian, I am sorry I do not have an answer for your question (yet!), but I am very curious as I have never used/seen anyone using Array variables like

arrSummaryData(intRecordCount)[COLOR=Red][B].strPromoterName[/B][/COLOR]

What is the strPromoterName that follows the Array Variable?

I have just been trying to find out why that is there. But I cant seem to remember, all I know is that if I take it out then I don't get the promoters name at the top of the email!

I am still working on this so if I come across the reason will post again.

cheers
ian

 

[iankerry]

Hello Ian, I am sorry I do not have an answer for your question (yet!), but I am very curious as I have never used/seen anyone using Array variables like

arrSummaryData(intRecordCount)[COLOR=Red][B].strPromoterName[/B][/COLOR]

What is the strPromoterName that follows the Array Variable?

I have just been trying to find out why that is there. But I cant seem to remember, all I know is that if I take it out then I don't get the promoters name at the top of the email!

I am still working on this so if I come across the reason will post again.

cheers
ian

 

[iankerry]

Problem Solved!

I used the line:

If strDVD = "False" Then

When I should have used the line:

arrSummaryData(intRecordCount).strDVD

:banghead:

 

[stopher]

Hello Ian, I am sorry I do not have an answer for your question (yet!), but I am very curious as I have never used/seen anyone using Array variables like

arrSummaryData(intRecordCount)[COLOR=Red][B].strPromoterName[/B][/COLOR]

What is the strPromoterName that follows the Array Variable?

The line DIM arrSummaryData() As SUMMARY_DATA suggest it is an array of objects of the class SUMMARY_DATA. So the .strPromoterName is most likely a method or public variable of the class.

The code in the class definition should explain it.

Chris

 

[pr2-eugin]

Thanks stopher.. I just was about to say the same.. I just looked over that declaration..

@iankerry: Glad you have it working, sorry was not really of great help there..

 

[iankerry]

The line DIM arrSummaryData() As SUMMARY_DATA suggest it is an array of objects of the class SUMMARY_DATA. So the .strPromoterName is most likely a method or public variable of the class.

The code in the class definition should explain it.

Chris

Hi Chris

Yes you are indeed correct:

Private Type SUMMARY_DATA
        strPromoterName As String
        strEmail As String
        dtDatefield As Date
        strFilmName As String
        strTime As String
        strVenue As String
        curAdultTP As Currency
        curChildTP As Currency
        curFamilyTP As Currency
        curother1tp As Currency
        StrOwnDvd As String
        intEventID As Integer
        StrSpEvent As String
        strDVD As String

 

[iankerry]

No problem, always good to refresh memory¬