Random # of records based on user input

[Guest]

Hello

I'm sure this is simple, but I have limited experience with Access and queries. I have a table of several thousand 3-character codes. The table has only one column containing those codes plus a primary key (ID). Here is what I am trying to do

Prompt user to enter a number of codes to selec
Retrieve specified number of codes and display the
Move retrieved codes from Column A (AVAIL) to Column B (USED
Save change
Re-display input box and prompt for input agai
Clicking 'X' to close macro/module (?

My research tells me this is probably a combination of modules, macros and/or queries, but I'm unable to come up with a workable solution so far. Any help would be greatly appreciated.

Thanks
CDConnelly

 

[Ptit Fred]

Hi,

Use the methods in the following article against an Update Query that
updates Column A (AVAIL) to Column B (USED)
ACC2000: How to Create a Parameter In() Statement
http://support.microsoft.com/default.aspx?scid=kb;en-us;210530

To call the query why not use a vbOkCancel button, example:

Function RunTheQuery()
Dim response
response = MsgBox("enter numbers", vbOKCancel, "My Numbers")

If response = vbOK Then
DoCmd.SetWarnings False ' turn off the warnings
DoCmd.OpenQuery "name of the query above"
DoCmd.SetWarnings True ' turn the warnings back on
End If
End Function

I hope this helps! If you have additional questions on this topic, please
respond back to this posting.


Regards,

Eric Butts
Microsoft Access Support

"Microsoft Security Announcement: Have you installed the patch for
Microsoft Security Bulletin MS03-026? If not Microsoft strongly advises
you to review the information at the following link regarding Microsoft
Security Bulletin MS03-026
<http://www.microsoft.com/security/security_bulletins/ms03-026.asp> and/or
to visit Windows Update at <http://windowsupdate.microsoft.com/> to install
the patch. Running the SCAN program from the Windows Update site will help
to insure you are current with all security patches, not just MS03-026."



--------------------
| Thread-Topic: Random # of records based on user input
| thread-index: AcQEdouicgH5XZTlSSKc/ksLEhlPqg==
| X-Tomcat-NG: microsoft.public.access.queries
| From: "=?Utf-8?B?Q0RDb25uZWxseQ==?=" <[email protected]>
| Subject: Random # of records based on user input
| Date: Sun, 7 Mar 2004 11:01:06 -0800
| Lines: 15
| Message-ID: <[email protected]>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.access.queries
| Path: cpmsftngxa06.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.access.queries:192727
| NNTP-Posting-Host: tk2msftcmty1.phx.gbl 10.40.1.180
| X-Tomcat-NG: microsoft.public.access.queries
|
| Hello,

I'm sure this is simple, but I have limited experience with Access and
queries. I have a table of several thousand 3-character codes. The table
has only one column containing those codes plus a primary key (ID). Here is
what I am trying to do:

Prompt user to enter a number of codes to select
Retrieve specified number of codes and display them
Move retrieved codes from Column A (AVAIL) to Column B (USED)
Save changes
Re-display input box and prompt for input again
Clicking 'X' to close macro/module (?)

My research tells me this is probably a combination of modules, macros
and/or queries, but I'm unable to come up with a workable solution so far.
Any help would be greatly appreciated.

Thanks,
CDConnelly
|

 

[John Viescas]

One technique is to use a query to select a "random" number of values.
Assuming that your table has a numeric ID and a Code field, you could select
50 random codes like this:

SELECT Top 50 MyTable.Code
FROM MyTable
ORDER BY Rnd(ID);

In code, you could call Randomize to reset the "seed" value for the Rnd
(Random) function and then open the above recordset to fetch the codes.
Spit out the found codes into a temp table for display and mark them "used."

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

Hello,

I'm sure this is simple, but I have limited experience with Access and

queries. I have a table of several thousand 3-character codes. The table has
only one column containing those codes plus a primary key (ID). Here is what
I am trying to do:

Prompt user to enter a number of codes to select
Retrieve specified number of codes and display them
Move retrieved codes from Column A (AVAIL) to Column B (USED)
Save changes
Re-display input box and prompt for input again
Clicking 'X' to close macro/module (?)

My research tells me this is probably a combination of modules, macros

and/or queries, but I'm unable to come up with a workable solution so far.
Any help would be greatly appreciated.

 

[Guest]

This sounds like a reasonable solution - can you elaborate a bit on how I would use the Randomize function to do this

Thanks

----- John Viescas wrote: ----

One technique is to use a query to select a "random" number of values
Assuming that your table has a numeric ID and a Code field, you could selec
50 random codes like this

SELECT Top 50 MyTable.Cod
FROM MyTabl
ORDER BY Rnd(ID)

In code, you could call Randomize to reset the "seed" value for the Rn
(Random) function and then open the above recordset to fetch the codes
Spit out the found codes into a temp table for display and mark them "used.

--
John Viescas, autho
"Microsoft Office Access 2003 Inside Out
"Running Microsoft Access 2000
"SQL Queries for Mere Mortals
http://www.viescas.com
(Microsoft Access MVP since 1993

Hello

queries. I have a table of several thousand 3-character codes. The table ha
only one column containing those codes plus a primary key (ID). Here is wha
I am trying to do

Retrieve specified number of codes and display the
Move retrieved codes from Column A (AVAIL) to Column B (USED
Save change
Re-display input box and prompt for input agai
Clicking 'X' to close macro/module (?

and/or queries, but I'm unable to come up with a workable solution so far
Any help would be greatly appreciated

 

[John Viescas]

Try the SQL I gave you. Every time you run the query, you should get a
different set of 50 "random" codes. You wouldn't need to copy the selected
code to a "used" column, but you could use a "used" yes/no field that you
flag and then elimnate those "used" ones on subsequent runs. I recommended
copying the "selected" set to a working table because the set will change
each time you reopen the recordset. Your code might look like:

Dim db As DAO.Database, rstRand As DAO.Recordset, rstTemp As DAO.Recordset

' Point to this database
Set db = CurrentDb
' Reset the seed
Randomize

' Clear out the working table
db.Execute "DELETE * FROM ztblWork", dbFailOnError
' Get the first set of random records
' You could also prompt the user here for the number of random rows
' and insert into the following SQL.
Set rstRand = db.OpenRecordset("SELECT Top 50 Code, Used FROM MyTable " &
_
"WHERE Used = 0 ORDER BY Rnd(ID)"
' Open the output recordset
Set rstTemp = db.OpenRecordset("ztblWork", dbOpenDynaset, dbAppendOnly)
' Loop until no more records
Do Until rstRand.EOF
' Copy the selected record
rstTemp.AddNew
rstTemp!ID = rstRand!ID
rstTemp!Code = rstRand!Code
rstTemp.Update
' Mark this one used
rstRand.Edit
rstRand!Used = -1
rstRand.Update
' Get the next record
rstRand.MoveNext
Loop
' Clean up
rstRand.Close
rstTemp.Close
' At this point you could open a report or form bound to ztblWork to
display the result


Does that help?

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

This sounds like a reasonable solution - can you elaborate a bit on how I

would use the Randomize function to do this?