Results 1 to 8 of 8
  1. #1
    jjmit1 is offline Novice
    Windows XP Access 2007
    Join Date
    May 2012
    Posts
    5

    Track the order in which user selects items from a list.

    I am trying to find out if there is a way to track the order in which items are selected from a list. I am a dabbler and any keyword searches that I can think of don't bring up what I am looking. So, here is what I am trying to do:

    I have a table of symptoms with 3 fields (ID, Category, and Symptom).
    I have a combo box that will allows the user to pick a category (using select Distinct on category field). I then have a list box populated with all the symptoms that have a category of whatever the user selected.

    The problem I am having is that I need to somehow track the order in which the user selects symptoms and then save that order for future reference and to be printed on a report. The order is important because the most severe symptom needs to be listed first.

    It doesn't matter to me how I accomplish this I just could really use some assistance.


    Thank you in advance!
    JJ

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Just populating an unbound listbox will not facilitate the report output. Must save the selected symptoms into records of a child table associated with another table (patient info?). Can have a field in the child table to save prioritization value. Could be automated with VBA 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.

  3. #3
    jjmit1 is offline Novice
    Windows XP Access 2007
    Join Date
    May 2012
    Posts
    5
    I had a few breakthroughs with my access knowledge and am close to getting this all figured out.
    I still have the symptoms table and the Patient table, but I added a junction table that links patients to Symptoms. I forgot to mention that each patient can have up to 10 symptoms.
    The good news is that I was able to make all of that work. But I still have my sort problem. Here is another attempt at explaining this.

    On the patient Form, I have 1o buttons. 1 for each possible type of symptom.
    When you click the button it loads a new form where the user selects the Symptom Category and picks from a listbox of symptoms. After Selecting the symptoms there is an "add symptoms" button that creates a record in the junction table. And then populates another list box through a query of the junction table. Here are the junction table Fields:

    ID | ID_Patient | ID_Symptoms | Symptomno | Sort


    I am not currently populating the sort field as I am not sure how to tackle allowing the user to rearrange the list. I am thinking that I will have an up and down button to allow them to move the item up and down in the list by changing the sort value in the junction table but I am not sure what I should assign the field initially and how to go about updating it to change the sort value.

    I seem to be making headway at least. I am a lot closer than I was this morning. It is late and I hope this makes sense.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Think 'up/down' buttons is impracticle if what you are imagining is behavior like with a Netflix queue, that would require very complex programming. What can be done relatively easy is assign number or letter value at time record is saved which can be used to control record sort wth the ORDER BY clause in query or with Grouping & Sorting functionality of report. If you want to allow user to manually change the assigned order, then that is also possible.
    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
    jjmit1 is offline Novice
    Windows XP Access 2007
    Join Date
    May 2012
    Posts
    5
    I tried the up down thing last night and it does seem to complicated for my limited access ability. I was then just trying to assign a value to the item in the list. Whether with a Hard coded 1st, 2nd,3rd button or something similar. i am struggling with the code to add to the onclick event. I was trying to modify the code that I was using to populate the listbox to modify the listbox, but I still struggle with the updating records. Can you give me some code assistance to change the sortorder field of the selected item? Here is what I am using:
    Code:
        Dim strSQL As String
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Dim ctl As Control
        Dim varItem As Variant
        Dim currval As Integer
        
        
        Set db = CurrentDb()
        Set rs = db.OpenRecordset("tblSymptomsFunction", dbOpenDynaset)
        For Each varItem In Me.listcombined.ItemsSelected
        rs.Edit
        rs!SortOrder = 1
        
        rs.Update
            Next varItem
        Me.lstcombined.Requery
        Set rs = Nothing
        Set db = Nothing
    I am sure I am missing important portions but I don't know what it is.
    Thank you!

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The code won't work because the recordset includes all records from the table, not just the ones for this particular patient visit and the code doesn't move through the recordset so it is only editing the first record with each loop of the listbox. I am sure there is easier way to accomplish this. I am not sure why you are using a listbox anyway. Why not entering symptom records into subform?

    Do you want to provide project for analysis. Follow instructions at bottom of my post.
    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.

  7. #7
    jjmit1 is offline Novice
    Windows XP Access 2007
    Join Date
    May 2012
    Posts
    5
    I hadn't thought of using a subform. I am working on this now and will add a subform and see what problem I run into. I woudl be happy to provide the project but I have to say I am hesitant as this is my first access project and it is ugly, as I have learned a lot since I started and needs reworked. Adding a subform now and will post the project shortly...
    Thanks

  8. #8
    jjmit1 is offline Novice
    Windows XP Access 2007
    Join Date
    May 2012
    Posts
    5
    Subform wasn't exactly working out for me, but it made it a lot easier to test.
    I managed to make this all work. Although probably not the prettiest or even within best practices.
    When I created the record I assigned the sortorder value from 1 to total numer of symptoms. I then had the add and delete buttons add or subtract 1.1 from the sortorder. It allowed me to move items up and down in the list although I image that there could be an instance that the user would need to click a few times to make it move, but as of right now I am willing to accept that.

    Although I found a solution that worked for me, I appreciate the help. The act of writing this post helped me move forward and get a clearer picture of what I am trying to accomplish.

    Thanks June7.

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

Similar Threads

  1. Track user usage - help
    By scotiwis in forum Access
    Replies: 3
    Last Post: 10-07-2011, 08:45 AM
  2. prefilling items into an order form
    By syscoandrew in forum Forms
    Replies: 5
    Last Post: 09-25-2011, 12:27 PM
  3. Replies: 2
    Last Post: 05-19-2011, 04:07 PM
  4. Replies: 1
    Last Post: 03-07-2011, 10:48 AM
  5. User selects pictures for report
    By NISMOJim in forum Reports
    Replies: 1
    Last Post: 10-29-2010, 03:06 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
  •  
Other Forums: Microsoft Office Forums