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

I took the quotation marks away from tblChats but still got the same error. I then took the quotations away from Agent =" chr(34) & me.cboAgent.Column(1) & chr(34) & " Order By 5 but I'm not getting a new error:

Run-time error '3075'

Syntax error (missing operator) in query expression 'Agent = chr(34) & me.cboAgent.Column(1) & chr(34) &'.

When I click Debug it highlights the line:
Set rs = DBEngine(0)(0).OpenRecordset(strSQL, dbOpenSnapshot)
 
Now you are guessing. The second lot of quotes are required because you are building (concatenating) a string to include your ccbo box value.

To include a string value within that string you need to add single quotes 'StringValue' around it, that is what the Chr(34) is doing. So by removing them you have broken the string, and any attempt to display it. Will break as there are now no " " around the values.
 
So how do I fix the Compile error that's occurring with the code?

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;"

Sorry to keep being a pain, I'm very appreciative of the help. I'm just an eejit when it comes to this.
 
Can you post the whole code including the functions as you have it now. Please use the code tags # on the editor.

Also why have you switched to me.cboAgent.Column(1) - do you know that this will return the second column of your combo box, NOT the first. Column numbering starts at (0)
 
Functions
Code: 
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(strtblChats As String, strAgent As String) As Long

    Dim rs     As DAO.Recordset
    Dim last   As Long
    Dim sSql   As String
    
    sSql = "SELECT * FROM " & strtblChats & " 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

Get Chat button
Code: 
'Code gets a random chat from the database.
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) & ") As Expr1 From tblChats where Agent =" chr(34) & me.cboAgent.Column(0) & chr(34) & " Order By 5;"
    Set rs = DBEngine(0)(0).OpenRecordset(strSQL, dbOpenSnapshot)
    Me![cboChatID] = rs(0)
    Me![cboAgent2] = rs(1)
    Me![cboChatDate] = rs(2)
    Me![cboDuration] = rs(3)
    rs.Close
    Set rs = Nothing
End Sub
 
Okay - you have a combination of Arnelgp's original code and my modifications which isn't going to work.

I have changed the code in both these parts - so please replace the whole lot. I've added some comments that will hopefully allow you to see what is happening.
Code: 
Public Function GetRandomChat(strtblChats As String, strAgent As String) As Long
   [COLOR="green"] 'This takes the chat table as a string and the agent name as parameters for the function.[/COLOR]
    Dim rs               As DAO.Recordset
    Dim last             As Long
    Dim sSql             As String

    sSql = "SELECT * FROM " & strtblChats & " WHERE Agent = '" & strAgent & "' ;"
    Debug.Print sSql
    
   [COLOR="green"] 'Open a snapshot of the records in the table based on the query above[/COLOR]
    Set rs = DBEngine(0)(0).OpenRecordset(sSql, dbOpenSnapshot)
    rs.MoveLast
    rs.MoveFirst
    last = rs.RecordCount
   [COLOR="green"] 'Using the number of records from the record count - get a random number between the first and last record[/COLOR]
    rs.Move (RandomNumber(1, last))
   [COLOR="green"] 'Go to that record[/COLOR]
    
 [COLOR="green"]   'Store the chat Id as the functions return value[/COLOR]
    GetRandomChat = rs(0)

    rs.Close
    Set rs = Nothing


End Function

[COLOR="green"]'Code gets a random chat from the database.[/COLOR]
Private Sub buttonGetChat_Click()
    Dim rs As DAO.Recordset
    Dim strSQL As String
    strSQL = "SELECT [ChatID],[Agent],[DateTime],[Duration] Where Chat_ID = (GetRandomChat('tblChats' , " & Chr(34) & Me.cboAgent.Column(0) & Chr(34) & ") ;"
    Debug.Print strSQL
   [COLOR="Green"] ' The string above should get a single record based on the chat ID returned from the function
    ' Using that recordset populate your controls.[/COLOR]
    Set rs = DBEngine(0)(0).OpenRecordset(strSQL, dbOpenSnapshot)
    Me![cboChatID] = rs(0)
    Me![cboAgent2] = rs(1)
    Me![cboChatDate] = rs(2)
    Me![cboDuration] = rs(3)
    rs.Close
    Set rs = Nothing
