Updating Records Where Driver Not Already Assigned

[Welshgasman]

Why are you storing the dayname, when you can get that from the date?

[Welshgasman]

I want YOU to work out why you have an & in your string.
Simple error, but you need to understand concatenation, just as you have to understand loops.

[DMT Dave]

WGM, no I can't see where i shouldn't have an extra & in the string

#" & Format(dtThur, "yyyy/mm/dd") & "#, opening and closing Date tags
'" & sThur & "' Opening and closing string delimiter
'" & strTask & " Opening and closing string delimiter
#" & Format(dtThur, "yyyy/mm/dd") & "# opening and closing Date tags
<> & '" & sDriver & "' Opening and closing string delimiter

I need to go to spec savers, i can't see an extra & in there outside of criteria's

please correct me ?

[DMT Dave]

very sorry WGM, Micron, no i didn't see that extra ampersand apologies, does explain missing operator

I just couldn't see it

spec savers here i go

again sorry


#" & Format(dtThur, "yyyy/mm/dd") & "#, opening and closing Date tags
'" & sThur & "' Opening and closing string delimiter
'" & strTask & " Opening and closing string delimiter
#" & Format(dtThur, "yyyy/mm/dd") & "# opening and closing Date tags
<> & '" & sDriver & "' Opening and closing string delimiter

[Welshgasman]

The fact that you had the & inside the string that is output, should tell you all you need to know.

[DMT Dave]

Thanks guys, this does what is required, sometimes a different set of eyes are needed to see what others can't

Dim dtMon As Date, dtFri As Date, dtDayMon As Date, dtDayFri As Date, dtNow As Date, dtThur As DateDim sSQL As String, sSQL2 As String, sMon As String, sThur As String, sFri As String, strMonTask As String, strTask As String
Dim sSQL3 As String, sStart1 As String, sStart2 As String, sSQL4 As String, sSQL5 As String, sSQL6 As String
Dim sSQL2a As String, sSQLf As String, sDriver As String
Dim rs As DAO.Recordset, rs2 As DAO.Recordset




dtNow = Format(Now(), "dd/mm/yyyy")


If Weekday(Now()) = 6 Then
    dtFri = 13 - Weekday(Date) + Date
    dtMon = 9 - Weekday(Date) + Date
    dtThur = 12 - Weekday(Date) + Date
Else
    dtFri = 6 - Weekday(Date) + Date
    dtMon = 2 - Weekday(Date) + Date
    dtThur = 5 - Weekday(Date) + Date
End If


sThur = WeekdayName(Weekday(dtThur, 0), False, 0)


strTask = "Warehouse Cover"


If Not IsNull(DLookup("DelDate", "tblRoutes", "[DelDate] = #" & dtThur & "#")) Then
    Set rs = CurrentDb.OpenRecordset("Select * From tblRoutes " _
        & "WHERE DelDate = #" & dtThur & "#")
    
    Do Until rs.EOF
        sDriver = rs.Fields("Driver")
        
        rs.MoveNext
    Loop
End If


    sSQL = "INSERT INTO tblRoutes ( Driver ) " _
        & "SELECT tblStaff.Name " _
        & "From tblStaff " _
        & "WHERE (((tblStaff.Name) <> '" & sDriver & "' And ((tblStaff.DelsMail) = Yes)));"
        
DoCmd.SetWarnings False
    DoCmd.RunSQL sSQL
DoCmd.SetWarnings True


Set rs2 = CurrentDb.OpenRecordset("Select * From tblRoutes WHERE DelNo = 0")
If Not (rs2.EOF And rs2.BOF) Then
    rs2.MoveFirst
    Do Until rs2.EOF = True
        rs2.Edit
        rs2!DelDate = dtThur
        rs2!DayName = sThur
        rs2!DelTo = strTask
        rs2.Update
        rs2.MoveNext
    Loop
    Debug.Print rs2.RecordCount
Else
    MsgBox "There are no records in the recordset."
End If
rs2.Close
Set rs2 = Nothing


Forms!frmPlanning!frmPlanningDS.Requery

Removed updates from the loop because if 3 drivers are available it will add 9 records, understanding that I only need 1 record per person available.

Thanks again. much appreciated

[Welshgasman]

Well that is another way to do it.

Are you sure you will have the correct record(s) for the update?

Personally I just use
Do Until rs2.EOF

as when you open an empty recordset, both EOF and BOF are True
Also I have never known on the record pointer being on any other record that the first when you open a recordset and do not move the record pointer yourself.

If that was me, I would not let that & beat me. I would enlarge the font and build it bit by bit again. You really need to understand your mistake, else you will likely repeat it time after time.

Again having the & inside your string should be the clue?

[DMT Dave]

ahh WGM, I think understood about the Do Until rs2.EOF, removing the true would only update records within criteria's whereas True would be all records ?

Thanks again

[Welshgasman]

No, do until rs.eof is the same as do until rs.eof = true
I was talking about the if not rs.bof and not rs.eof, move first. You would already be on the first record if any exist when you open the recordset.