Updating Times With Criteria's

[DMT Dave]

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

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

[DMT Dave]

Durrrr no wonder not getting 4 records, debug.Print after loop not before!!!!!

now got 4 records

Will come back to it but any better suggestions to what i have

Case Is = 1    Set rs = CurrentDb.OpenRecordset("Select * From tblRemovals WHERE RemovalDate = #" & dtDate & "# And Engineer Like ""*" & sDriver & "*"" And ETAAdjust = Yes")
    
    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
            
    Debug.Print sDriver & " ; " & dtDate & vbCrLf & "Records = " & rs.RecordCount
    Exit Sub

[Minty]

I am not a fan of inputboxes, there is no formatting or validation available, except after the event.

I would put the input data onto a form so you can check for sensible inputs, before processing it.

[DMT Dave]

Thanks Minty, do you think this would be a better structure to carry out the procedure ?

[Minty]

If it removes all elements of doubt/user entry errors, then yes.

I would tend to label the controls in some way to make it really obvious what they were doing, but you know your users and how they interact with the process.

Maybe use a + and - symbols instead of the arrows? And why not display it as 10 Mins 40 Mins etc? That remove any possible confusion.

[DMT Dave]

yes i like your approach, will do some adapting, i think my procedures were perhaps wrong along with method, i should map it out

such as:

1 change timing
2 select check boxes for required timing changes
3 Update the recordset with checkbox = yes
4 generate the desired mail body
5 Open mails to check and send
6 clear all check boxes (all records)

agree ?

and thank you

[Welshgasman]

Why not just have a combo box that goes from 1 to 10, or 0 to 10 ?

You and loops do not get on, do you?

[DMT Dave]

Hi WGM, yes another good approach, Loops are my friend lol just writing things the wrong way then realizing what i should be doing prior, within and after loops

May i tell you, i do use debug a lot now as you can easily find empty strings or spelling/writing faults, i can't remember who kept telling me that debug is your best friend, can you remember WGM WGM WGM WGM WGM