DateAdd with DMax +1, not updating table data (1 Viewer)

[avincent61]

Hello! Looking for help so I can stop banging my head into the wall...
My newest headache: I'm using a subform to mass schedule appointments at 10 minute intervals. The loop function I have works for applying dates to records the user selects in the subform (continuous), so I used the same loop code with changes for the times, but I can't seem to get it right.

In my attempts I made an independent table tblApptTimes with the TimeSlots and TimeDesc, which works until I use DayShift (TimeDesc) as the criteria in the DMax function. DayShift is a short text field is adjacent to the TimeSlot used to kick off the 10 minute intervals. I think the issue is that tblApptTimes is not in the Main form or the subform, but I honestly don't know.

Ultimate Goal: User selects multiple records (in a row) displayed on subform (record selectors), User clicks a cmd button (Day 1 Times/Day 2 Times), and the appt times for those records are updated at 10 minute intervals (7:10, 7:20, 7:30, etc.). The start time is held in tblApptTimes (7:00). The screenshot shows how the form is oriented, though it's not finished.

ApptTime and TimeSlot are Date/Time fields set to MediumTime

My code is below, which is in a separate module. Some of my attempts (RS.Fields) are commented out to show trial/error. The most recent code give Run-Time Error 2471 referencing 'DayShift'.

Function SetTimesDay1()
    Dim i As Long
    Dim F As Form
    Dim RS As Recordset
    Dim formRecords As DAO.Recordset


    ' Get the form and its recordset.
    Set F = Forms![frmEyeExams]![sfrmEyeApptCards].[Form]
    Set RS = F.RecordsetClone

    ' Move to the first record in the recordset.
    RS.MoveFirst

    ' Move to the first selected record.
    RS.Move F.SelTop - 1

    ' Enumerate the list of selected records
    For i = 1 To F.SelHeight

    RS.Edit
    RS.Fields("ApptTime") = DateAdd("n", 10, DMax("TimeSlot", "tblApptTimes") + 1)
' '   RS.Fields("ApptTime") = DateAdd("n", 10, DMax("TimeSlot", "tblApptTimes", "[TimeDesc]" = "DayShift") + 1)
' '   RS.Fields("ApptTime") = DateAdd("n", 10, (DMax("TimeSlot", "tblApptTimes", "[TimeDesc]='" & "DayShift" & "'") + 1))
' '   RS.Fields("ApptTime") = DateAdd("n", 10, Forms![frmEyeExams]![sfrmEyeApptCards].[Form].txtApptTime)
    RS.Update
    RS.MoveNext
Next i

End Function

 

[The_Doc_Man]

Hello! Looking for help so I can stop banging my head into the wall...

First, invest in a good bicycle or ice-hockey helmet.

Second, Error 2471 is a semi-generic error that says a query (implied or explicit) produced an unexpected result. This is usually because of a bad field or parameter that is part of or fed to the query. Note that all Domain Aggregate functions implicitly create a query for you in the background.

I would use this technique for debugging: When you are ready to test this, put a breakpoint in the first executable line ("Set F = ..." in your example) and then use the F8 key to single-step through that pile. Since you don't appear to have any user-defined functions in that particular routine, you should execute one line at a time, effectively. Hope that loop doesn't have TOO many records to single-step for debugging.

OR since you don't have an error handler, you can also use the Tools >> Options >> General and look for Error Trapping. Set it to take a breakpoint on "Unhandled Errors" and then don't try to set a breakpoint of your own. Just let it run in a way that eventually hits the error. An important consideration is to learn which instruction is called out. Ad second important matter is to find which record throws the error - First? Last? Somewhere in the middle?

While in that breakpoint, open up the Immediate Window. From there you can use this command "Debug.Print" to examine things... anything currently visible to Access by way of the so-called "scope" rules. For instance, let's say it breaks on the line AFTER the "RS.Edit" line. Things you could examine using Debug.Print to see what is in your loop counter I or F.SelHeight or what is the value resulting from DMax("TimeSlot", "tblApptTimes") - or whatever else you want to examine at that time.

The trick is that of the things you can examine, you have to determine which one isn't the value it should be. Then work backwards to determine HOW it got to that value.

 

[MarkK]

You are trying to do too much all at once. Create a stand-alone selection interface based around a listbox. Then run the update process using that selection. Then present the results. Rolling it all into one like you are trying to do, because you think it will be less work, is having the opposite effect.
hth

 

[arnelgp]

i dont think your current code will work.
first of all once you clicked the button that will generate the "timeslot",
the subform will loose it's focus, therefore all selection is erased.

you need to add code to the subForm's MouseMove event to capture
those records (you must have PK, autonumber) PK numbers.

 

[arnelgp]

see this demo.
open frmEyeExams form.
see the code on the MouseMove event of the subform.
see the code of the button.