why does my code miss out a record?

[iankerry]

Hi All, have been scratching more than my head about this one for sometime. hope i can explain it properly.

i have a database with 1000 records on it. on a form i will have a filter on which shows me all the events coming up for one venue.

i have a button which if pressed generates an email, with a list of all the bookings for that venue, so i can send it by way of confirmation. This has worked well for years!

but now for some reason, the code misses out one record...e.g. it generates an email without one of the bookings for that venue in it.

I turned the SQL statement in the code into a query in Access and in the SQL server, and they both include the missing record. What on earth can be going wrong?

Private Sub cmdEmail_Click()

  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 strposter As String
  Dim curAdultTP As Currency
  
'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 [poster notes], EventsFlicks.cluster, EventsFlicks.specialevents, EventsFlicks.datefield, Films.[film name], EventsFlicks.owndvd, EventsFlicks.time, EventsFlicks.AdultTP, EventsFlicks.FamilyTP, EventsFlicks.ChildTP, EventsFlicks.other1TP,Venues.VENUE, Promoters.NAME,Promoters.email, Promoters.ID" & _
" FROM (Films INNER JOIN filmCopies ON Films.ID = filmCopies.tblFilms_ID) INNER JOIN (Venues INNER JOIN (Promoters INNER JOIN EventsFlicks ON Promoters.ID = EventsFlicks.promoterID) ON Venues.ID = EventsFlicks.venueID) ON filmCopies.ID = EventsFlicks.filmCopyID " & _
" WHERE (((EventsFlicks.datefield) > GetDate()) And ((Promoters.ID) = " & lngPromoterId & "))" & _
" ORDER BY 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 arrSummaryData(intRecordCount).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])
                     ' If specialevents = Null Then
                     ' specialevents = "not applicable"
                     ' Else
                      arrSummaryData(intRecordCount).StrSpEvent = IIf(IsNull(![specialevents]), "", ![specialevents])
                     ' End If
                      
                      
                      
                      arrSummaryData(intRecordCount).strposter = IIf(IsNull(![poster notes]), "", ![poster notes])


                     .MoveNext
                     intRecordCount = intRecordCount + 1
                 Loop
                .Close
            End With

        Dim strmessage As String

        strmessage = "Dear " & strPromoterName & "," & vbCrLf & vbCrLf & "Below are your requested bookings - please check ALL the details:" & vbCrLf

I hope that is understandable. Forgive me i am not a great programmer and have built this system up over many years!

thanks for any clues.

ian

 

[arnelgp]

I think there is no GetDate() function in access, or is it a udf? Yiu should use Now() or Date().

 

[iankerry]

Hi Thanks for replying.

i took out the getdate WHERE clause, in case. It created an email with hundreds of dates in for that venue but that one record was still missing. the code skips over that date/record (1st july 2018)

having googled getdate, i can see anyway that it shouldn't work in access, but curious thing it does.... but that is another story.

Ian

 

[isladogs]

As a test, try replacing (EventsFlicks.datefield) > Date() with (EventsFlicks.datefield) = #" & 1/7/2018 & "#

Paste the SQL into a query & run it. Do you get a result for that venue?

If yes, then place a break point on the line

arrSummaryData(intRecordCount).strPromoterName = IIf(IsNull(![NAME]), "", ![NAME])

and run the code.
Does it trigger the break point or bypass it?
If the former, add more breakpoints or step through line by line

I would also suggest adding error handling to the code and see if it breaks on errors

 

[MajP]

Often when you miss a record/s is that you are comparing a date that has 0 time to the same date with some time. You may be unaware of the time portion so you either want to convert it to a mm/dd/yyyy string format or take the int([thedate])

 

[iankerry]

Thanks both for replying.

Strange how these things work... you comment on getdate() made me think -

given the command does work, then it must be talking directly to the sql server.

sure enough when i searched for a OpenConnection command, i found a connection to an old server.

once i amended this, pointing it to the new server, it all worked again.

thanks so much guys for spending the time replying.

ian

 

[MajP]

One thing you can type this
arrSummaryData(intRecordCount).strPromoterName = IIf(IsNull(![NAME]), "", ![NAME])
a little easier using null to zero function
arrSummaryData(intRecordCount).strPromoterName = nzl(![NAME]), "")
The second parameter can be whatever you want not just "".

 

[arnelgp]

Datefield should only contain date (w/o time)

Datefield >= Date()

 

[MajP]

Datefield should only contain date (w/o time)

Not a problem if running a sql server backend, but a common problem I see in Access where you only have date field which is date time. Often people will have the field formatted to a date format and due to how they enter, import, or calculate the field they inadvertently have a time portion but not seen. Then they cannot figure out how
someDateTimeField = Date()
returns no record.

 

[Cronk]

Indeed but can be achieved with

someDateTimeField >= Date()

or maybe for a particular day

someDateTimeField >= Date() and someDateTimeField <Date()+1

One of my early developments was for a local area government agency undertaking dog control. I made provision for recording the time of impoundment of stray dogs in a date/time field and had to use the latter code in showing dogs impounded on a particular day.


If I was doing it again, I'd probably split the date and time into separate fields.

 

[isladogs]

If I was doing it again, I'd probably split the date and time into separate fields.

Why? Similar to you, I just use:

someDateTimeField Between Date() and Date()+1

 

[MajP]

@Cronk
Yes, I already explained how to do it in Access by simply taking the int of date
WHERE Int([dateTimeField])=Date()