If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Help displaying a random record in a form--Access newbie
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!! |
#2
|
|||
|
|||
Help displaying a random record in a form--Access newbie
Try this --
To Do: IIF([ID] = Rnd(Left(Right(Now(), 4),1), [CHORE],"") -- Build a little, test a little. "poopyurinal" wrote: 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!! . |
#3
|
|||
|
|||
Help displaying a random record in a form--Access newbie
"poopyurinal" wrote in message
... 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. -- Dirk Goldgar, MS Access MVP Access tips: www.datagnostics.com/tips.html (please reply to the newsgroup) |
#4
|
|||
|
|||
Help displaying a random record in a form--Access newbie
On Nov 17, 12:53*pm, "Dirk Goldgar"
wrote: "poopyurinal" wrote in message ... 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. -- 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! |
#5
|
|||
|
|||
Help displaying a random record in a form--Access newbie
"poopyurinal" wrote in message
... 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. -- Dirk Goldgar, MS Access MVP Access tips: www.datagnostics.com/tips.html (please reply to the newsgroup) |
#6
|
|||
|
|||
Help displaying a random record in a form--Access newbie
On Nov 17, 10:30*pm, "Dirk Goldgar"
wrote: "poopyurinal" wrote in message ... 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. -- 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! |
Thread Tools | |
Display Modes | |
|
|