Date Issues

[DMT Dave]

Hi Guy's this one is bugging me why records between 2 dates appear on a different date period but there are still only records added between dates on screen ????

Please note, any names and vehicles are rubbed out on here, below is

Code For initial adding

Form Datasheet Criteria Code

Form Datasheet results for 12/07/21 to 18/07/21

Scroll back 2 weeks there results still appear scroll back to 28/06/21 to 04/07/21 ehhhh!!!!!

Code on scroll back button "<"

Initial Adding (taken From lstMS in Snip)

Dim rs As DAO.Recordset, rs2 As DAO.Recordset, rsEdit As DAO.Recordset, myDriver As String, myStart As Date, myEnd As Date, i As Integer, RecQty As Integer, optRecs As Integer, TotRecs As IntegerDim rsMS As DAO.Recordset, whDate As Date


If IsNull(Me.cboDriver) Then
MsgBox ("You Need To Select A Driver To Add Time Sheets"), vbInformation + vbOKOnly, "NO DRIVER SELECTED"
DoCmd.CancelEvent
Else


myDriver = Me.cboDriver


Me.lstDels.RowSource = "SELECT tblAssign.DeliveryDate, tblAssign.Vehicle, tblAssign.Driver, tblAssign.DelNo, tblAssign.DelTo, tblAssign.Town, tblAssign.PostCode From tblAssign GROUP BY tblAssign.DeliveryDate, tblAssign.Vehicle, tblAssign.Driver, tblAssign.DelNo, tblAssign.DelTo, tblAssign.Town, tblAssign.PostCode HAVING (((tblAssign.DeliveryDate) Between [Forms]![frmDriverHours]![txtStartDate] And [Forms]![frmDriverHours]![txtEndDate]) AND ((tblAssign.Driver)=[Forms]![frmDriverHours]![cboDriver])) ORDER BY tblAssign.DeliveryDate DESC , tblAssign.Driver;"
Me.lstDels.Requery


Forms!frmDriverHours.frmMileageSheetDS.Form.RecordSource = "SELECT tblJobSheets.DelDate, tblJobSheets.Vehicle, tblJobSheets.Driver, tblJobSheets.Del1, tblJobSheets.Del2, tblJobSheets.Del3, tblJobSheets.Del4, tblJobSheets.Del5, tblJobSheets.Del6, tblJobSheets.Del7, tblJobSheets.Del8, tblJobSheets.Del9, tblJobSheets.Del10, tblJobSheets.Del11, tblJobSheets.Del12, tblJobSheets.Del13, tblJobSheets.Del14, tblJobSheets.Del15 From tblJobSheets WHERE (((tblJobSheets.DelDate) Between [Forms]![frmDriverHours]![txtStartDate] And [Forms]![frmDriverHours]![txtEndDate]) AND ((tblJobSheets.Driver)=[Forms]![frmDriverHours]![cboDriver])) ORDER BY tblJobSheets.DelDate DESC;"
Me.frmMileageSheetDS.Requery


Me.lstMS.Requery


myStart = Me.txtStartDate
myEnd = Me.txtEndDate


RecQty = Me.lstMS.ListCount
If RecQty = 0 Then
MsgBox ("There Are No Time Sheets To Add For:" & vbNewLine & vbNewLine & _
Me.cboDriver & vbNewLine & vbNewLine & _
"Between: " & Me.txtStartDate & vbNewLine & vbNewLine & _
Me.txtEndDate & vbNewLine & vbNewLine & _
"Perhaps " & Me.cboDriver & " Was Absent Within These Dates!"), vbInformation + vbOKOnly, "NO RECORDS TO ADD"
DoCmd.CancelEvent
Else
If Not IsNull(DLookup("StartDate", "tblDriverHours", "[StartDate] Between #" & myStart & "# And #" & myEnd & "#")) Then
DoCmd.CancelEvent
Else
Set rs = CurrentDb.OpenRecordset("Select * From tblDriverHours")
With rs
For i = 0 To Me.lstMS.ListCount - 1
.AddNew
!Driver = Me.cboDriver
!StartDate = Format(Me.lstMS.Column(0, i), "dd-mmm-yyyy")
!Rate = "9.00"
!MSNo = Me.lstMS.Column(3, i)
!StdHours = "37"
.Update
Next i
End With
End If

Result



Datasheet Record Soucre

Forms!frmDriverHours!frmDriverHoursDS.Form.RecordSource = "SELECT tblDriverHours.Driver, tblDriverHours.StartDate, tblDriverHours.MSNo, " _& "tblDriverHours.StartTime, tblDriverHours.FinishTime, tblDriverHours.HoursDone, tblDriverHours.RestBreak, tblDriverHours.Rate, tblDriverHours.StdHours, " _
& "tblDriverHours.HoursPaid, tblDriverHours.HoursOver, tblDriverHours.HoursBanked, tblDriverHours.Mins, tblDriverHours.HoursLeft, tblDriverHours.UnitHrs " _
& "FROM tblDriverHours " _
& "WHERE (((tblDriverHours.StartDate) Between [Forms]![frmDriverHours]![txtStartDate] And [Forms]![frmDriverHours]![txtEndDate])); "
Me.frmDriverHoursDS.Requery

Go Back 2 weeks and still there after the requery of Forms!DriverHoursDS ?



It's baffling me !!!!!

[Minty]

Debug.print the recordsource of the sub form, after you think you have set it.
By setting it you shouldn't need to requery it, it should just reload automatically?

Personally, I would use Me.SubformControlName.Form.RecordSource to refer to the subform from the main form, just as I know it's the correct instance?

EDIT: Actually if the form controls are your criteria in the sub form recordsource you don't need to do anything, a requery on it's own should do?

[Gicu]

Hi Dave,
Why would you set the record source of the subform in code and not simply use a saved query with your chosen criteria. Then you would need to simply do a requery (Me.frmDriverHoursDS.Form.Requery) in the AfterUpdate of the two date textboxes or where else you change them.
Cheers,

[DMT Dave]

Hi Vlad, and Minty, yes i will adapt to your suggestion

Kindest

[CJ_London]

build your sql string like this, i.e. not including parameters in the query

"WHERE (((tblDriverHours.StartDate) Between #" & [Forms]![frmDriverHours]![txtStartDate] & "# And #" & [Forms]![frmDriverHours]![txtEndDate] & "#")); "

You really need to get into the habit of indenting code - extremely difficult to read otherwise.

You also don't need to keep referencing tblDriverHours in your select part or need so many brackets in your where statement - but guess you have just copy/pasted this from a query

[Micron]

You really need to get into the habit of indenting code - extremely difficult to read otherwise.

I second that notion.
@dmtdave; I cannot see how that can work - must be at least 2 End If's missing, which should be apparent if you indented. On that note, you might dissuade others from attempting to help if they cannot make sense of code that you post. I did not 'format' the long sql, which is also not very attractive to look at but I'd suggest you do that in your code, not just in a post.

If IsNull(Me.cboDriver) Then
   MsgBox ("You Need To Select A Driver To Add Time Sheets"), vbInformation + vbOKOnly, "NO DRIVER SELECTED"
   DoCmd.CancelEvent
Else
   myDriver = Me.cboDriver

   Me.lstDels.RowSource = "SELECT tblAssign.DeliveryDate, tblAssign.Vehicle, tblAssign.Driver, tblAssign.DelNo,..."
   Me.lstDels.Requery

   Forms!frmDriverHours.frmMileageSheetDS.Form.RecordSource = "SELECT tblJobSheets.DelDate, tblJobSheets.Vehicle,..."
   Me.frmMileageSheetDS.Requery
   Me.lstMS.Requery

   myStart = Me.txtStartDate
   myEnd = Me.txtEndDate

   RecQty = Me.lstMS.ListCount
      If RecQty = 0 Then
         MsgBox ("There Are No Time Sheets To Add For:" & vbNewLine & vbNewLine & _
         Me.cboDriver & vbNewLine & vbNewLine & _
         "Between: " & Me.txtStartDate & vbNewLine & vbNewLine & _
         Me.txtEndDate & vbNewLine & vbNewLine & _
         "Perhaps " & Me.cboDriver & " Was Absent Within These Dates!"), vbInformation + vbOKOnly, "NO RECORDS TO ADD"
         DoCmd.CancelEvent
      Else
         If Not IsNull(DLookup("StartDate", "tblDriverHours", "[StartDate] Between #" & myStart & "# And #" & myEnd & "#")) Then
            DoCmd.CancelEvent
         Else
            Set rs = CurrentDb.OpenRecordset("Select * From tblDriverHours")
            With rs
               For i = 0 To Me.lstMS.ListCount - 1
                  .AddNew
                  !Driver = Me.cboDriver
                  !StartDate = Format(Me.lstMS.Column(0, i), "dd-mmm-yyyy")
                  !Rate = "9.00"
                  !MSNo = Me.lstMS.Column(3, i)
                  !StdHours = "37"
                  .Update
               Next i
            End With
         End If
      End If
End If

[moke123]

You really need to get into the habit of indenting code - extremely difficult to read otherwise.

Dave,
Here's a link to Smart Indenter, a free add on to indent code. It works on all versions of access.

http://www.oaltd.co.uk/Indenter/Default.htm

[DMT Dave]

Thank you guy's for your help, i have been dragged away from this and coming back to it soon as poss

Will go though what you guy's have written, thank you