Button to find random record (1 Viewer)

ScottBeatty

Registered User.
Local time
Yesterday, 23:43
Joined
May 31, 2013
Messages
65
Hello everyone. I was wondering if anyone could help me create a button on my form that would find a random record. I would imagine I will be doing this through VB but any help would be great. Thanks
 

pr2-eugin

Super Moderator
Local time
Today, 04:43
Joined
Nov 30, 2011
Messages
8,498
Hello Scott, define Random.. You can use the Rnd() to get Random number.. Maybe between a range.. Is that something you would be interested in?
 

ScottBeatty

Registered User.
Local time
Yesterday, 23:43
Joined
May 31, 2013
Messages
65
Yes sorry I should have been specific. The problem I have is that I am using this database as a template so I can import data from Excel. Having said that, my ranges are not always the same (meaning my upper bound and my lower bound). Is there a generic code for finding a random record that is applicable to any database size?
 

pr2-eugin

Super Moderator
Local time
Today, 04:43
Joined
Nov 30, 2011
Messages
8,498
Could you in a bit more detail explain what you need.. Just a simple SELECT query, that will take a random record? Is there any ID field in the table that the Rnd function can use?
 

ScottBeatty

Registered User.
Local time
Yesterday, 23:43
Joined
May 31, 2013
Messages
65
I actually haven't been using a query to do this. I was just wondering if there was a way to find a random record in a form that has data from my table. I have an ID as my primary key so every record has a unique ID. I am just wondering if there is a way to make a generic random function for varying data sizes. For example, I may have data that I want to import into my database that has 20 records but then the next day there may be data that I want to import that has 2,000 records. Therefore, I do not have a definite upper bound to a Rnd function.
 

pr2-eugin

Super Moderator
Local time
Today, 04:43
Joined
Nov 30, 2011
Messages
8,498
That's alright.. As long as it has some ID it can look at.. The following code will generate a Random ID within a Range, you do not have to specify, the code will automatically take the Min and Max values from the table..
Code:
Public Function getRandomID(tmpTblName As String, tmpFldName As String) As Long
[COLOR=Green]'*******************************************************
'   Code to obtain a Random ID between a Minimum
'       and Maximum number. The code uses
'   DAO Recordset object, to obtain the Min & Max
'           and returns a Random value.
'
'Input : tmpTblName - Table Name - to obtain value from
'        tmpFldName - Field Name - The ID field
'
' USAGE Exmaple:
'   ? getRandomID("tbl_Clients","clientID")
'     5109
'
'Code Courtesy of
'  Paul Eugin
'*******************************************************[/COLOR]
    Dim dbObj As DAO.Database, rsObj As DAO.Recordset
    Dim strSQL As String, minID As Long, maxID As Long
    
    strSQL = "SELECT Min(" & tmpFldName & ") AS MinOfID, " & _
             "Max(" & tmpFldName & ") AS MaxOfID FROM " & tmpTblName
        
    Set dbObj = CurrentDb()
    Set rsObj = dbObj.OpenRecordset(strSQL)
    
    If rsObj.RecordCount <> 0 Then
        minID = rsObj!MinOfID
        maxID = rsObj!MaxOfID
        getRandomID = Int((maxID - minID + 1) * Rnd + 1)
    End If
    
    Set dbObj = Nothing
    Set rsObj = Nothing
End Function
You can change the code if you want to.. but that is the basic idea behind it.. So if you want to SELECT a Random record.. Just create a Query as..
Code:
SELECT SomeFields FROM theTable WHERE theIDField = getRandomID("theTable","theIDField");
Save the Query by giving it a Name and then on the Click of the button Open it..
Code:
Private Sub buttonName_Click()
    DoCmd.OpenQuery "theQueryYouJustSaved"
End Sub
 
Last edited:

ScottBeatty

Registered User.
Local time
Yesterday, 23:43
Joined
May 31, 2013
Messages
65
Okay I was wondering if you could help me out with some troubleshooting. I typed in everything nearly word for word because I am still a novice in VBA and I got a Run-time error '3131': Syntax error in FROM clause.

My table name is "Imported Data" the field name is just "ID" and the button name is just Command204

Here is my code:

Private Sub Command204_Click()
Dim dbObj As DAO.Database, rsObj As DAO.Recordset
Dim strSQL As String, minID As Long, maxID As Long
strSQL = "SELECT MIN(" & ID & ") AS MinOfID, " & _
"Max (" & tmpFldName & ") AS MaxOfID FROM " & tmpImportedData
Set dbObj = CurrentDb()
Set rsObj = dbObj.OpenRecordset(strSQL)
If rsObj.RecordCount <> 0 Then
minID = rsObj!MinOfID
maxID = rsObj!MaxOfID
getRandomID = Int((maxID - minID + 1) * Rnd + 1)
End If
End Sub
 

