Hi,
I've made a few posts here and got some help to get me this far, I'm looking for a couple of pointers. A quick rundown of what I'm doing:
I've got a few questions if anyone would be so kind...
1/ Is there a better way of checking if the user has saved their record? I'm currently writing to a hidden control and checking the value later. Seems a bit sloppy...
2/ When I identify the next record to be worked on I set a flag (Working) to True to prevent 2 users getting the same record. Is this necessary? If yes is there a better way to do it?
3/ When I start building the code for repeat attempts I will need to check the records for previous contact attempts. What's the easiest way to check for the highest value in a certain field? I plan to store it in a public variable and use it in a FindFirst statement, but welcome any suggestions for a better approach
4/ I know it's good practice to close all databases etc and set to nothing. Do I need to do this in every sub and function, or just the final one before code finishes running?
5/ In Excel you can detect if a user closes the form using the X button. I'm using a pop-up form for this and wondered if there is a similar way to capture the close and run my code for exiting gracefully?
Cheers
Diberlee
I've made a few posts here and got some help to get me this far, I'm looking for a couple of pointers. A quick rundown of what I'm doing:
- I have a table with a list of clients to contact in a 2 hour window.
- Some clients are marked as a priority and should be called first but could appear anywhere on the list.
- Others have requested a call at a certain time and should be ignored until that time.
- Once these priority clients are dealt with all others will be worked through in sequence.
- If contact is unsuccessful the record will be update to show that a contact attempt has been made and a time to try again (10-15 mins most likely) will be entered. These records should be ignored until that time has passed, but then tried again.
- I have an unbound form where a user clicks "Next" to have VBA code run and select the appropriate record to display. User selects a result for the call and record is saved using another button.
- Once finished this will be used by 15-25 people at a time.
Code:
Option Compare Database
Public foundNum As String
Public noShowCutOff As String
[COLOR=green]Private Sub bttnNext_Click()[/COLOR] 'user clicks NEXT - select next client to call
Dim cControl As Control
Dim db As Database
Dim rst As Recordset2
Set db = DBEngine(0)(0)
Set rst = db.OpenRecordset("Clients", dbOpenSnapshot)
'Check if user has not saved a reserved record
If Me.txtSaved = False Then
MsgBox ("Save changes before getting next")
Exit Sub
End If
'find the next client based on priorities
nextFound = False
NextClient
'If no more then clear controls to avoid confusion and show message
'otherwise populate boxes with client details
If foundNum = "" Then
For Each cControl In Me.Controls
If cControl.Name Like "txt*" Then cControl = vbNullString
Next
MsgBox ("No more clients ready to call.")
Else
strCriteria = "ClientRef = '" & foundNum & "'"
rst.FindFirst (strCriteria)
If Not rst.NoMatch Then
Me.txtName = rst!ClientName
Me.txtRef = rst!ClientRef
Me.txtTel = rst!Telephone
Me.txtType = rst!ReviewType
Me.txtAttempts = rst!Attempts
Me.txtTryAgain = rst!TryAgain
Me.chkLetter = rst!LetterBooking
Me.txtSaved = "False"
Debug.Print "Sent details for " & foundNum & " to form."
Else
Debug.Print "ERROR when getting next (NoMatch = True)"
End If
End If
rst.Close
Set rst = Nothing
db.Close
Set db = Nothing
End Sub
[COLOR=green]Private Sub bttnSave_Click()[/COLOR]
Dim db As Database
Dim rst As Recordset2
Set db = DBEngine(0)(0)
Set rst = db.OpenRecordset("Clients", dbOpenDynaset)
noShowCutOff = "13:30" 'Can't mark as no show until after this time
strCriteria = "ClientRef = '" & Me.txtRef & "'"
rst.FindFirst (strCriteria)
If Not rst.NoMatch Then
rst.Edit
rst!Attempts = rst!Attempts + 1
rst!Working = False
rst.Update
Me.txtSaved = ""
Debug.Print "Saved " & rst!ClientName & " " & rst!ClientRef
Else
Debug.Print "Searched " & strCriteria & ". rst.NoMatch = True"
MsgBox ("Encountered an error when trying to save result.")
Exit Sub
End If
rst.Close
Set rst = Nothing
db.Close
Set db = Nothing
End Sub
[COLOR=green]Sub NextClient()[/COLOR]
foundNum = findPriority("Priority = True AND Attempts = 0 AND Working = False")
If foundNum = "" Then
Debug.Print "No priority client found - checking for timed"
Else
Exit Sub
End If
foundNum = findPriority("TimeToCall -2 <= Time() AND Attempts = 0 AND Working = False")
If foundNum = "" Then
Debug.Print "No timed client found - checking for priority try again"
Else
Exit Sub
End If
End Sub
'receive string to use in FindFirst and return client number if found
[COLOR=green]Public Function findPriority(strCriteria As String) As String[/COLOR]
Dim db As Database
Dim rst As Recordset2
Set db = DBEngine(0)(0)
Set rst = db.OpenRecordset("Clients", dbOpenDynaset)
rst.FindFirst (strCriteria)
If Not rst.NoMatch Then
rst.Edit
rst!Working = True
rst.Update
findPriority = rst!ClientRef
Debug.Print "Reserved " & rst!ClientName & " " & rst!ClientRef
Else
findPriority = ""
Debug.Print "Searched " & strCriteria & ". rst.NoMatch = True"
Exit Function
End If
'this section will be expanded to check for different criteria as decribed above.
rst.Close
Set rst = Nothing
db.Close
Set db = Nothing
End FunctionI've got a few questions if anyone would be so kind...
1/ Is there a better way of checking if the user has saved their record? I'm currently writing to a hidden control and checking the value later. Seems a bit sloppy...
2/ When I identify the next record to be worked on I set a flag (Working) to True to prevent 2 users getting the same record. Is this necessary? If yes is there a better way to do it?
3/ When I start building the code for repeat attempts I will need to check the records for previous contact attempts. What's the easiest way to check for the highest value in a certain field? I plan to store it in a public variable and use it in a FindFirst statement, but welcome any suggestions for a better approach
4/ I know it's good practice to close all databases etc and set to nothing. Do I need to do this in every sub and function, or just the final one before code finishes running?
5/ In Excel you can detect if a user closes the form using the X button. I'm using a pop-up form for this and wondered if there is a similar way to capture the close and run my code for exiting gracefully?
Cheers
Diberlee
Last edited:
