Dlookup Need To Select Random Value

[Slashback115]

Here is what I have this looks up the first field in box and works fine. I am hoping there is some simple line I need to add. And not over engineer it.

---------------------------------------------------------------------------------------
Private Sub shuffle_Click()

str_table = DLookup("

", "Innq")

Me.Table1.Value = str_table

End Sub
---------------------------------------------------------------------------------------



This was my attempt to make it random, but it won't randomize it.
---------------------------------------------------------------------------------------
Private Sub shuffle_Click()

intRandom = Int((30 * Rnd) + 1)

str_table = DLookup("

", "Innq", "[ID]='" & intRandom)

Me.Table1.Value = str_table

End Sub
---------------------------------------------------------------------------------------

 

[plog]

Here's the link to the Rnd function: https://www.techonthenet.com/access/functions/numeric/rnd.php

I think your implementation produces results way outside what your ID values are. I don't think you are constraining the value of Rnd to just possible ID values. And if ID is an autonumber, I don't think you can ensure you do. Even if you limit it to Min(ID) and Max(ID) there's still the possiblity of generating ID values that have been deleted.

My advice is to make a query to randomize your table:

SELECT * FROM Innq ORDER BY Rnd(ID)

Then do the Dlookup into that query.

 

[Slashback115]

Based on your advice online google I have come up with this.

Private Sub shuffle_Click()
' create a random number that references the ID column from the table Inn and returns the corresponding Event column listing

Dim sSQL As String
Dim rs As DAO.Recordset


sSQL = "SELECT Event FROM Inn WHERE ID = '" & Int((4 * Rnd) + 1) & "'"
MsgBox (sSQL)
Set rs = CurrentDb.OpenRecordset(sSQL)

strText = rs!Event
strText = rs.Fields(1)

srt_table = DLookup("Event", "Inn", "ID=" & eventbox)

srt_table = Me.eventbox.Value


End Sub

Error is coming from Set rs = CurrentDB.OpenRecordset(sSQL). "Datatype Mismatch in Criteria Expression 3464"

 

[Slashback115]

Got it working. I over complicated it lol.

Private Sub shuffle_Click()
' create a random number that references the ID column from the table Inn and returns the corresponding Event column listing

Dim sSQL As String

sSQL = "SELECT Event FROM Inn WHERE ID = '" & Int((4 * Rnd) + 1) & "'"
srt_table = DLookup("Event", "Inn", "ID=" & Int((4 * Rnd) + 1))

Me.eventbox.Value = srt_table

End Sub

 

[jdraw]

Are you certain this is working?

In this line of your code

sSQL = "SELECT Event FROM Inn WHERE ID = '" & Int((4 * Rnd) + 1) & "'"

you are saying ID is a text/string.

In this line, you're saying it is a number

DLookup("Event", "Inn", "ID=" & Int((4 * Rnd) + 1))

What is the reasoning that says a record in your table will have the ID generated by your code?