Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-10-2013, 07:06 AM   #1
ScottBeatty
Newly Registered User
 
Join Date: May 2013
Posts: 65
Thanks: 10
Thanked 0 Times in 0 Posts
ScottBeatty is on a distinguished road
Button to find random record

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

ScottBeatty is offline   Reply With Quote
Old 06-10-2013, 07:10 AM   #2
pr2-eugin
Super Moderator
 
pr2-eugin's Avatar
 
Join Date: Nov 2011
Location: Bournemouth, UK
Posts: 8,498
Thanks: 68
Thanked 2,067 Times in 2,014 Posts
pr2-eugin has a spectacular aura about pr2-eugin has a spectacular aura about pr2-eugin has a spectacular aura about
Re: Button to find random record

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?
__________________
Regards,
Paul Eugin

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
!
!
Windows 7 Professional, MS Access 2010
---------------------------------------------------------------------------------------------------------------------
If the above post has helped you, please click the scales
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
or click the 'Thumbs up'. Cheers.

---------------------------------------------------------------------------------------------------------------------



pr2-eugin is offline   Reply With Quote
Old 06-10-2013, 07:15 AM   #3
ScottBeatty
Newly Registered User
 
Join Date: May 2013
Posts: 65
Thanks: 10
Thanked 0 Times in 0 Posts
ScottBeatty is on a distinguished road
Re: Button to find random record

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?

ScottBeatty is offline   Reply With Quote
Old 06-10-2013, 07:34 AM   #4
pr2-eugin
Super Moderator
 
pr2-eugin's Avatar
 
Join Date: Nov 2011
Location: Bournemouth, UK
Posts: 8,498
Thanks: 68
Thanked 2,067 Times in 2,014 Posts
pr2-eugin has a spectacular aura about pr2-eugin has a spectacular aura about pr2-eugin has a spectacular aura about
Re: Button to find random record

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?
__________________
Regards,
Paul Eugin

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
!
!
Windows 7 Professional, MS Access 2010
---------------------------------------------------------------------------------------------------------------------
If the above post has helped you, please click the scales
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
or click the 'Thumbs up'. Cheers.

---------------------------------------------------------------------------------------------------------------------



pr2-eugin is offline   Reply With Quote
Old 06-10-2013, 07:38 AM   #5
ScottBeatty
Newly Registered User
 
Join Date: May 2013
Posts: 65
Thanks: 10
Thanked 0 Times in 0 Posts
ScottBeatty is on a distinguished road
Re: Button to find random record

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.
ScottBeatty is offline   Reply With Quote
Old 06-10-2013, 07:56 AM   #6
pr2-eugin
Super Moderator
 
pr2-eugin's Avatar
 
Join Date: Nov 2011
Location: Bournemouth, UK
Posts: 8,498
Thanks: 68
Thanked 2,067 Times in 2,014 Posts
pr2-eugin has a spectacular aura about pr2-eugin has a spectacular aura about pr2-eugin has a spectacular aura about
Re: Button to find random record

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
'*******************************************************
'   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
'*******************************************************
    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
__________________
Regards,
Paul Eugin

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
!
!
Windows 7 Professional, MS Access 2010
---------------------------------------------------------------------------------------------------------------------
If the above post has helped you, please click the scales
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
or click the 'Thumbs up'. Cheers.

---------------------------------------------------------------------------------------------------------------------




Last edited by pr2-eugin; 06-10-2013 at 08:17 AM. Reason: Added more info.
pr2-eugin is offline   Reply With Quote
The Following User Says Thank You to pr2-eugin For This Useful Post:
ScottBeatty (06-10-2013)
Old 06-10-2013, 08:19 AM   #7
ScottBeatty
Newly Registered User
 
Join Date: May 2013
Posts: 65
Thanks: 10
Thanked 0 Times in 0 Posts
ScottBeatty is on a distinguished road
Re: Button to find random record

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

ScottBeatty is offline   Reply With Quote
Old 06-10-2013, 08:26 AM   #8
pr2-eugin
Super Moderator
 
pr2-eugin's Avatar
 
Join Date: Nov 2011
Location: Bournemouth, UK
Posts: 8,498
Thanks: 68
Thanked 2,067 Times in 2,014 Posts
pr2-eugin has a spectacular aura about pr2-eugin has a spectacular aura about pr2-eugin has a spectacular aura about
Re: Button to find random record

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 = "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
So now you have this ID, how are you planning on opening this record?
__________________
Regards,
Paul Eugin

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
!
!
Windows 7 Professional, MS Access 2010
---------------------------------------------------------------------------------------------------------------------
If the above post has helped you, please click the scales
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
or click the 'Thumbs up'. Cheers.

