Random
Random Records

What error message do you get?

Thanks Alex,

I found the "Random Records" help video and have followed that and resolved my issue so that now whenever i load the menu I get a quotation and if I click on the field it refreshes the quotation.

Many Thanks for the quick response

Alfred

This is actually a perfectly valid approach, and for a small quotes table it'll work just fine. The logic is clear, easy to follow, and it avoids the classic Access trap of ORDER BY Rnd(), which can get ugly performance wise. So from a learning standpoint, this is solid code.

That said, I can pretty confidently tell this was written by AI, and that's not a criticism. The giveaway is the explicit DAO declarations. You don't need to qualify DAO unless you're mixing DAO and ADO in the same project, which I almost never do. Also the variable naming leans more toward camelCase than the PascalCase most long time VBA developers use. Neither of those things are wrong, just stylistic fingerprints.

There are a couple of minor technical notes worth mentioning. Using dbOpenTable and MoveLast to get RecordCount forces Access to walk the entire table, which is fine for dozens or hundreds of rows but doesn't scale great if the table grows. A more production friendly pattern is DCount to get the total and then opening a snapshot recordset ordered by an AutoNumber primary key. Same idea, just fewer side effects and less locking.

Also, calling Randomize every time the routine runs usually isn't necessary unless this code fires multiple times in the same second. It won't hurt anything here, but it's something people often overuse.

For what it's worth, I use ChatGPT all the time for little helper routines like this. Could I write it myself? Sure. Do I want to spend 20 minutes writing the same function I wrote 2 years ago, or even digging through my own code vault when GPT can spit out something workable in five seconds? Nope. LOL. As long as you understand what the code is doing and why it works, it's a perfectly reasonable tool to have in your toolbox.

This is a little trick I use myself on the header of my own homepage. It's classic ASP talking to SQL Server, and the key part is ORDER BY NEWID(). In SQL Server, NEWID() generates a random GUID value. When you use it in an ORDER BY clause, SQL Server assigns a different random value to every row in the table, then sorts by those random values. The end result is that the rows are effectively shuffled into a random order. By combining that with SELECT TOP 1, you get one random record each time the page runs. It's simple, reliable, and works great for small tables like a quotes list, which is why I use it here.

Details

SQLStr = "SELECT TOP 1 * FROM QuoteT ORDER BY NEWID()"
Dim Conn
set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open Application("DATABASE_CONNECT_STRING")
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open SQLStr, Conn
QuoteText = rs("Quote")
QuoteAuthor = rs("Author")
rs.Close
set rs = Nothing
Conn.Close
set Conn = Nothing

Response.write QuoteText & " - " & QuoteAuthor
Copy

For a small quote table like mine, which has maybe 100 rows in it, this approach is perfectly fine. The database does such a tiny amount of work that the overhead is basically nonexistent, and the simplicity of the code is a big win. When you're dealing with a handful of rows, there's no practical downside to shuffling them and grabbing one at random.

Where this starts to fall apart is when the table gets big. If you've got hundreds of thousands of rows, SQL Server has to generate a random value for every single row and then sort all of them just to pick one quote. That's a lot of unnecessary work when you only need a single record. It still works, but it's doing way more effort than required, which can slow things down and put extra load on the server. That's why, for large tables, you usually switch to a different technique that avoids randomizing the entire dataset.