Random Day

[Guest]

I am designing this dBase for some Special Ed students. They need much more repetition to help remember things so this dBase will help them achieve that

I have a form, frmDayOfWeek, that has two fields on it, Today and DayAfter. I want a random day of the week to appear in the field Today, stored so I can use it on the report, and then the students will type in whatever the next day of the week is in the field DayAfter

I have a table, tblDaysOfWeek, that has and ID field and a Day field. The ID range from 1 to 7 and the correspont with the day of the week. 1=Sunday, 2=Monday, 3=Wednesday, etc

I thought the code would generate a random number, go get the day of the week from the tblDaysOfWeek, and put that day in the field Today on the current form

With this code, am I in the right ballpark or is there a better way to do what I want

'Highest form numbe
Const lngcUpperBound =

'Lowest form numbe
Const lngcLowerBound =

Dim lngFormNumber As Lon
Dim strRandomDay As Strin

'Requires reference to Microsoft DAO 3.6 Object Library (or 3.51 for Access 97)
Dim db As DAO.Databas
Dim rst As DAO.Recordse

'Generate a random number in the specified range. See 'Rnd Function' in the help file for details
Randomize Time
lngFormNumber = Int((lngcUpperBound - lngcLowerBound + 1) * Rnd + lngcLowerBound

'Open a recordset to get the form name corresponding to the randomnumber
Set db = CurrentD
Set rst = db.OpenRecordset("SELECT [Day] FROM tblDaysofWeek WHERE [Day] = Weekday(" & lngFormNumber & ")"

---> HOW DO I GET THE RANDOM DAY INTO THE TODAY FIELD ON THE FORM

Thanks
RI

 

[Douglas J. Steele]

The Weekday function expects to find a date, and what you're passing it is
an integer between 1 and 7. Not only that, but you need to compare your
number to the ID in your tblDaysOfWeek table, not to Day.

Try

Set rst = db.OpenRecordset("SELECT [Day] FROM tblDaysofWeek WHERE ID = "
& lngFormNumber)

strRandomDay = rst![Day]


Realistically, though, opening a recordset may be overkill in this case.
DLookup may be sufficient:

'Highest form number
Const lngcUpperBound = 7

'Lowest form number
Const lngcLowerBound = 1

Dim lngFormNumber As Long
Dim strRandomDay As String

'Generate a random number in the specified range. See 'Rnd Function' in
the help file for details.
Randomize Timer
lngFormNumber = Int((lngcUpperBound - lngcLowerBound + 1) * Rnd +
lngcLowerBound)

strRandomDay = DLookup("[Day]", "tblDaysOfWeek", "ID = " &
lngFormNumber)

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

I am designing this dBase for some Special Ed students. They need much

more repetition to help remember things so this dBase will help them achieve
that.

I have a form, frmDayOfWeek, that has two fields on it, Today and

DayAfter. I want a random day of the week to appear in the field Today,
stored so I can use it on the report, and then the students will type in
whatever the next day of the week is in the field DayAfter.

I have a table, tblDaysOfWeek, that has and ID field and a Day field. The

ID range from 1 to 7 and the correspont with the day of the week. 1=Sunday,
2=Monday, 3=Wednesday, etc.

I thought the code would generate a random number, go get the day of the

week from the tblDaysOfWeek, and put that day in the field Today on the
current form.

With this code, am I in the right ballpark or is there a better way to do what I want?

'Highest form number
Const lngcUpperBound = 7

'Lowest form number
Const lngcLowerBound = 1

Dim lngFormNumber As Long
Dim strRandomDay As String

'Requires reference to Microsoft DAO 3.6 Object Library (or 3.51 for Access 97).
Dim db As DAO.Database
Dim rst As DAO.Recordset

'Generate a random number in the specified range. See 'Rnd Function' in the help file for details.
Randomize Timer
lngFormNumber = Int((lngcUpperBound - lngcLowerBound + 1) * Rnd + lngcLowerBound)

'Open a recordset to get the form name corresponding to the randomnumber.
Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT [Day] FROM tblDaysofWeek WHERE

[Day] = Weekday(" & lngFormNumber & ")")

 

[Douglas J. Steele]

How were you planning on doing that with your code? I gave you complete code
to replace what you posted.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

I am still a rookie at the VB stuff. Where do I place the DLookup

expression to get it to place the random day in the Today field on
frmDayOfWeek?

Thanks,
RIP

----- Douglas J. Steele wrote: -----

Realistically, though, opening a recordset may be overkill in this case.
DLookup may be sufficient:

'Highest form number
Const lngcUpperBound = 7

'Lowest form number
Const lngcLowerBound = 1

Dim lngFormNumber As Long
Dim strRandomDay As String

'Generate a random number in the specified range. See 'Rnd Function' in
the help file for details.
Randomize Timer
lngFormNumber = Int((lngcUpperBound - lngcLowerBound + 1) * Rnd +
lngcLowerBound)

strRandomDay = DLookup("[Day]", "tblDaysOfWeek", "ID = " &
lngFormNumber)

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

I am designing this dBase for some Special Ed students. They need

much
more repetition to help remember things so this dBase will help them achieve

that.

DayAfter. I want a random day of the week to appear in the field Today,
stored so I can use it on the report, and then the students will type in
whatever the next day of the week is in the field DayAfter. field. The
ID range from 1 to 7 and the correspont with the day of the week. 1=Sunday,
2=Monday, 3=Wednesday, etc. of the
week from the tblDaysOfWeek, and put that day in the field Today on the
current form. way to do
what I want? 3.51 for
Access 97).

Dim db As DAO.Database
Dim rst As DAO.Recordset

Function' in
the help file for details.

Randomize Timer
lngFormNumber = Int((lngcUpperBound - lngcLowerBound + 1) * Rnd

+

lngcLowerBound)

'Open a recordset to get the form name corresponding to the

randomnumber.
Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT [Day] FROM tblDaysofWeek

WHERE
[Day] = Weekday(" & lngFormNumber & ")")

 

[Pieter Wijnen]

don't use domain aggregate (DLookup, DSUM..) functions they are notoriously
slow....

Pieter

I am still a rookie at the VB stuff. Where do I place the DLookup

expression to get it to place the random day in the Today field on
frmDayOfWeek?

Thanks,
RIP

----- Douglas J. Steele wrote: -----

Realistically, though, opening a recordset may be overkill in this case.
DLookup may be sufficient:

'Highest form number
Const lngcUpperBound = 7

'Lowest form number
Const lngcLowerBound = 1

Dim lngFormNumber As Long
Dim strRandomDay As String

'Generate a random number in the specified range. See 'Rnd Function' in
the help file for details.
Randomize Timer
lngFormNumber = Int((lngcUpperBound - lngcLowerBound + 1) * Rnd +
lngcLowerBound)

strRandomDay = DLookup("[Day]", "tblDaysOfWeek", "ID = " &
lngFormNumber)

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

I am designing this dBase for some Special Ed students. They need

much
more repetition to help remember things so this dBase will help them achieve

that.

DayAfter. I want a random day of the week to appear in the field Today,
stored so I can use it on the report, and then the students will type in
whatever the next day of the week is in the field DayAfter. field. The
ID range from 1 to 7 and the correspont with the day of the week. 1=Sunday,
2=Monday, 3=Wednesday, etc. of the
week from the tblDaysOfWeek, and put that day in the field Today on the
current form. way to do
what I want? 3.51 for
Access 97).

