Hi,
I recently volunteered to set up a database to be used by my employer to handle calling clients who have booked an appointment. We book these clients in advance to be called within a 2 hour window, but do not give a specific time in most cases. The database will have a form where a user clicks a button and is given the details of the next client to call, they will then select a result and click the button again to move on to the next entry.
It's not quite as simple as that though, some clients are given priority and would be called ahead of any other, and others are not available until after a certain time (stored in "TimeToCall") so we don't want to waste time trying to call them earlier. If a client is not contacted successfully a field named "LastAttempt" will be updated with the time we tried them, this field will be used later.
I'm learning VBA for Access, I'm fairly well versed in VBA for Excel but didn't realise how different the 2 would be... I just want to check I'm on the right track. I plan to use an unbound form with VBA code doing the following when "Next" is clicked.
So far I have this:
Which selects the correct records for what I have entered so far so seems a good start. I'm not on the chapter of my book that deals with passing info to forms yet, so just wanted to make sure that I'll be able to do what I'm planning, the way I plan to do it. If not, any suggestions on how to go about this would be appreciated.
Cheers
Diberlee
I recently volunteered to set up a database to be used by my employer to handle calling clients who have booked an appointment. We book these clients in advance to be called within a 2 hour window, but do not give a specific time in most cases. The database will have a form where a user clicks a button and is given the details of the next client to call, they will then select a result and click the button again to move on to the next entry.
It's not quite as simple as that though, some clients are given priority and would be called ahead of any other, and others are not available until after a certain time (stored in "TimeToCall") so we don't want to waste time trying to call them earlier. If a client is not contacted successfully a field named "LastAttempt" will be updated with the time we tried them, this field will be used later.
I'm learning VBA for Access, I'm fairly well versed in VBA for Excel but didn't realise how different the 2 would be... I just want to check I'm on the right track. I plan to use an unbound form with VBA code doing the following when "Next" is clicked.
Check for priority clients where LastAttempt is empty or more than 15 mins ago and if they exist pass details to the form.
If no records satisfy the above then look for timed clients where TimeToCall has passed, and LastAttempt is empty or more than 15 mins ago.
If no records satisfy the above select the next entry that is not a priority or timed client, and lastAttempt is empty or more than 15 mins ago.
Continue looping through the entries until we either contact all clients, or run out of time and mark clients as "unable to contact" or similar.
Is doing this VBA a good way to go, or should I be using queries/filters or a combination of the 3?If no records satisfy the above then look for timed clients where TimeToCall has passed, and LastAttempt is empty or more than 15 mins ago.
If no records satisfy the above select the next entry that is not a priority or timed client, and lastAttempt is empty or more than 15 mins ago.
Continue looping through the entries until we either contact all clients, or run out of time and mark clients as "unable to contact" or similar.
So far I have this:
Code:
Sub next_record()
Const lngMaxRecords = 100
Dim lngCount As Long
Dim rc As Long
Dim db As Database
Dim rst As Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT * FROM Clients", dbOpenDynaset)
If Not rst.EOF Then
rst.MoveLast
rc = rst.RecordCount
rst.MoveFirst
'Select priority clients first. Priority flag set and timeToCall is not in the future
For lngCount = 1 To rc
If (rst!Priority) = True And (rst!TimeToCall) < Time() Then
Debug.Print rst!ClientName & " has priority"
End If
If lngCount > lngMaxRecords Then
Exit For
End If
rst.MoveNext
Next
End If
rst.Close
Set rst = Nothing
db.Close
Set db = Nothing
End Sub
Which selects the correct records for what I have entered so far so seems a good start. I'm not on the chapter of my book that deals with passing info to forms yet, so just wanted to make sure that I'll be able to do what I'm planning, the way I plan to do it. If not, any suggestions on how to go about this would be appreciated.
Cheers
Diberlee