Hi Guy's, should be able to do this easily but I think when I keep trying and testing, the mind wanders and can't seem to figure this so another set of eyes required please ?
So target is
the procedure is on a yes/no box
select correct box for correct date, driver
update all yes/no fields from No to Yes (particular one I'm trying has 4 records, debug window showing 1) may well be the initial updating / dirty from No to Yes ?
all 4 Yes / No is now yes
Input to ask for Either a delay or earlier arrival
Update all times according to Input box selection
Once working, i am going add 2 more items afterwards
email all with ETAUdjust = Yes
set all ETAAdjust to No
NOTE: Currently Exiting Sub to test record count
Code:
sSQL = "UPDATE tblRemovals SET tblRemovals.ETAAdjust = Yes " _
& "WHERE (((tblRemovals.RemovalDate) = #" & dtDate & "#) " _
& "AND ((tblRemovals.Engineer) Like ""*" & sDriver & "*""))"
DoCmd.SetWarnings False
DoCmd.RunSQL sSQL
DoCmd.SetWarnings True
If Me.Dirty Then Me.Dirty = False
varOpt = InputBox(Chr(149) & " Click Cancel To Abort These Options " & Chr(49) & vbNewLine & vbNewLine & _
Chr(149) & " 1 " & "Inform Of Delays " & Chr(149) & vbNewLine & vbNewLine & _
Chr(149) & " 2 " & "Inform Of Earlier Arrival " & Chr(149), "CHANGE TIMINGS")
Select Case varOpt
Case Is = ""
sSQL = "UPDATE tblRemovals SET tblRemovals.ETAAdjust = No"
DoCmd.SetWarnings False
DoCmd.RunSQL sSQL
DoCmd.SetWarnings True
Case Is = 1
Set rs = CurrentDb.OpenRecordset("Select * From tblRemovals WHERE RemovalDate = #" & dtDate & "# And Engineer Like ""*" & sDriver & "*"" And ETAAdjust = Yes")
Debug.Print sDriver & " ; " & dtDate & vbCrLf & "Records = " & rs.RecordCount
Exit Sub
iETA = InputBox("Add How Much Delay in Minutes ?", "ENTER DELAY")
dtETA1 = Format(DateAdd("n", iETA, rs.Fields("ETA")), "Long Time")
dtETA2 = Format(DateAdd("n", iETA, rs.Fields("ETA2")), "Long Time")
Do While Not rs.EOF
sSQL2 = "UPDATE tblRemovals SET tblRemovals.ETA = #" & dtETA1 & "#, tblRemovals.ETA2 = #" & dtETA2 & "# " _
& "WHERE (((tblRemovals.RemovalDate)= #" & dtDate & "#) " _
& "AND ((tblRemovals.Engineer) Like ""*" & sDriver & "*"") " _
& "AND ((tblRemovals.ETAAdjust)=Yes));"
rs.MoveNext
Loop
DoCmd.RunSQL sSQL2
Case Is = 2
Set rs = CurrentDb.OpenRecordset("Select * From tblRemovals WHERE RemovalDate = #" & dtDate & "# And Engineer Like ""*" & sDriver & "*"" And ETAAdjust = Yes")
iETA = InputBox("Add How Much Earlier in Minutes ?", "ENTER EARLIER TIME")
dtETA1 = Format(DateAdd("n", -iETA, rs.Fields("ETA")), "Long Time")
dtETA2 = Format(DateAdd("n", -iETA, rs.Fields("ETA2")), "Long Time")
Do While Not rs.EOF
sSQL2 = "UPDATE tblRemovals SET tblRemovals.ETA = #" & dtETA1 & "#, tblRemovals.ETA2 = #" & dtETA2 & "# " _
& "WHERE (((tblRemovals.RemovalDate)= #" & dtDate & "#) " _
& "AND ((tblRemovals.Engineer) Like ""*" & sDriver & "*"") " _
& "AND ((tblRemovals.ETAAdjust)=Yes));"
rs.MoveNext
Loop
DoCmd.RunSQL sSQL2
End Select