Dim db As DAO.Database
Dim rst As DAO.Recordset

Function' in
the help file for details.

Randomize Timer
lngFormNumber = Int((lngcUpperBound - lngcLowerBound + 1) * Rnd

+

lngcLowerBound)

'Open a recordset to get the form name corresponding to the

randomnumber.
Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT [Day] FROM tblDaysofWeek

WHERE
[Day] = Weekday(" & lngFormNumber & ")")

 

[Guest]

The code does great and gets the random day of the week, I just can't figure out how to get that random day to appear in the field Today on the form called frmDayOfWeek

I was trying to get the other code to do the same, but hadn't quite gotten to that part yet as that code kept hanging occassionally

Thanks
RIP

 

[Douglas J. Steele]

If you read the original post, you'll see that there are only 7 rows in
tblDaysofWeek. Somehow I don't think DLookup is going to be an issue!

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

don't use domain aggregate (DLookup, DSUM..) functions they are notoriously
slow....

Pieter

I am still a rookie at the VB stuff. Where do I place the DLookup

expression to get it to place the random day in the Today field on
frmDayOfWeek?

Thanks,
RIP

----- Douglas J. Steele wrote: -----

Realistically, though, opening a recordset may be overkill in this case.
DLookup may be sufficient:

'Highest form number
Const lngcUpperBound = 7

'Lowest form number
Const lngcLowerBound = 1

Dim lngFormNumber As Long
Dim strRandomDay As String

'Generate a random number in the specified range. See 'Rnd Function' in
the help file for details.
Randomize Timer
lngFormNumber = Int((lngcUpperBound - lngcLowerBound + 1) * Rnd +
lngcLowerBound)

