Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Desstro's Avatar
    Desstro is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2010
    Posts
    185

    Random Records From Query Shown On a Form - Button To Accept Each Random Selection

    I have a query that pulls random records (top 15 values).



    I would like to build a user form (called "frmAvailable") that can do the following:


    • On Form Load - I would like the query ("qryAvailable") to refresh in the background and then post the results into text boxes on the form. For simplicity let's call those fields in the table field 1, 2, 3, and 4. (each record will then show 4 different fields from the table in the text boxes).


    • I would like there to be a button that refreshes the above evolution (bullet 1) if the end user is unhappy with the results showing.


    • I would like there to be 15 buttons (one button next to each record result) that when pushed will add the system date in a field in the table for that record. ("tblMasterData" and field name "DatePreviouslyChosen")


    • Also on the 15 buttons, if pressed, the data will stay in the text boxes on the form until the form is closed that way if the end user accepts 10 of the 15 results they can press the other button to have the remaining 5 results shuffle from the re-query (Bullet 2).


    • When the form closes, obviously all data is cleared.



    I am having a hard time wrapping my head around how to do all this.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I would have a form in continuous view based on the query. Bullet 2 just needs to requery the form. A button in the detail section accomplishes bullet 3, with code like:

    Me.DatePreviouslyChosen = Now()


    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Desstro's Avatar
    Desstro is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2010
    Posts
    185
    pbaldy,

    Thanks... yeah that is the easy part.
    Can you help me out with bullet 4? That is my main sticking point.... the other bullets were to really paint the picture.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    FYI
    • a form bound to a query or table gets its records when the form Loads. What you wrote suggests you already have the query open, when you shouldn't, so no need to "refresh" the query
    • I don't see the need for an "unhappy" button. You will get the same results each time you click it unless something changes, which isn't apparent wrt this bullet
    • as noted, if you have a continuous (or datasheet) with a bound date field, you'll get the date picker control when you click on that field - no buttons needed.
    • data is saved when user takes any action that forces a save, including navigating off record, closing or requerying the form. Seems like only 1 button would be required to effect a save without closing/navigating.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    Desstro's Avatar
    Desstro is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2010
    Posts
    185
    You may not see the need for an "unhappy" button but I do. Refreshing the query will not return the same results, I have tested it. It is possible that I may get the same result every so often because it is truly a random pull from the data set but with hundreds of records to pull from the chances of a refresh calling up the exact same record(s) is pretty slim.

    So I'l ask again, how can I, once a record has been chosen as acceptable, lock that choice in, refresh the query and have only those records not accepted be changed by the query refresh? Which again is a query that pulls random records in the table. Refreshing it changes the sort, thus changing the results.

    I apologize for not explaining earlier that the query has an expression that calls for 15 random records.

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Quote Originally Posted by Desstro View Post
    pbaldy,

    Thanks... yeah that is the easy part.
    Can you help me out with bullet 4? That is my main sticking point.... the other bullets were to really paint the picture.
    I guess I was thrown off by "I would like" in every bullet...

    Can the query include a criteria for the date field populated by bullet 3?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    So I'l ask again, how can I, once a record has been chosen as acceptable, lock that choice in
    If doing this changes the criteria used to retrieve the record, then I guess it won't be reloaded, thus your results should change as you expect. The answer as to how is suggested in my last bullet. There are a few ways to cause the record to be saved, depending on the nature of your form, which you haven't said much about. If it's a continuous or data sheet, simply moving off the record will do the trick (such as selecting a different one). If it's a navigation form, going to another record via navigation controls will save a record. If there can't be such record selection/navigation or it would be unreliable, a command button captioned Save can force the save, provided the form fields don't violate data constraints such as not providing data for a table field that is required. Simply, that code can be as little as
    Me.Dirty = False
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    Desstro's Avatar
    Desstro is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2010
    Posts
    185
    I know you guys are trying to help and I appreciate it very much but, for some reason I am just not wrapping my head around this.... I have attached an example.
    Table
    Query
    Form

    On the form I put some dummy buttons...
    In the header is a "REFRESH QUERY" Button - to be used to run query and refresh data on the form
    Next to each record there is a "LOCK IT IN" Button - to be used to add the current system date to the field in the table "Date Previously Chosen"

    If the end user "Locks 5 records in" and then pushes the "REFRESH QUERY", I would like the 5 that were "Locked In" to remain on the form and the text boxes "greyed out" while the other 10 change based on the query refresh results.

    The "Date Available For Re-Use" field is automatically populated based on the value in "Date Previously Chosen"

    The query only returns a random 15 results where "Date Available For Re-Use" <=Date().
    Attached Files Attached Files

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Since they were the easy part, I'm surprised bullets 1-3 weren't all done. I think I've got it working with a UNION query that joins songs with today's date in the date previously chosen field (with 1 in the random field) and your existing query. The form is based on a query that gets the top 15 from that. Anything with today's date is locked in, since it has a 1 in the random sorting field.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #10
    Desstro's Avatar
    Desstro is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2010
    Posts
    185
    pbaldy,

    Bullet one is done. When you call the form it runs the query.

    Soooo.... did you mean to attach what you think you got working and forgot to? You know so that I might be able to take a look at it? I'm as you might have guessed somewhat of a noob at Access and I am more of a visual learner as well. It would be great if you could attach what it is you think you have working?

  11. #11
    Desstro's Avatar
    Desstro is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2010
    Posts
    185
    pbaldy,

    Can you please attach an example? I have never worked with a union query. I have been reading about it and am starting to understand the concept but am struggling to put it together. I don't know if I even have a strong suit but sql certainly is not it if I do.

  12. #12
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Here's the query:

    SELECT Master_Data.ID, Master_Data.[Master Song List], Master_Data.[Song Length], Master_Data.BPM, Master_Data.Comments, Master_Data.[Date Available For Re-Use], 1 AS SortField
    FROM Master_Data
    WHERE Master_Data.[Date Previously Chosen]=Date()
    UNION ALL
    SELECT Master_Data.ID, Master_Data.[Master Song List], Master_Data.[Song Length], Master_Data.BPM, Master_Data.Comments, Master_Data.[Date Available For Re-Use], Rnd([Master_Data]![ID]) AS Expr1
    FROM Master_Data
    WHERE Master_Data.[Date Available For Re-Use]<=Date()
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  13. #13
    Desstro's Avatar
    Desstro is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2010
    Posts
    185
    pbaldy,

    Awesome! Thanks!! I need to read a bit more on Union Queries. So I got everything set up and all is working as I envisioned except for one thing, and it is really the main bit I am looking to have happen. I imagine you are getting bored with helping me on this but there is just one last thing and I think this mystery will be solved.

    So when I click the lock it in button for the first record it writes the system date in the date previously chosen field as intended.
    When I hit the refresh button it refreshes the entire form.

    I would like it so that if I "Lock it in" and refresh the form, the records I have locked in stay put on the form while the rest change. How do I make that happen?

    I have reattached a revised example with your suggestions so far.
    Attached Files Attached Files

  14. #14
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Um, I don't see you using the union query for anything. I used it as the base of the form, indirectly via SQL that pulls the top 15 from it. Part of the union query pulls records where that date is today. With the "SortField", that locks it in.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  15. #15
    Desstro's Avatar
    Desstro is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2010
    Posts
    185
    So.... I am honestly trying to learn and figure this out but I am still at a loss for what you are trying to explain to me. "...used it as the base of the form..." Are you saying that the record source for the form needs to be the union query? I tried that to no avail. I feel like I am missing just 1 or 2 key things here.

    Can you please explain to me step by step what I need to do with my last uploaded example? Are there fields that I need to add to the query / union query / form?


    UPDATE** - I have tried everything I can think to try. I have spent all day on this and am going crazy!! I feel like my query "Avilable_Songs" needs to be coming from Table "querytest1"? Does there need to be any criteria for SortField in the query? You asked earlier in this thread..."Can the query include a criteria for the date field populated by bullet 3?" What did you mean by that?

    Is there anyway I can get you to attach a working example so I can dissect it? I am just about going out of my head here.
    Last edited by Desstro; 04-01-2018 at 07:47 PM.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Query Random records based on criteria
    By Grant Shea in forum Access
    Replies: 31
    Last Post: 09-09-2016, 03:10 PM
  2. Replies: 2
    Last Post: 05-29-2015, 11:16 AM
  3. Replies: 2
    Last Post: 03-05-2015, 01:43 PM
  4. Random row selection without selecting twice
    By timosilver in forum Access
    Replies: 7
    Last Post: 03-03-2012, 07:37 PM
  5. Random Default Value Combo Box Selection
    By sking89 in forum Programming
    Replies: 2
    Last Post: 01-02-2012, 12:17 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums