Why are you storing the dayname, when you can get that from the date?
Why are you storing the dayname, when you can get that from the date?
Please use # icon on toolbar when posting code snippets.
Cross Posting: https://www.excelguru.ca/content.php?184
Debugging Access: https://www.youtube.com/results?sear...bug+access+vba
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.
Please use # icon on toolbar when posting code snippets.
Cross Posting: https://www.excelguru.ca/content.php?184
Debugging Access: https://www.youtube.com/results?sear...bug+access+vba
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 ?
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
The fact that you had the & inside the string that is output, should tell you all you need to know.
Please use # icon on toolbar when posting code snippets.
Cross Posting: https://www.excelguru.ca/content.php?184
Debugging Access: https://www.youtube.com/results?sear...bug+access+vba
Thanks guys, this does what is required, sometimes a different set of eyes are needed to see what others can't
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.Code: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
Thanks again. much appreciated
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?
Please use # icon on toolbar when posting code snippets.
Cross Posting: https://www.excelguru.ca/content.php?184
Debugging Access: https://www.youtube.com/results?sear...bug+access+vba
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
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.
Please use # icon on toolbar when posting code snippets.
Cross Posting: https://www.excelguru.ca/content.php?184
Debugging Access: https://www.youtube.com/results?sear...bug+access+vba