CODEWITHSUNDEEP
sqlms-accessrandomvba
Muhnamana
Muhnamana

Reputation: 1044

Grab Random Record and Mark As Being Used

Alright, so I have a table called Colors, within that table I have 5 records (Red, Blue, Green, Yellow and Orange). The color table currently has two fields (ID and Color Name). My overall goal is to randomly select a color and mark this color as being used. Rinse and repeat until all colors are used and them mark all colors as being unused.

Here is the SQL on the RandomColorsQuery:

SELECT TOP 1 Colors.[Color Name]
FROM Colors
ORDER BY Rnd(ColorID);

So far, I've been able to select a random color by using the following within VBA and works fine:

Dim RanColor As DAO.Recordset
Set RanColor = CurrentDb.OpenRecordset("RandomColorsQuery")
'MsgBox (RanColor.Fields(0))

Text1.SetFocus
Text1.Text = RanColor.Fields(0)

Obviously I would need to add a new field to the Colors table, say a field called "Used". I'd rather not use a Yes/No field and just add an "X" in the "Used" field when the color is used.

Any suggestions or similar examples on how to accomplish this?

Upvotes: 0

Views: 94

Answers (1)

maxhugen
maxhugen

Reputation: 1944

Add a boolean (Yes/No) field.

Modify query to return only those that haven't been used:

SELECT TOP 1 Colors.[Color Name]
FROM Colors
WHERE USED=False
ORDER BY Rnd(ColorID);

In VBA, if the recordset returns a record, grab your color value, and set Used field to True.

If recordset returns doesn't return a record, ie rs.EOF=True, then update Used field in all records to false, and rerun the query to start over.

Upvotes: 1

Related Questions