Help displaying a random record in a form--Access newbie

[poopyurinal]

Hello,

I am new to Access, so if anyone can help me, please assume I know
absolutely nothing! I currently have a table with two columns, ID and
CHORE. In each row, the ID number corresponds to a chore that I have
entered. I would like to create a form with a button and a text field
such that when I press the button, a random chore is displayed in the
text field (supposed to make chores more "fun" for my son). There is
no need to remove this record from future clicks (the same record can
come up over and over without any problem for me) Can anyone explain
to me (in painstaking detail) how to do this?

Thanks!!

 

[KARL DEWEY]

Try this --
To Do: IIF([ID] = Rnd(Left(Right(Now(), 4),1), [CHORE],"")

 

[Dirk Goldgar]

Hello,

I am new to Access, so if anyone can help me, please assume I know
absolutely nothing! I currently have a table with two columns, ID and
CHORE. In each row, the ID number corresponds to a chore that I have
entered. I would like to create a form with a button and a text field
such that when I press the button, a random chore is displayed in the
text field (supposed to make chores more "fun" for my son). There is
no need to remove this record from future clicks (the same record can
come up over and over without any problem for me) Can anyone explain
to me (in painstaking detail) how to do this?

Suppose your form has two controls, a command button named "cmdPickChore"
and a text box "txtChore" for displaying the random chore that is selected.
Suppose also that your table is named "Chores", and has the fields you
described above. Then the code for the button's Click event could look like
this:

'------ start of code ------
Private Sub cmdPickChore_Click()

Randomize

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("Chores", dbOpenDynaset)
With rs

' Make sure we have at least one chore!
If .EOF Then

MsgBox _
"Lucky you -- there are no chores to do!", _
vbOKOnly, _
"No Chores Defined"

Else

' Okay, we have one or more chores.
' Make sure we know how many.

.MoveLast

' Pick a random number between 0 and the number
' of chores (minus 1), and position the recordset
' to the record whose position corresponds to that
' number.

.AbsolutePosition = CLng(Rnd() * .RecordCount)

' Get the name of that chore and display it in
' our text box.

Me.txtChore = !Chore

End If

.Close

End With

Set rs = Nothing

End Sub
'------ end of code ------

Change the names in the above code as needed to correspond to the names of
your objects.

To enter this code for the command button, open the form in design view,
click on the button, bring up its property sheet, go to the Event tab, click
on the On Click property line, choose [Event Procedure] from the dropdown,
then click the "build" button (caption "...") at the end of the line.
You'll be placed in the VBA Editor, within an initial procedure stub
reading:

Private Sub cmdPickChore_Click()

End Sub

Paste my code (with names corrected) in place of that code. Click Debug ->
Compile to compile the code and ensure that there are no mistakes. Then
close the VBA window and save the form.

 

[poopyurinal]

Hello,

I am new to Access, so if anyone can help me, please assume I know
absolutely nothing!  I currently have a table with two columns, ID and
CHORE.  In each row, the ID number corresponds to a chore that I have
entered.  I would like to create a form with a button and a text field
such that when I press the button, a random chore is displayed in the
text field (supposed to make chores more "fun" for my son).  There is
no need to remove this record from future clicks (the same record can
come up over and over without any problem for me)  Can anyone explain
to me (in painstaking detail) how to do this?

Suppose your form has two controls, a command button named "cmdPickChore"
and a text box "txtChore" for displaying the random chore that is selected.
Suppose also that your table is named "Chores", and has the fields you
described above.  Then the code for the button's Click event could looklike
this:

'------ start of code ------
Private Sub cmdPickChore_Click()

    Randomize

    Dim rs As DAO.Recordset

    Set rs = CurrentDb.OpenRecordset("Chores", dbOpenDynaset)
    With rs

        ' Make sure we have at least one chore!
        If .EOF Then

            MsgBox _
                "Lucky you -- there are no chores to do!", _
                vbOKOnly, _
                "No Chores Defined"

        Else

            ' Okay, we have one or more chores.
            ' Make sure we know how many.

            .MoveLast

            ' Pick a random number between 0 and the number
            ' of chores (minus 1), and position the recordset
            ' to the record whose position corresponds to that
            ' number.

            .AbsolutePosition = CLng(Rnd() * .RecordCount)

            ' Get the name of that chore and display it in
            ' our text box.

            Me.txtChore = !Chore

        End If

        .Close

    End With

    Set rs = Nothing

End Sub
'------ end of code ------

Change the names in the above code as needed to correspond to the names of
your objects.

To enter this code for the command button, open the form in design view,
click on the button, bring up its property sheet, go to the Event tab, click
on the On Click property line, choose [Event Procedure] from the dropdown,
then click the "build" button (caption "...") at the end of the line.
You'll be placed in the VBA Editor, within an initial procedure stub
reading:

    Private Sub cmdPickChore_Click()

    End Sub

Paste my code (with names corrected) in place of that code.  Click Debug ->
Compile to compile the code and ensure that there are no mistakes.  Then
close the VBA window and save the form.

--
Dirk Goldgar, MS Access MVP
Access tips:www.datagnostics.com/tips.html

(please reply to the newsgroup)

I tried this as you suggested, and thank you for being so thorough!
When I try to compile the VB script, I get the error:

Compile error:

User-defined type not defined

and the following text is highlighted in the script: "rs As
DAO.Recordset"


You also said something about changing the names in the script, but
I'm not sure what you meant.

Thanks for your help!

 

[Dirk Goldgar]

I tried this as you suggested, and thank you for being so thorough! When
I try to compile the VB script, I get the error:

Compile error:

User-defined type not defined

and the following text is highlighted in the script: "rs As DAO.Recordset"

That implies that you don't have a reference set to the DAO object library.
Here's how to set it:

1. While viewing the code in the VB Editor environment, click menu items
Tools -> References...

2. In the References dialog, locate the reference named "MIcrosoft DAO 3.6
Object Library".

3. Put a check mark in the box next to it.

4. Click the dialog's OK button.

If you try compiling now, you should not get the error.

You also said something about changing the names in the script, but I'm
not sure what you meant.

The code I posted assumed the following names of objects:

"cmdPickChore" - the name of the command button

"txtChore" - the name of the text box where the selected chore will be
displayed

"Chores" - the name of the table containing the list of possible chores

"Chore" - the name of the text field, in the Chores table, that holds
the chore name/description.

If any of these names is not correct, you should change the code to use the
correct name for that object. Or, of course, you could change the name of
the object to match the code, if that's more convenient.

 

[poopyurinal]

That implies that you don't have a reference set to the DAO object library.
Here's how to set it:

1. While viewing the code in the VB Editor environment, click menu items
Tools -> References...

2. In the References dialog, locate the reference named "MIcrosoft DAO 3.6
Object Library".

3. Put a check mark in the box next to it.

4. Click the dialog's OK button.

If you try compiling now, you should not get the error.


The code I posted assumed the following names of objects:

    "cmdPickChore" - the name of the command button

    "txtChore" - the name of the text box where the selected chore will be
displayed

    "Chores" - the name of the table containing the list of possible chores

    "Chore" - the name of the text field, in the Chores table, that holds
the chore name/description.

If any of these names is not correct, you should change the code to use the
correct name for that object.  Or, of course, you could change the nameof
the object to match the code, if that's more convenient.

--
Dirk Goldgar, MS Access MVP
Access tips:www.datagnostics.com/tips.html

(please reply to the newsgroup)

That worked perfectly! Thanks for your in depth help!