End Sub
This isn't tested but should work, if you get errors report back and include a copy and paste of the strings that will appear in the Immediate window if you click debug on the error.
 
Minty said:
Okay - you have a combination of Arnelgp's original code and my modifications which isn't going to work.

I have changed the code in both these parts - so please replace the whole lot. I've added some comments that will hopefully allow you to see what is happening.
Code: 
Public Function GetRandomChat(strtblChats As String, strAgent As String) As Long
   [COLOR="green"] 'This takes the chat table as a string and the agent name as parameters for the function.[/COLOR]
    Dim rs               As DAO.Recordset
    Dim last             As Long
    Dim sSql             As String

    sSql = "SELECT * FROM " & strtblChats & " WHERE Agent = '" & strAgent & "' ;"
    Debug.Print sSql
    
   [COLOR="green"] 'Open a snapshot of the records in the table based on the query above[/COLOR]
    Set rs = DBEngine(0)(0).OpenRecordset(sSql, dbOpenSnapshot)
    rs.MoveLast
    rs.MoveFirst
    last = rs.RecordCount
   [COLOR="green"] 'Using the number of records from the record count - get a random number between the first and last record[/COLOR]
    rs.Move (RandomNumber(1, last))
   [COLOR="green"] 'Go to that record[/COLOR]
    
 [COLOR="green"]   'Store the chat Id as the functions return value[/COLOR]
    GetRandomChat = rs(0)

    rs.Close
    Set rs = Nothing


End Function

[COLOR="green"]'Code gets a random chat from the database.[/COLOR]
Private Sub buttonGetChat_Click()
    Dim rs As DAO.Recordset
    Dim strSQL As String
    strSQL = "SELECT [ChatID],[Agent],[DateTime],[Duration] Where Chat_ID = (GetRandomChat('tblChats' , " & Chr(34) & Me.cboAgent.Column(0) & Chr(34) & ") ;"
    Debug.Print strSQL
   [COLOR="Green"] ' The string above should get a single record based on the chat ID returned from the function
    ' Using that recordset populate your controls.[/COLOR]
    Set rs = DBEngine(0)(0).OpenRecordset(strSQL, dbOpenSnapshot)
    Me![cboChatID] = rs(0)
    Me![cboAgent2] = rs(1)
    Me![cboChatDate] = rs(2)
    Me![cboDuration] = rs(3)
    rs.Close
    Set rs = Nothing
End Sub
This isn't tested but should work, if you get errors report back and include a copy and paste of the strings that will appear in the Immediate window if you click debug on the error.
Click to expand...

Get an error saying: "Syntax error (missing operator) in query expression '[Duration] Where Chat_ID = (GetRandomChat('tblChats',"AGENT NAME");'.

and in the Immediate window it has:
Code: 
SELECT [ChatID],[Agent],[DateTime],[Duration] Where Chat_ID = (GetRandomChat('tblChats' , "AGENT NAME") ;

I've put "AGENT NAME" in place of the name of the agent I was testing it on.
 
Last edited:
I've noticed I've missed a closing ) in the expression, apologies.

To hard code the agent put their name in single quotes - so your complete new line for agent called John Doe should be ;
Code: 
strSQL = "SELECT [ChatID],[Agent],[DateTime],[Duration] Where Chat_ID = (GetRandomChat('tblChats' ,'John Doe')[COLOR="Red"])[/COLOR] ;"
 
Minty said:
I've noticed I've missed a closing ) in the expression, apologies.

To hard code the agent put their name in single quotes - so your complete new line for agent called John Doe should be ;
Code: 
strSQL = "SELECT [ChatID],[Agent],[DateTime],[Duration] Where Chat_ID = (GetRandomChat('tblChats' ,'John Doe')[COLOR="Red"])[/COLOR] ;"
Click to expand...
Does the agent name need to be hard coded? If so do I need to put in a lot of IIF formulae to say if it's John Doe use this code, if it's Jane Bloggs use this code or is there an alternative way?

