Results 1 to 6 of 6

Best Practice for Creating a "Playlist" or "Album" type function within Access

  1. #1
    miketamilio is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2019
    Posts
    3

    Question Best Practice for Creating a "Playlist" or "Album" type function within Access

    Hello All,
    I wonder if you may have some insights on the best practice to create something similar to "playlists" or "albums" from within Access 2016?
    The use case is this:
    A group of professional day traders would like to save screenshots of their trades and assign all kinds of tags and details to each, for further study.
    They would then filter/query on these predefined tags and details.
    However, they may end up filtering a list of 10,000 images down to perhaps 100.
    Then, they would want to manually select individual records from the form view and pick the 10-20 for a album/playlist to save for further studying, as well as assigning this subset of trades to others for follow-up/homework.
    They would want to name the playlist/album, just as you would when doing so from within any music player or photo app, and save that album for later.



    Is there a best practice to this use case?
    For example, I have thought about an underlying boolean table which can be called, and a new column added each time they create a playlist. The playlist, in essence, would then be a single column of yes/no data.
    Then a query might pull from that column of booleans to show only the selected records in a form/report view.

    I am hoping that this community of experts might be able to point me in the right direction!
    Thank you in advance for any help at all!
    Mike
    Click image for larger version. 

Name:	Screenshot_3.jpg 
Views:	16 
Size:	189.8 KB 
ID:	38169

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    46,889
    If you want to have multiple sets of picks saved, then their 'picks' need to be saved to another table: UserPicks. Fields: UserID, ImageID, PickListName.

    The real trick is figuring out how to allow user to make the final selection of 10-20 picks. Common process is to use cascading comboboxes to narrow down to the 100 choices. These comboboxes would be on a form bound to UserPicks. Each item selected in the last combobox would be saved to a record.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    miketamilio is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2019
    Posts
    3
    Thank you - I will think about that in greater detail.
    Is there a way that the picks could be assigned simply by clicking a "Save to Playlist" button, which would then allow the user to select an existing playlist table or create a new table using the approach you mentioned?

    To further explain, the users will already have filtered down their list using standard filters and queries. The very next step would be to manually select from among those to assign to a playlist of their own.
    Thank you again,
    Mike

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    46,889
    Instead of the 100 choices in a combobox, could be a multi-select listbox. Then code can loop through the SelectedItems and write records to table.

    Don't create a table for each playlist, use 1 table and add records as described.

    If this is a single-user db, then can have a yes/no field in the master Images table to allow selection of the 10-20 records on the filtered form. Then code would copy each record ID into UserPickList table. This can be accomplished with an INSERT SELECT action. In VBA looks like:

    CurrentDb.Execute "INSERT INTO UserPickList(UserID, ImageID, PickListName) SELECT " & Me.tbxUserID & ", ImageID, '" & Me.tbxListName & "' FROM Images WHERE PickImage = True"

    Then after that, reset the yes/no field back to all False:

    CurrentDb.Execute "UPDATE Images SET PickImage = False"

    If this is db will have multiple simultaneous users, this technique will result in user conflicts setting the yes/no field to pick images.

    The more 'user friendly', the more code.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    miketamilio is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2019
    Posts
    3

    Lightbulb Thank you!

    Quote Originally Posted by June7 View Post
    Instead of the 100 choices in a combobox, could be a multi-select listbox. Then code can loop through the SelectedItems and write records to table.

    Don't create a table for each playlist, use 1 table and add records as described.

    If this is a single-user db, then can have a yes/no field in the master Images table to allow selection of the 10-20 records on the filtered form. Then code would copy each record ID into UserPickList table. This can be accomplished with an INSERT SELECT action. In VBA looks like:

    CurrentDb.Execute "INSERT INTO UserPickList(UserID, ImageID, PickListName) SELECT " & Me.tbxUserID & ", ImageID, '" & Me.tbxListName & "' FROM Images WHERE PickImage = True"

    Then after that, reset the yes/no field back to all False:

    CurrentDb.Execute "UPDATE Images SET PickImage = False"

    If this is db will have multiple simultaneous users, this technique will result in user conflicts setting the yes/no field to pick images.

    The more 'user friendly', the more code.
    This is very helpful! thank you for taking the time!
    Eventually, this will be multiuser with the Backend on a Local Server.
    In this case, I assume I'll need the code to create temporary files which become are basically the backbone of each playlist.
    Any advice on how best to approach that?

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    46,889
    I don't know what you mean by 'create temporary files'. Why do you think that?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 12
    Last Post: 10-01-2018, 01:40 PM
  2. Suppress "Requery" and/or "Repaint" when running function
    By GraeagleBill in forum Programming
    Replies: 3
    Last Post: 12-07-2017, 11:04 PM
  3. Replies: 3
    Last Post: 02-08-2016, 08:52 AM
  4. Replies: 1
    Last Post: 09-07-2015, 07:00 AM
  5. Replies: 2
    Last Post: 11-04-2011, 01:45 AM

Posting Permissions

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