Randomizer not being random

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

miss031 - 18 Mar 2007 17:57 GMT

I made a subform with a twxt box on my main menu to show a "quote of the
day". I have about 200 of them etnered into "table_quotes", containing
fields "[quote]" as memo, and "[quote_ID]" as number (PK).

I made a query with the following SQL, and it returns a new quote every time
I run it, but not that I have the result of the query as the record source of
the text box, it returns the same first quote everytime I open the form. I
tried requery on load, so no it returns another same quote everytime. What
did I do wrong?

SELECT TOP 1 table_quotes.*
FROM table_quotes
WHERE randomizer()=0
ORDER BY rnd(isnull([table_quotes].[quote])*0+1);

I made a command button on the form that reloads or refreshes the form to
bring a new quote, and it appears to be fairly random once the form is open,
but the form always opens on the same quote.

Reply to this Message

Gary Walter - 18 Mar 2007 23:43 GMT

I would have thought either of following
would have worked:

SELECT TOP 1 table_quotes.*
FROM table_quotes
ORDER BY Rnd(1 + Len([quote] & ""));

SELECT TOP 1 table_quotes.*
FROM table_quotes
ORDER BY Rnd([quote_ID]);

> I made a subform with a twxt box on my main menu to show a "quote of the
> day". I have about 200 of them etnered into "table_quotes", containing
[quoted text clipped - 19 lines]
> open,
> but the form always opens on the same quote.

Reply to this Message

Dale Fye - 19 Mar 2007 18:05 GMT

It looks like you have a Randomizer function that you are using. What is the
code behind it?

Personally, I'd call the Randomize function in the forms Open event, then
requery the form, and get rid of the where clause in your SQL statement

Private Sub Form_Open()

Randomize
me.requery

end sub

HTH
Dale

Signature

Email address is not valid.
Please reply to newsgroup only.

> I made a subform with a twxt box on my main menu to show a "quote of the
> day". I have about 200 of them etnered into "table_quotes", containing
[quoted text clipped - 14 lines]
> bring a new quote, and it appears to be fairly random once the form is open,
> but the form always opens on the same quote.

Reply to this Message

miss031 - 21 Mar 2007 06:26 GMT

I tried this on the subform _load and _open events separately, as well as the
main form _load and _open events, but I cannot get it to work on any one of
them. It always returns the same quote when I open the form. What am I
doing wrong?

> It looks like you have a Randomizer function that you are using. What is the
> code behind it?
[quoted text clipped - 30 lines]
> > bring a new quote, and it appears to be fairly random once the form is open,
> > but the form always opens on the same quote.

Reply to this Message

miss031 - 21 Mar 2007 06:35 GMT

Strange thing is, it works when I close the form and open it again, but this
is a main menu form, so it opens when my database opens, and it doesn't work
for that.

Is there a code can use to reload the form as soon as my database opens? I
tried requery, but it doesn't work.

Reply to this Message

Dale Fye - 22 Mar 2007 02:08 GMT

Try setting the recordsource to blank and saving the form.

Then try this:

Private sub Form_Open()

Randomize
me.RecordSource = "Insert query name here"

Exit sub

By deleteing the record source, you will basically start out with only the
labels on your form. However the Open event should set the randomizer and
then run the query that generates your first tip.

HTH
Dale

> Strange thing is, it works when I close the form and open it again, but
> this
[quoted text clipped - 30 lines]
>> open,
>> but the form always opens on the same quote.

Reply to this Message

miss031 - 22 Mar 2007 05:35 GMT

I sincerely appreciate all of the help you have given, but I am completely
at a loss. I cannot generate a random result upon opening the application
no matter what I try, and yet I get random quote every time I run the query,
and even when I open the subform by itself I get a random result.

I just tried something though: the form that the random quote generator is
on opens upon startup, and that's the only time that its not random, so I
changed the startup form to the quote subform, and its not random now either.

Is there something that affects forms when they open on startup?

> Try setting the recordsource to blank and saving the form.
>
[quoted text clipped - 48 lines]
> >> open,
> >> but the form always opens on the same quote.

Reply to this Message