I've tried your code with my combo box dropdown of agent names but getting the same Syntax error and:
Code: 
SELECT [ChatID],[Agent],[DateTime],[Duration] Where ChatID = (GetRandomChat('tblChats' , "John Doe")) ;
in the Immediate Window.
 
No the agent doesn't need to be hard coded. I assumed you had done that for trouble shooting.

I'm sorry I also hadn't noticed a fundamental flaw in the original code, it's missing the FROM clause! To use your original combo please try this, replacing the Me.cmb... with your control name.

Code: 
strSQL = "SELECT [ChatID],[Agent],[DateTime],[Duration] FROM tblChats WHERE Chat_ID = (GetRandomChat('tblChats' ,'" & Me.cmbAgent & "')) ;"
 
Minty said:
No the agent doesn't need to be hard coded. I assumed you had done that for trouble shooting.

I'm sorry I also hadn't noticed a fundamental flaw in the original code, it's missing the FROM clause! To use your original combo please try this, replacing the Me.cmb... with your control name.

Code: 
strSQL = "SELECT [ChatID],[Agent],[DateTime],[Duration] FROM tblChats WHERE Chat_ID = (GetRandomChat('tblChats' ,'" & Me.cmbAgent & "')) ;"
Click to expand...

I've put in your code and tried the form. Selected 'John Doe' from the dropdown and clicked my Get Chat button but it's now coming up with an error saying:

Run-time error '3021': No current record.

Immediate window says:
Code: 
SELECT [ChatID],[Agent],[DateTime],[Duration] FROM tblChats WHERE ChatID = (GetRandomChat('tblChats' ,'John Doe')) ;
SELECT * FROM tblChats WHERE Agent = 'John Doe' ;

There are 2 records in tblChats with John Doe as the Agent. The VBA window has:
Code: 
GetRandomChat = rs(0)
highlighted when it opens if that means anything.
 
Okay - now we are getting somewhere. Your SQL strings look correct to me. So maybe something isn't returning a record from the random bit.

Just as a check - Paste

SELECT [ChatID],[Agent],[DateTime],[Duration] FROM tblChats WHERE ChatID = (GetRandomChat('tblChats' ,'John Doe')) ;

Into a new blank query SQL view and the run it. In the meantime I'll look into the random code a bit closer.
 
Try Changing this bit of code
Code: 
   [COLOR="Green"] 'Store the chat Id as the functions return value[/COLOR]
    Debug.Print "ChatID : " &  rs.fields("ChatID")
    GetRandomChat = rs.fields("ChatID")
 
I was able to use your query:

SELECT [ChatID],[Agent],[DateTime],[Duration] FROM tblChats WHERE ChatID = (GetRandomChat('tblChats' ,'John Doe')) ;

And it ran without a problem and pulled one chat into a table. I can change the agent name to any other agent name and provided I Save + Close the query I can then re-run it and it works. If I change the name in the query, save and then just click the Run button though it'll give me the 'No current record' error.
 
In that case it's possibly the syntax in Arnelgp's settings. It's not a syntax I have used so I assumed it was ok. Try changing the bottom part of your code to;
Code: 
    Me.cboChatID = rs.Fields("ChatID")
    Me.cboAgent2 = rs.Fields("Agent")
    Me.cboChatDate = rs.Fields("DateTime")
    Me.cboDuration = rs.Fields("Duration")
 
Hello! Apologies it's taken a couple of days, haven't been near my work laptop all weekend.

Thank you both for the help. It works brilliantly. :) I added in an After Insert action too to remove the chat from the list of possible chats to evaluate once done too so avoid any potential - though very small - chance of the same chat being done more than once.
 
You must log in or register to reply here.

Similar threads

R
Open New Form and Populate Fields Based on the Old Form
Replies
11
Views
410
MajP
MajP
J
ms access query by insert a random record which not exist in other table
Replies
8
Views
556
Pat Hartman
P
B
  • Question Question
Auto-populate dependent table/form based on selection in main table/form.
Replies
1
Views
230
Pat Hartman
P
H
Pull a data from Customer Table based on a combo box
Replies
13
Views
346
hrdpgajjar
H
D
Solved Populate Form & Associated Table with Values from another Table
Replies
5
Views
657
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom