DateAdd with DMax +1, not updating table data

[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.

 

[Pat Hartman]

If records can be selected via criteria using a query, then the loop method is fine. If you are scrolling through a list of records and using some unknown logic to "pick" records, then I would not use a loop, I would generate the appointment as each record is selected. You code will find the next available slot based on the selected date (not day 1 or day 2) and day or evening.

 

[avincent61]

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.

I failed to mention I do have those mouse events in place, although you're still half right. I had it implemented for my ApptDate, but I failed to put it in the ApptTime mouse events. This is likely causing an issue that I couldn't see yet.
Thank you Arnelgp!

 

[avincent61]

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.

Thank you for being so thorough! I am working on your instruction now...I'll check back in.

 

[avincent61]

If records can be selected via criteria using a query, then the loop method is fine. If you are scrolling through a list of records and using some unknown logic to "pick" records, then I would not use a loop, I would generate the appointment as each record is selected. You code will find the next available slot based on the selected date (not day 1 or day 2) and day or evening.

Hi Pat! Thank you for your reply...
So the list of records is based on a query however, the scheduling of appointments happens quarterly, in one shot for that quarter. To give a little more detail: The "appts" are time slots for personnel to arrive for an annual exam. The query pulls personnel based on the date of their previous exam. The total number of personnel is approx. 120, and per quarter approx. 30 personnel are due. The way we are scheduling now is 100% manual (selecting dates and times for each record one at a time ). My goal is to almost completely automate the process by the scheduler selecting the records for the 1st day and clicking the cmd button to enter that date into the ApptDate field for the selected records, then clicking a different button to fill in the ApptTime incrementally across the selected records. After that the scheduler can click Print Appt. Cards and the cards will print with personnel name, appt date, appt time etc. The automated way would take 5 mins, compared to manual at almost 20 minutes (we've timed it lol).

 

[avincent61]

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

Thank you for this Arnelgp...Here's what I have that I failed to mention in my original post. If you see any reason to modify what I'm using, I'm 100% all ears.

Option Compare Database
Option Explicit

Dim MySelTop As Long
Dim MySelHeight As Long

Dim MySelForm As Form
Dim fMouseDown As Integer
Public m_save_selheight As Integer
Dim g_numSelectedRecords As Long

Public Property Get save_selheight() As Integer
    save_selheight = m_save_selheight
End Property

Function SelRecord(F As Form, MouseEvent As String)
   Select Case MouseEvent
      Case "Move"
         ' Store the form and the form's Sel property settings
         ' in the MySel variables ONLY if mouse down has not
         ' occurred.
         If fMouseDown = True Then Exit Function
         Set MySelForm = F
         MySelTop = F.SelTop
         MySelHeight = F.SelHeight

      Case "Down"
         ' Set flag indicating the mouse button has been pushed.
         fMouseDown = True
      Case "Up"
         ' Reset the flag for the next time around.
         fMouseDown = False
   End Select
End Function

Public Sub SelRestore()
Debug.Print "got into Restore"
   ' Restore the form's Sel property settings with the values
   ' stored in the MySel variables.
   MySelForm.SelTop = MySelTop
   MySelForm.SelHeight = MySelHeight

End Sub

 

[Pat Hartman]

If you are using a query to select people, a simple method would be to use what we sometimes call a "tally table". The table would contain 1 record for each time slot for a given day. They would be assigned the numbers 1-30. Your selection query would assign a sequence number 1-30 to the selected people and you simply join your query to the tally table on sequence number and convert that to an append query to append the appointments to a specific date for slots 1-30.

I'm in the middle of something and can't get to this before tomorrow. Someone else familiar with queries can build it in the mean time or I'll check back tomorrow to see if you still need help.

Don't write code when a query will do the job.

PS, I didn't pay a lot of attention to the code but I have no idea why mouse movements would ever be relevant to this process. The button click that runs the process should loop through the recordset of selected records and append records to the schedule table.

 

[avincent61]

If you are using a query to select people, a simple method would be to use what we sometimes call a "tally table". The table would contain 1 record for each time slot for a given day. They would be assigned the numbers 1-30. Your selection query would assign a sequence number 1-30 to the selected people and you simply join your query to the tally table on sequence number and convert that to an append query to append the appointments to a specific date for slots 1-30.

I'm in the middle of something and can't get to this before tomorrow. Someone else familiar with queries can build it in the mean time or I'll check back tomorrow to see if you still need help.

Don't write code when a query will do the job

I was thinking about something similar to what you've described. Using a completely different table to manage the appointments so I'm not limited to the structure of the table I'm pulling my data from. I will definitely start digging around on the "tally table". Thank you!

 

[Pat Hartman]

Good. I don't know why the structure of the table you are pulling the data from has anything to do with the appointments table. You pull from your employee table and append to the appointments table.

 

[avincent61]

Good. I don't know why the structure of the table you are pulling the data from has anything to do with the appointments table. You pull from your employee table and append to the appointments table.

I admit, I was trying to be cheap and hold the appt times in my source table. Creating new tables is a last resort for me in most instances, so it can take some work to get me there. Lol

 

[Pat Hartman]

I admit, I was trying to be cheap and hold the appt times in my source table.

And what were you going to do next year?