Problem finding correct record in recordset

[Sprocket]

Hi Everyone,

I have a problem finding the last record in a recordset, which I am realy having problems getting my head around.

I have a form - Client with a subform to book further appointments.
On the main form is a combo box to select the client - this populates the subform which shows all previous appointments and whether they were kept or not.

I am trying to set up some code to check if the client kept the last appointment and if not to pop up a message box warning of this before another appointment is booked

See form


This is the code I am using

Option Compare Database
Dim dbs As DAO.Database 'Database object
Dim rst As DAO.Recordset 'Recordset object
Dim currentClientID As Integer 'Reference for looking up the last value
Dim hasMissed As Boolean 'Has the client missed an appointment?
Dim e As DAO.Field 'Reference the fieldnames on the subform.
Dim response As VbMsgBoxResult


'Rationale. There's probably an easier way of doing this -- I've yet to find it. Essentially
'this works like the following:
'
' 1. When you select the person from the combobox, the subform updates with the correct
' information.
' 2. When that has been updated, the subform is the looked at, and the clientID that is shown (it
' doesn 't matter which number we pick from which field, they're all the same) and is then stored
' in the variable 'currentClientID'
' 3. A recordset is then established. There is no otherway of doing it. What this means is that we can
' take each record in turn and match the clientID we stored earlier in 'currentClientID' to the same
' values (hopefully) stored in the "Appointment" table.
' 4. If any values match, the recordset is then queried further to match the "AppKept" field. The value
' of either true or false is then assigned to the 'hasMissed' variable.
' 5. Steps 3-4 is continued until all the values in the recordset have been queried.
' 6. Since we're only interested in the last value, we can just look at the value of 'hasMissed'
' directly rather than applying an equality check on it.
' 7. If it is true that the client missed an appointment, popup a message saying so.
' 8. Done.

'general function (useful if you need to repeatedly call this from other "Events" via a form).
Private Sub check_app()

hasMissed = False ' was set to True but this caused form to fail on load

Set dbs = CurrentDb 'Current DB
Set rst = dbs.OpenRecordset("Appointments", dbOpenDynaset) 'The information!
rst.Sort = "DateFor DES, TimeFor DES"

' An error is produced if a new record is created -- this if block
' ensures that we just exit the sub if this is so.

If IsNull(Form.Controls("ClientId").Value) Then
Exit Sub
End If

'Obtain the clientID based on a value selected in the combo box.
currentClientID = Form.Controls("ClientId").Value

'move to the start of the records.
rst.MoveFirst
'While we're not at the end of the recordset...
Do While Not rst.EOF

'Take each fieldname from the "Appointment table"
For Each e In rst.Fields

'If we find a match between the clientID obtained (step 3, above)
If rst.Fields("ClientID").Value = currentClientID Then

'See step 4
If e.Name = "ApptKept" Then
'If there is only one record (a possible new record) then check
'to ensure that the "DateFor" field on the form is blank before
'displaying the error message.


If IsNull(Form.Controls("DateFor").Value) Then
End
Else
If (Form.Controls("ApptKept").Value = False) Then

hasMissed = True

End If
End If
End If
End If
Next

'Move to the next recordset
rst.MoveNext
Loop

'Step 7
If hasMissed = True Then ' was If Not hasMissed but changed this to match line 1
result = MsgBox("Missed last appointment", vbExclamation, "Missed Appointment")
result = ""
End 'finish
End If

'Debugging only.
''''MsgBox ("final = " & d)

End Sub

My problem is that I cannot seem to check just the last appointment.

I am returning a "missed last appointment" for any missed appointment.

How do I check just the last record?

I've tried things like rst.MoveLast but this doesn't work as I believe the order Access stores its markers in isn't the order that you think.

Any ideas welcome.

 

[Pat Hartman]

Eliminate the code and use a query.

Select ClientID, Max(appointmentdate)
From yourtable
Where clientid = Forms!yourform!yourClientID AND ApptKept = false
group by clientID;

 

[Sprocket]

Thanks for the suggestion Pat.

BUT could you kind of point out how/where to use your query instead of code. It kind of looks simple, which is always good but I don't see how to get it to pop up a warning message. By the way it is only if the last appointment was missed not any previous appointment.


I was thinking of setting up an SQL string and using:

srtSQL = "SELECT Client.ClientID, Appointments.DateFor, Appointments.ApptKept
FROM Client LEFT JOIN Appointments ON Client.ClientID = Appointments.ClientID
GROUP BY Client.ClientID, Appointments.DateFor
ORDER BY Appointments.DateFor;"

make this active with:

Set rs = CurrentDb.OpenRecordset(strSQL)

And then perform the Do While loop on this recordset

But this just kind of extends the code, and I'm still not sure if it will sequence in the right order.

Also I think I should have had another line inside the loop, near the top of the loop, to re-set the "hasMissed" variable to False, so that it is only the value of the last record that is passed to step 7

I could just be digging a deeper hole here - so a little bit more guidance please.

Regards.... Sprocket

 

[Sprocket]

Answer found

Dear All,

I put up a post a little while ago - I have finally worked my way through this problem. I thought I would share the result back with the forum in case it is of use to anyone.

Here is the resulting code which now works fine.



' 1. When you select the person from the combobox, the subform updates with the correct information.
' 2. When that has been updated, the subform is the looked at, and the clientID that is shown (it doesn 't matter which number we pick from which field, they're all the same) and is then stored in the variable 'currentClientID'
' 3. A recordset is then established. What this means is that we can take each record in turn and match the clientID we stored earlier in 'currentClientID' to the same values stored in the "Appointment" table.
' 4. If any values match, the recordset is then queried further to match the "AppKept" field.
' The value of either true or false is then assigned to the 'hasMissed' variable.
' 'hasmissed' is then reset to False before testing the next record so we only get a possible TRUE value for the last record in the recordset
' 5. Steps 3-4 ar repeated until all the values in the recordset have been queried.
' 6. Since we're only interested in the last value, we can just look at the value of 'hasMissed' directly rather than applying an equality check on it.
' 7. If it is true that the client missed an appointment. So generate a message saying so.
' 8. Clear the message box - Done.

'general function (useful if you need to repeatedly call this from other "Events" via a form).
Private Sub check_app()

hasMissed = False 'lets be shure we are starting with a clear slate

Set dbs = CurrentDb 'Current DB
Set rst = dbs.OpenRecordset("Appointments", dbOpenDynaset) 'The information!

' An error is produced if a new record is created -- this if block
' ensures that we just exit the sub if this is so.

If IsNull(Form.Controls("ClientId").Value) Then
Exit Sub
End If

'Obtain the clientID based on a value selected in the combo box.
currentClientID = Form.Controls("ClientId").Value

'move to the start of the records.
rst.MoveFirst


'While we're not at the end of the recordset...

Do

'Take each fieldname from the "Appointment table"
For Each e In rst.Fields

'If we find a match between the clientID obtained (step 3, above)
If rst.Fields("ClientID").Value = currentClientID Then

hasMissed = False
'reset this for each pass so only the last pass can be true

If rst.Fields("ApptKept").Value = False Then
hasMissed = True
counter = counter + 1


End If
End If

Next

'Move to the next recordset
rst.MoveNext

Loop While Not rst.EOF


If hasMissed = True Then ' was If Not hasMissed but changed this to match line 1
result = MsgBox("Missed last booked appointment", vbExclamation, "Missed Appointment")
result = ""
End 'finish - this line is needed to stop the message box popping up twice

End If

End Sub