---------------------------------------------------------------------------------------------------------------------



pr2-eugin is offline   Reply With Quote
Old 06-10-2013, 08:33 AM   #9
ScottBeatty
Newly Registered User
 
Join Date: May 2013
Posts: 65
Thanks: 10
Thanked 0 Times in 0 Posts
ScottBeatty is on a distinguished road
Re: Button to find random record

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.
ScottBeatty is offline   Reply With Quote
Old 06-10-2013, 08:48 AM   #10
pr2-eugin
Super Moderator
 
pr2-eugin's Avatar
 
Join Date: Nov 2011
Location: Bournemouth, UK
Posts: 8,498
Thanks: 68
Thanked 2,067 Times in 2,014 Posts
pr2-eugin has a spectacular aura about pr2-eugin has a spectacular aura about pr2-eugin has a spectacular aura about
Re: Button to find random record

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
    Dim RandomID As Long
    strSQL = "SELECT MIN(ID) AS MinOfID, " & _
             "Max(ID) AS MaxOfID FROM [Imported Data];"
    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
    Me.recordsetclone.Findfirst ("ID = " & RandomID)
    Set dbObj = Nothing
    Set rsObj = Nothing
End Sub
The highlighted section should take you to the Record..
__________________
Regards,
Paul Eugin

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
!
!
Windows 7 Professional, MS Access 2010
---------------------------------------------------------------------------------------------------------------------
If the above post has helped you, please click the scales
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
or click the 'Thumbs up'. Cheers.

---------------------------------------------------------------------------------------------------------------------



pr2-eugin is offline   Reply With Quote
The Following User Says Thank You to pr2-eugin For This Useful Post:
ScottBeatty (06-10-2013)
Old 06-10-2013, 08:55 AM   #11
ScottBeatty
Newly Registered User
 
Join Date: May 2013
Posts: 65
Thanks: 10
Thanked 0 Times in 0 Posts
ScottBeatty is on a distinguished road
Re: Button to find random record

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?
ScottBeatty is offline   Reply With Quote
Old 06-10-2013, 09:29 AM   #12
billmeye
Access Aficionado
 
Join Date: Feb 2010
Location: New Haven CT, USA
Posts: 542
Thanks: 0
Thanked 119 Times in 116 Posts
billmeye is on a distinguished road
Re: Button to find random record

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
billmeye is offline   Reply With Quote
The Following User Says Thank You to billmeye For This Useful Post:
ScottBeatty (06-10-2013)
Old 06-10-2013, 09:32 AM   #13
ScottBeatty
Newly Registered User
 
Join Date: May 2013
Posts: 65
Thanks: 10
Thanked 0 Times in 0 Posts
ScottBeatty is on a distinguished road
Re: Button to find random record

thank you so much billmeye! It worked perfectly
ScottBeatty is offline   Reply With Quote
Old 06-10-2013, 10:31 AM   #14
billmeye
Access Aficionado
 
Join Date: Feb 2010
Location: New Haven CT, USA
Posts: 542
Thanks: 0
Thanked 119 Times in 116 Posts
billmeye is on a distinguished road
Re: Button to find random record

Great, your welcome.
billmeye is offline   Reply With Quote
Old 06-11-2013, 12:51 AM   #15
pr2-eugin
Super Moderator
 
pr2-eugin's Avatar
 
Join Date: Nov 2011
Location: Bournemouth, UK
Posts: 8,498
Thanks: 68
Thanked 2,067 Times in 2,014 Posts
pr2-eugin has a spectacular aura about pr2-eugin has a spectacular aura about pr2-eugin has a spectacular aura about
Re: Button to find random record

Quote:
Originally Posted by billmeye View Post
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..

__________________
Regards,
Paul Eugin

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
!
!
Windows 7 Professional, MS Access 2010
---------------------------------------------------------------------------------------------------------------------
If the above post has helped you, please click the scales
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
or click the 'Thumbs up'. Cheers.

---------------------------------------------------------------------------------------------------------------------



pr2-eugin is offline   Reply With Quote
Reply

Tags
button , form , random , record

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Button on a form to find a certain record? Sonya_S General 69 08-26-2012 12:36 PM
Find Record button error zedlexx Forms 0 03-09-2009 07:58 AM
[SOLVED] Find BUtton Locking Record keyboard88 Forms 0 06-08-2006 02:39 PM
Find Record button on form bmike Forms 1 10-30-2004 06:18 AM
cmd button to find record Steven811 Modules & VBA 2 05-27-2004 11:16 PM




All times are GMT -8. The time now is 10:49 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World