why does my code miss out a record? (1 Viewer)

iankerry

iankerry

Registered User.
Local time
Today, 11:33
Joined
Aug 10, 2005
Messages
190
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?


Code: 
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

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:33
Joined
May 7, 2009
Messages
19,233
I think there is no GetDate() function in access, or is it a udf? Yiu should use Now() or Date().
 
iankerry

iankerry

Registered User.
Local time
Today, 11:33
Joined
Aug 10, 2005
Messages
190
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

isladogs

MVP / VIP
Local time
Today, 11:33
Joined
Jan 14, 2017
Messages
18,209
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
Code: 
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

MajP

You've got your good things, and you've got mine.
Local time
Today, 06:33
Joined
May 21, 2018
Messages
8,525
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

iankerry

Registered User.
Local time
Today, 11:33
Joined
Aug 10, 2005
Messages
190
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

MajP

You've got your good things, and you've got mine.
Local time
Today, 06:33
Joined
May 21, 2018
Messages
8,525
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

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:33
Joined
May 7, 2009
Messages
19,233
Datefield should only contain date (w/o time)

Datefield >= Date()
 
MajP

MajP

You've got your good things, and you've got mine.
Local time
Today, 06:33
Joined
May 21, 2018
Messages
8,525
Datefield should only contain date (w/o time)
Click to expand...
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

Cronk

Registered User.
Local time
Today, 20:33
Joined
Jul 4, 2013
Messages
2,771
Indeed but can be achieved with

Code: 
someDateTimeField >= Date()
or maybe for a particular day

Code: 
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

isladogs

MVP / VIP
Local time
Today, 11:33
Joined
Jan 14, 2017
Messages
18,209
Cronk said:
If I was doing it again, I'd probably split the date and time into separate fields.
Click to expand...

Why? Similar to you, I just use:
Code: 
someDateTimeField Between Date() and Date()+1
 
MajP

MajP

You've got your good things, and you've got mine.
Local time
Today, 06:33
Joined
May 21, 2018
Messages
8,525
@Cronk
Yes, I already explained how to do it in Access by simply taking the int of date
WHERE Int([dateTimeField])=Date()
 
You must log in or register to reply here.

Similar threads

iankerry
format a currency field within arrSummaryData
Replies
7
Views
639
iankerry
iankerry
iankerry
turning a variable into Text for an email
Replies
8
Views
1,012
iankerry
iankerry
iankerry
understanding what i am doing so i can solve the issues!
Replies
8
Views
1,058
iankerry
iankerry
iankerry
showing checkbox in an email
Replies
8
Views
1,318
boblarson
boblarson

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom