Can I use VBA for this? Display records based on multiple criteria (1 Viewer)

diberlee

Registered User.
Local time
Today, 09:41
Joined
May 13, 2013
Messages
85
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.

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?

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
 

DJkarl

Registered User.
Local time
Today, 11:41
Joined
Mar 16, 2007
Messages
1,028
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.
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?

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

I think in this case you would be better served by writing a query to filter your conditions rather than looping through a recordset to check each record.

Code:
SELECT * FROM Clients WHERE Priority = True and TimeToCall < Time()

If you only want 100 or less records add the following

Code:
SELECT TOP 100 * FROM Clients WHERE Priority = True and TimeToCall < Time()
 

diberlee

Registered User.
Local time
Today, 09:41
Joined
May 13, 2013
Messages
85
That makes a lot more sense than what I was trying to do. So the query would have some sort of property that tells us how many entries it returns and if it's 0 we just move on to the next query... I'll have a tinker with this in mind when I'm back in work.

I knew all the different pieces of Access would work together somehow, it's just a lot to take in at first.
 

Users who are viewing this thread

Top Bottom