Retrieve random record from table and populate fields based on criteria.

[andrewdg]

Hello all.

Hoping somebody here will be able to guide me in this endeavour.

I work for a retailer and am currently trying to create a database and front-end form for our Quality and Coaching team to use when assessing our customer service staff - in this instance it's a web chat team that engage in live discussion with customers on our website.

To eliminate any suggestion of bias from our service agents that our QA Team are intentionally picking poor/good contacts to assess I'm trying to sort a way that they are assigned a chat at random to go and assess. I have an opening form that prompts the Assessor to select the staff member being evaluated and a table behind it with a record of all chats. The table contains four fields:

• Chat ID - a unique identifier assigned to the chat by the chat software integrated into our website
• Agent - the name of the customer service rep that handled the chat
• Date/Time - when the chat happened
• Duration - length of the chat in seconds

What I would like is for the Assessor to select the name of the agent from the dropdown list and click a button which would then select a random Chat ID for that agent and populate 3 fields on the form with the Chat ID, Date/Time and Duration.

I'm pretty sure there'll be an easy way to do this but I'm an utter Access novice.

Thank you in advance for any and all help.

 

[Minty]

To get a random record ID you would need to get a count of the available records. This code blatantly stolen/copied from stackoverflow here https://stackoverflow.com/questions/6266593/in-access-find-a-random-record-true-random?rq=1 should get you going;

Option Compare Database
Option Explicit

Sub Test()
    Randomize
    Dim x As Integer
    'Print the first field of a 100 random records
    For x = 0 To 100
        CallRandomRecord
    Next x
End Sub

Sub CallRandomRecord()
    Dim rs As DAO.Recordset
    Dim recordCount As Long
    Dim randomRecord As Long

    Set rs = CurrentDb.OpenRecordset("SELECT * FROM MyTable")
    rs.MoveLast 'To get the count
    rs.MoveFirst
    recordCount = rs.recordCount - 1
    randomRecord = CLng((recordCount) * Rnd)

     rs.Move randomRecord

     Debug.Print "Random Record No:" & randomRecord & "  Field 1:  " & rs.Fields(0)

End Sub

You would replace the ("SELECT * FROM MyTable") with
SELECT * FROM YourTable WHERE Agent = '" & Me.YourComboAgent &"' ;"

Where the YourComboAgent is your combocontrol name with your agents listed.

 

[arnelgp]

maybe somebody has short and better method.
this is what i have in minde.

first create a function that will generate random number.
on a module add this codes:


Option Compare Database
Option Explicit

Public Function RandomNumber(Optional Lowest As Long = 1, Optional Highest As Long = 9999)
' Generates a random whole number within a given range
Randomize (Timer)
RandomNumber = Int(Rnd * (Highest + 1 - Lowest)) + Lowest
End Function


Public Function GetRandomChat(strTableName As String) As Long

Dim rs As DAO.recordSet
Dim last As Long
Set rs = DBEngine(0)(0).OpenRecordset(strTableName, dbOpenSnapshot)
last = rs.RecordCount
rs.Close
Set rs = Nothing
GetRandomChat = RandomNumber(1, last)
End Function


----
next, on the click of a button on your form to get random records:

Private sub button_click()
dim rs as dao.recordset
dim strSQL as string
strSQL="SELECT [Chat ID],[Agent],[DAte/Time],[Duration],GetRandomChat(" & chr(34) & "yourTableName" & Chr(34) & ") As Expr1 From yourTable Order By 5;"
set rs = dbengine(0)(0).OpenRecordset(strSQL, dbOpenSnapshot)
Me![yourIDChatID_textbox]=rs(0)
Me![yourAgent_textbox]=rs(1)
Me![yourDate_textbox]=rs(2)
Me![yourDuration_textbox]=rs(3)
rs.Close
set rs=Nothing
End Sub

 

[andrewdg]

Thank you both for the help, I'll try them in the morning and report back!

 

[andrewdg]

maybe somebody has short and better method.
this is what i have in minde.

first create a function that will generate random number.
on a module add this codes:


Option Compare Database
Option Explicit

Public Function RandomNumber(Optional Lowest As Long = 1, Optional Highest As Long = 9999)
' Generates a random whole number within a given range
Randomize (Timer)
RandomNumber = Int(Rnd * (Highest + 1 - Lowest)) + Lowest
End Function


Public Function GetRandomChat(strTableName As String) As Long

Dim rs As DAO.recordSet
Dim last As Long
Set rs = DBEngine(0)(0).OpenRecordset(strTableName, dbOpenSnapshot)
last = rs.RecordCount
rs.Close
Set rs = Nothing
GetRandomChat = RandomNumber(1, last)
End Function


----
next, on the click of a button on your form to get random records:

Private sub button_click()
dim rs as dao.recordset
dim strSQL as string
strSQL="SELECT [Chat ID],[Agent],[DAte/Time],[Duration],GetRandomChat(" & chr(34) & "yourTableName" & Chr(34) & ") As Expr1 From yourTable Order By 5;"
set rs = dbengine(0)(0).OpenRecordset(strSQL, dbOpenSnapshot)
Me![yourIDChatID_textbox]=rs(0)
Me![yourAgent_textbox]=rs(1)
Me![yourDate_textbox]=rs(2)
Me![yourDuration_textbox]=rs(3)
rs.Close
set rs=Nothing
End Sub

Hello, hoping somebody will be able to offer some further assistance. I'm using this code and it's pulling chat details from my table but it's changing the Agent as well.

What I'm hoping to have is that I can choose a particular agent from a list (for arguments sake let's call him "Joe Bloggs") and then click the button and it'll pull a random chat of Joe's in the table and populate the Chat ID, Date/Time and Duration fields.

At the moment I select Joe and click the button but it's then pulling a chat for another agent (say "Jane Doe"), changing the Agent field to Jane Doe and filling the fields in with the chat details.

Thank you again to whomever can help!

A

 

[Minty]

That's because Arnelgp code is picking a completely random chat ID, not a random one from that agents list.

Change the random function to this

Public Function GetRandomChat(strTableName As String, strAgent as String) As Long

	Dim rs     As DAO.recordSet
	Dim last   As Long
	Dim sSql   As String
	
	sSql = "SELECT * FROM " & strTableName & " WHERE Agent = '" & strAgent &"' ;" 
	
	Set rs = DBEngine(0)(0).OpenRecordset(sSql , dbOpenSnapshot)
	last = rs.RecordCount
	rs.Close
	Set rs = Nothing
	GetRandomChat = RandomNumber(1, last)

End Function

And Adjust the button click code to

Private sub button_click()
	dim rs as dao.recordset
	dim strSQL as string
	
	strSQL="SELECT [Chat ID],[Agent],[DAte/Time],[Duration],GetRandomChat(" & chr(34) & "yourTableName" & Chr(34) & " , " & chr(34) & Me.cmbAgent & chr(34) &") As Expr1 From yourTable Order By 5;"
	set rs = dbengine(0)(0).OpenRecordset(strSQL, dbOpenSnapshot)
	Me![yourIDChatID_textbox]=rs(0)
	Me![yourAgent_textbox]=rs(1)
	Me![yourDate_textbox]=rs(2)
	Me![yourDuration_textbox]=rs(3)
	rs.Close
	set rs=Nothing
End Sub

 

[andrewdg]

That's because Arnelgp code is picking a completely random chat ID, not a random one from that agents list.

Change the random function to this

Public Function GetRandomChat(strTableName As String, strAgent as String) As Long

	Dim rs     As DAO.recordSet
	Dim last   As Long
	Dim sSql   As String
	
	sSql = "SELECT * FROM " & strTableName & " WHERE Agent = '" & strAgent &"' ;" 
	
	Set rs = DBEngine(0)(0).OpenRecordset(sSql , dbOpenSnapshot)
	last = rs.RecordCount
	rs.Close
	Set rs = Nothing
	GetRandomChat = RandomNumber(1, last)

End Function

And Adjust the button click code to

Private sub button_click()
	dim rs as dao.recordset
	dim strSQL as string
	
	strSQL="SELECT [Chat ID],[Agent],[Date/Time],[Duration],GetRandomChat(" & chr(34) & "yourTableName" & Chr(34) & " , " & chr(34) & Me.cmbAgent & chr(34) &") As Expr1 From yourTable Order By 5;"
	set rs = dbengine(0)(0).OpenRecordset(strSQL, dbOpenSnapshot)
	Me![yourIDChatID_textbox]=rs(0)
	Me![yourAgent_textbox]=rs(1)
	Me![yourDate_textbox]=rs(2)
	Me![yourDuration_textbox]=rs(3)
	rs.Close
	set rs=Nothing
End Sub

Hi. I've put the module and on click codes in as you've suggested but it's still changing the Agent. If I select Jane Doe and click Get Chat it's changing to Joe Bloggs and pulling a Joe Bloggs chat through.

 

[Minty]

What is your Agent combo box called?

 

[andrewdg]

What is your Agent combo box called?

It's called cboAgent. I changed the "cmbAgent" in the code to "cboAgent".

Full code as I have it is:

Private Sub buttonGetChat_Click()
Dim rs As DAO.Recordset
    Dim strSQL As String
    strSQL = "SELECT [ChatID],[Agent],[DateTime],[Duration],GetRandomChat(" & Chr(34) & "tblChats" & Chr(34) & " , " & Chr(34) & Me.cboAgent & Chr(34) & ") As Expr1 From tblChats Order By 5;"
    Set rs = DBEngine(0)(0).OpenRecordset(strSQL, dbOpenSnapshot)
    Me![cboChatID] = rs(0)
    Me![cboAgent] = rs(1)
    Me![cboChatDate] = rs(2)
    Me![cboDuration] = rs(3)
    rs.Close
    Set rs = Nothing
End Sub

 

[Minty]

Okay you resetting cboAgent with this line

Me![cboAgent] = rs(1)

So remove that line or add another control to display a confirmation of the agent selected, and put it in the place of cboAgent

After the strSql Line please add

Debug.Print strSQL

In the immediate window of the VBA editor you should see your sql query - copy and paste it into a new query window substitute an agent name and run it. See what you get.

 

[andrewdg]

I've removed the line you said and added in the Debug.Print but it didn't bring up my SQL query.

When I click my Get Chat button it keeps pulling the same chat through each time regardless of the agent I've selected from the dropdown.

 

[Minty]

Do you have the Immediate window open. In the VBA editor press ctrl & G.

 

[arnelgp]

Change strSQL to;

strSQL="SELECT [Chat ID],[Agent],[DAte/Time],[Duration],GetRandomChat(" & chr(34) & "yourTableName" & Chr(34) & ") As Expr1 From yourTable where agent =" chr(34) & me.cboAgent & chr(34) & " Order By 5;"

 

[andrewdg]

I have the Immediate window open but nothing shows in it.

View attachment AccessSQL (2).zip

 

[andrewdg]

Change strSQL to;

strSQL="SELECT [Chat ID],[Agent],[DAte/Time],[Duration],GetRandomChat(" & chr(34) & "yourTableName" & Chr(34) & ") As Expr1 From yourTable where agent =" chr(34) & me.cboAgent & chr(34) & " Order By 5;"

I'm getting an Expected: End of Statement error with this. Sorry to be such a pain!

 

[arnelgp]

did you change "yourTableName" with the correct table name you have, ie: "tblChat".

 

[andrewdg]

I changed it to:

strSQL="SELECT [ChatID],[Agent],[DateTime],[Duration],GetRandomChat(" & chr(34) & "tblChats" & Chr(34) & ") As Expr1 From tblChats where Agent =" chr(34) & me.cboAgent & chr(34) & " Order By 5;"

 

[arnelgp]

can you help me debug this...

after this line:

strSQL = "SElect......"

put:

Msgbox strSQL
Stop

show us what you got in the messagebox.

how many column does cboAgent (or cboAgents?) have?
if there are more than 1, change in our strSQL (Me.cboAgent to Me.cboAgent.Column(1)).

 

[andrewdg]

The message box I get up says "Compile error: Expected: end of statement". cboAgent did have 2 columns too so I changed the code as you advised to:

strSQL="SELECT [ChatID],[Agent],[DateTime],[Duration],GetRandomChat(" & chr(34) & "tblChats" & Chr(34) & ") As Expr1 From tblChats where Agent =" chr(34) & me.cboAgent.Column(1) & chr(34) & " Order By 5;"

 

[Minty]

That's because you have surrounded tblChats with quotes - which Arnelgp's code is already providing as Chr(34). Try removing them.