pr2-eugin

Super Moderator
Local time
Today, 04:43
Joined
Nov 30, 2011
Messages
8,498
I guess ID is the Field Name and tmpImportedData is the name of the Table.. If you are Hard Coding the Names of Table and Field Name you do not have to enclose them as variables.. So try the following..
Code:
Private Sub Command204_Click()
    Dim dbObj As DAO.Database, rsObj As DAO.Recordset
    Dim strSQL As String, minID As Long, maxID As Long
    strSQL = [B]"SELECT MIN(ID) AS MinOfID, " & _
             "Max(ID) AS MaxOfID FROM tmpImportedData;"[/B]
    Set dbObj = CurrentDb()
    Set rsObj = dbObj.OpenRecordset(strSQL)
    If rsObj.RecordCount <> 0 Then
        minID = rsObj!MinOfID
        maxID = rsObj!MaxOfID
        getRandomID = Int((maxID - minID + 1) * Rnd + 1)
    End If
End Sub
So now you have this ID, how are you planning on opening this record?
 

ScottBeatty

Registered User.
Local time
Yesterday, 23:43
Joined
May 31, 2013
Messages
65
Okay, I came up with another error. It says Run-time error "3078": The Microsoft Access database engine cannot find the input table or query 'tmpImportedData'. Make sure it exists and that its name is spelled correctly.

I was confused about this so I clicked on Debug and Access highlighted this line:
Set rsObj = dbObj.OpenRecordset(strSQL)

Again, here is my code:

Private Sub Command204_Click()
Dim dbObj As DAO.Database, rsObj As DAO.Recordset
Dim strSQL As String, minID As Long, maxID As Long
strSQL = "SELECT MIN(ID) AS MinOfID, " & _
"Max (ID) AS MaxOfID FROM tmpImportedData;"
Set dbObj = CurrentDb()
Set rsObj = dbObj.OpenRecordset(strSQL)
If rsObj.RecordCount <> 0 Then
minID = rsObj!MinOfID
maxID = rsObj!MaxOfID
getRandomID = Int((maxID - minID + 1) * Rnd + 1)
End If
End Sub

I'm also a little confused by your question? Maybe if I describe my set up, that will help. I have a form with my ID as a field along with all of my other information. I'm hopefully going to have a button that has the above code to find a random record out of all of my records and then go from there.
 

pr2-eugin

Super Moderator
Local time
Today, 04:43
Joined
Nov 30, 2011
Messages
8,498
Okay My bad, I just now saw what your table Name was.. Change it as..
Code:
Private Sub Command204_Click()
    Dim dbObj As DAO.Database, rsObj As DAO.Recordset
    Dim strSQL As String, minID As Long, maxID As Long
    [COLOR=Blue][B]Dim RandomID As Long[/B][/COLOR]
    strSQL = "SELECT MIN(ID) AS MinOfID, " & _
             "Max(ID) AS MaxOfID FROM [COLOR=Blue][B][Imported Data][/B][/COLOR];"
    Set dbObj = CurrentDb()
    Set rsObj = dbObj.OpenRecordset(strSQL)
    If rsObj.RecordCount <> 0 Then
        minID = rsObj!MinOfID
        maxID = rsObj!MaxOfID
        RandomID = Int((maxID - minID + 1) * Rnd + 1)
    End If
    [COLOR=Red]Me.recordsetclone.Findfirst ("ID = " & RandomID)[/COLOR]
    Set dbObj = Nothing
    Set rsObj = Nothing
End Sub
The highlighted section should take you to the Record..
 

ScottBeatty

Registered User.
Local time
Yesterday, 23:43
Joined
May 31, 2013
Messages
65
Oh no worries. Well, I made progress. The button with the corresponding code is now clickable without any error messages. However, it does not find a page. I thought it initially had something to do with my ID being set to visible= no but I just changed it to yes and it did not make a difference. Do you have any suggestions?
 

billmeye

Access Aficionado
Local time
Yesterday, 23:43
Joined
Feb 20, 2010
Messages
542
Hi Scott, Paul. I've done this before like this:

Code:
Dim RecordNumber As Integer
Dim RecSor As String
RecSor = Me.RecordSource
RecordNumber = CInt(Rnd() * DCount("*", RecSor))
DoCmd.GoToRecord , , acFirst
Me.Recordset.Move RecordNumber
 

pr2-eugin

Super Moderator
Local time
Today, 04:43
Joined
Nov 30, 2011
Messages
8,498
Hi Scott, Paul. I've done this before like this:
Code:
Dim RecordNumber As Integer
Dim RecSor As String
RecSor = Me.RecordSource
RecordNumber = CInt(Rnd() * DCount("*", RecSor))
DoCmd.GoToRecord , , acFirst
Me.Recordset.Move RecordNumber
Well isn't this neat.. Good one mate.. :)
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom