read and write to specific field

[Darren B via AccessMonster.com]

I'm trying to make a lottery program for a church org. I've got everything
else finsihed but can not seem to resolve one issue.

During the proformance of the actually lottery I need to check the value
held in the WINNER field at the specified row determined by a random number
generator. So if the RN says 78, I want to access row 78 and check the
value stored in the winner field. I need to check this because if the field
is already a 1 I won't write to that field but rather choose another random
number.

Also, once I know the field is a 0 I want to be able to write a 1 there so
if that number is generated again I won't write to it.

The table is called LotteryEntry, the field is called WINNER. The code I
ahve so far goes like this :

Do While Count < NumberOfSpaces
Randomize
RandomNumber = Int(ListLength * Rnd + 1)


Do While RandomNumber > ListLength
Randomize
RandomNumber = Int(ListLength * Rnd + 1)
Loop
Do While Table!LotteryEntry!Winner(RandomNumber).Value = 1
Randomize
RandomNumber = Int(ListLength * Rnd + 0)
Loop


Table.LotteryEntry.Item(RandomNumber).Winner = 1
Count = Count + 1
Loop

So basically I want to go to the row number that the random number
generates and first see if it is a 1 and if so, I will draw another number,
if not I want to set that field to 1!

I've been looking in books and other forums and nobody seems to know!PLEASE
HELP!!!!!!

Darren

 

[Dirk Goldgar]

I'm trying to make a lottery program for a church org. I've got
everything else finsihed but can not seem to resolve one issue.

During the proformance of the actually lottery I need to check the
value held in the WINNER field at the specified row determined by a
random number generator. So if the RN says 78, I want to access row
78 and check the value stored in the winner field. I need to check
this because if the field is already a 1 I won't write to that field
but rather choose another random number.

Also, once I know the field is a 0 I want to be able to write a 1
there so if that number is generated again I won't write to it.

The table is called LotteryEntry, the field is called WINNER. The
code I ahve so far goes like this :

Do While Count < NumberOfSpaces
Randomize
RandomNumber = Int(ListLength * Rnd + 1)


Do While RandomNumber > ListLength
Randomize
RandomNumber = Int(ListLength * Rnd + 1)
Loop
Do While Table!LotteryEntry!Winner(RandomNumber).Value = 1
Randomize
RandomNumber = Int(ListLength * Rnd + 0)
Loop


Table.LotteryEntry.Item(RandomNumber).Winner = 1
Count = Count + 1
Loop

So basically I want to go to the row number that the random number
generates and first see if it is a 1 and if so, I will draw another
number, if not I want to set that field to 1!

I've been looking in books and other forums and nobody seems to
know!PLEASE HELP!!!!!!

Darren

You have a fundamental misunderstanding, but I think maybe, in this
case, it's one you can work around.

The misunderstanding is this: you speak of a "row number", but there's
no such thing as a "row number" in Access. If you have created a
sequential RowNumber field in your table, such that every record has a
RowNumber, and the numbers ascend in sequence, without gaps, from 1 to
the number of records in the table, then you can talk meaningfully about
row numbers. Otherwise, in accordance with relational database
principles, the records in a table have no defined sequence, so the
record that is at position 1 when you look in the table today, may be at
position 999 when you look in the table next tomorrow. The sequence of
records in a recordset is only meaningful when you explicitly sort them
via a query's ORDER BY clause, or (in Access) via the OrderBy property
of a table or form.

HOWEVER, since you're picking records at random from the set of records
in the table, I don't think it matters what order the records are in, or
which one happens to be at the "row number" you generated. Are you just
trying to pick "n" records at random and make them "winners"? Can we
assume that there are no "winners" already in the table when this
process begins? If those assumptions are true, you can do it with a
fairly simple query; see this link for how:

http://www.mvps.org/access/queries/qry0011.htm