strRandomDay = DLookup("[Day]", "tblDaysOfWeek", "ID = " &
lngFormNumber)

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

I am designing this dBase for some Special Ed students. They

need

much
more repetition to help remember things so this dBase will help

them

achieve and
DayAfter. I want a random day of the week to appear in the field Today,
stored so I can use it on the report, and then the students will

type

in
whatever the next day of the week is in the field DayAfter. field. The
ID range from 1 to 7 and the correspont with the day of the week. 1=Sunday,
2=Monday, 3=Wednesday, etc.

day
of the

week from the tblDaysOfWeek, and put that day in the field Today on the
current form. way to do
what I want? 3.51 for
Access 97). Function' in
the help file for details.

Rnd

+

lngcLowerBound)
'Open a recordset to get the form name corresponding to the randomnumber.
Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT [Day] FROM tblDaysofWeek

WHERE
[Day] = Weekday(" & lngFormNumber & ")")

---> HOW DO I GET THE RANDOM DAY INTO THE TODAY FIELD ON THE FORM?
Thanks,
RIP

 

[Douglas J. Steele]

If all you want is to put the value on the form, change

strRandomDay = DLookup("[Day]", "tblDaysOfWeek", "ID = " &
lngFormNumber)

to

Me![Today] = DLookup("[Day]", "tblDaysOfWeek", "ID = " &
lngFormNumber)

If you need the value in a variable for further processing in that module,
you can add the following

Me![Today] = strRandomDay

after

strRandomDay = DLookup("[Day]", "tblDaysOfWeek", "ID = " &
lngFormNumber)


This assumes that your code is associated with the form, not in a separate
Module. If it's in a separate module, try using
Forms("frmDayOfWeek")![Today] instead of Me![Today] (and note that it'll
only work if the form's already open)

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

The code does great and gets the random day of the week, I just can't

figure out how to get that random day to appear in the field Today on the
form called frmDayOfWeek.

I was trying to get the other code to do the same, but hadn't quite gotten

to that part yet as that code kept hanging occassionally.

 

[Guest]

Thank You!!!!!!

Man I was about to stab myself. This thing was killing me

RIP

 

[Pieter Wijnen]

Agreed,
It was merely meant as a general advise .-)
(they do tend to slow up Reports a lot when used there - far better to put
code for the format_events)

Pieter

If you read the original post, you'll see that there are only 7 rows in
tblDaysofWeek. Somehow I don't think DLookup is going to be an issue!

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

don't use domain aggregate (DLookup, DSUM..) functions they are notoriously
slow....

Pieter


expression to get it to place the random day in the Today field on
frmDayOfWeek? in
this case.

Rnd

+

lngcLowerBound)

strRandomDay = DLookup("[Day]", "tblDaysOfWeek", "ID = " &
lngFormNumber)

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


I am designing this dBase for some Special Ed students. They

need

much
more repetition to help remember things so this dBase will help

them

achieve
that.
I have a form, frmDayOfWeek, that has two fields on it, Today and
DayAfter. I want a random day of the week to appear in the field Today,
stored so I can use it on the report, and then the students will

type

in
whatever the next day of the week is in the field DayAfter.
I have a table, tblDaysOfWeek, that has and ID field and a Day field. The
ID range from 1 to 7 and the correspont with the day of the week. 1=Sunday,
2=Monday, 3=Wednesday, etc.
I thought the code would generate a random number, go get the

day
of the

week from the tblDaysOfWeek, and put that day in the field Today

on
the

current form.
With this code, am I in the right ballpark or is there a

better
way to do

what I want?
'Highest form number
Const lngcUpperBound = 7
'Lowest form number
Const lngcLowerBound = 1
Dim lngFormNumber As Long
Dim strRandomDay As String
'Requires reference to Microsoft DAO 3.6 Object Library

(or
3.51 for

Access 97).
Dim db As DAO.Database
Dim rst As DAO.Recordset
'Generate a random number in the specified range. See 'Rnd Function' in
the help file for details.
Randomize Timer
lngFormNumber = Int((lngcUpperBound - lngcLowerBound + 1) *

Rnd

+
lngcLowerBound)
'Open a recordset to get the form name corresponding to the
randomnumber.
Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT [Day] FROM tblDaysofWeek WHERE
[Day] = Weekday(" & lngFormNumber & ")")
---> HOW DO I GET THE RANDOM DAY INTO THE TODAY FIELD ON THE FORM?
Thanks,
RIP