Page 1 of 4 123 ... LastLast
Results 1 to 15 of 57
  1. #1
    rlbroerman
    Guest

    list box in form (access 2007)

    I am trying to insert a list box into “frmPatients” with option to save multiple values using check boxes. tblPainLevel and tblCurrentPain have multiple valued check boxes. My attempt is shown in the lower right corner of the attached “frmPatients”. I have inserted a list box into the form but it doesn’t have the check boxes or does it let me save multiple values in a record. Like the tables do. Can you help, please?
    Rick B
    Attached Files Attached Files

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: list box in form (access 2007)

    Hi there

    Is there a chance you could save the database in 2003 format and zip it again?
    Jerry

  3. #3
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: list box in form (access 2007)

    The first step is you are going to need to modify the listbox to a multi-select listbox to be able to select multiple values.

    In the form design view, select the properties of the listbox, go to the other tab and set the Multi Select row to extended. This permits you to select multiple values in your listbox. You still need to determine how to get the data where you want.

    I am, unfortunately, lost in your description of where you want to write the selections to. Once you make your selections, do you plan to store them in a table linked to the patient ID or other?

    This should give you a start. If you search the forum on multi select list boxes you will find many examples of how to get the data selected and store them. Post back if you are stuck.
    Regards,

    Gary
    (It's been a while!)

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: list box in form (access 2007)

    I think the problem here, which I have not yet had a chance to investigate properly, is that 2007 multivalue fields are being used. Converting back to 2003 is not an option if I am correct.
    Regards
    John



  5. #5
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    387
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: list box in form (access 2007)

    I don't know if this helps but to get all the values, of lines that are selected in a list box, in earlier version to 2007 this code works
    Dim varIndex As Variant
    Dim strFilter As String

    For Each varIndex In lstBox.ItemsSelected
    strFilter = strFilter & ", " & lstBox.ItemData(varIndex)
    ' if the ID field is a string use this line instead of the previous line
    ' strFilter = strFilter & ", """ & lstBox.ItemData(varIndex) & """"
    Next varIndex

    in this example it is just loops through the rows and concatenating the values of the selected lines into a string to pass to a report filter but you could write each value to a table.
    In this example the multi select property of the list box is set to normal.

  6. #6
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: list box in form (access 2007)

    John,

    After re-reading the post, you are absolutely correct. I am going to post a link to the original post that has directions on how to use multivalue fields.
    Regards,

    Gary
    (It's been a while!)

  7. #7
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: list box in form (access 2007)

    Rick,

    Check out the following link for a review on creating multivalue fields. HTH http://office.microsoft.com/en-us/access/H...=CH100645681033
    Regards,

    Gary
    (It's been a while!)

  8. #8
    rlbroerman
    Guest

    Re: list box in form (access 2007)

    I can do this to table but I can't get to it form a form.
    Rick b

  9. #9
    rlbroerman
    Guest

    Re: list box in form (access 2007)

    multi value list box is new to 2007 therefore a 2003 would not have it.
    Rick b

  10. #10
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: list box in form (access 2007)

    I have had a look in 2007, and I think we need to take a step back to clarify your table structure, and what you want to achieve, before we look at how to do it with a form and list box.

    tblPainLevel is just there as a lookup table I believe. It does not need multivalues, and does not need to lookup a value list that is the same as its own contents. So change this table so it is just a plain list of values.

    What is tblCurrentPain trying to record? and why do you want multiple values? At the moment it seems that each patient has multiple records in tblcurrentpain, and each of these has multiple values. If you really need multiple records, then I imagine you need some other field to distinguish them, such as a date field.
    I don't understand this - which does not make it wrong - but I want to be clear about it.

    As you know multivalue fields are new in 2007. I suspect that many developers are reluctant to use them (I certainly am) because the idea of multivalue fields is not consistent with relational design. I see them as a shortcut for people who can't be bothered with (or don't understand) proper design. They might work, but I would be worried that I might find later that I am restricted with what I can do with the data contained in the multivalued field.
    Regards
    John



  11. #11
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: list box in form (access 2007)

    Rick,

    John brings up some good points. In looking at your current design, you input patient data into TblPatients. Then you use a one to many relationship to track visits in the table named Visits that is linked back to the patient table via the SocSecNo. Your table PainLevel is used as a lookup to select the PainLevel of the patient for the current visit.

    If I understand your request, you want the ability to select one or many Pain Levels for the current patient visit and want to store the data selected. (Correct?)

    The question is how to do this? Do you want to use a MultiValue field as the selection that stores the data in a string as choice 1, choice2, choice3? I would not do it this way as it would then be a little more difficult to get a count on patients with a particular pain as the stored data is stored as part of a string. If using a multiselect listbox, you can loop through the values and store the data in a table that links back to the visit table via the visit number and store each pain as a separate value. It would then be easy to use the "pains" selected for further analysis or querying.

    Anyway, back to your question. How do you want to obtain the data - via a multivalue listing that stores the data in a string or a multiselect listbox that would store the individual records in a new table linked to the visit as a one to many relationship?
    Regards,

    Gary
    (It's been a while!)

  12. #12
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: list box in form (access 2007)

    Rick,

    One other question. Your form that has the listbox for pains is tied to the main patient form. Wouldn't you want this listbox that selects pains to be tied to not only the patient but on a particular visit as I would assume that a patient could have different symptoms or pains on different visits.
    The listbox for pains should be tied to the vist table. You might want to move this listbox to the visits form. Just something to think about.
    Regards,

    Gary
    (It's been a while!)

  13. #13
    rlbroerman
    Guest

    Re: list box in form (access 2007)

    I relent.
    After numerous comments about not using a multivalue list box. I agree and will try to use a subform instead.
    Originally I thought the multi value check box look like a good idea because the subforms that I had created seemed so mechanical and did not fit in with te rest of my form.
    Got any ideas on how to make the subform more like the rest of the form?
    Rick B
    Attached Files Attached Files

  14. #14
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: list box in form (access 2007)

    You have done the exact opposite of what I asked.

    Before talking about what what type of forms/controls to use, get clear about the table structure you need, and what it all means.

    I am not telling you not to use a mutlivalued field in a table (and perhaps displaying it with a list box) because I don't yet understand what you are trying to do.
    However, I am sceptical about the use of multivalued fields.
    Regards
    John



  15. #15
    rlbroerman
    Guest

    Re: list box in form (access 2007)

    JH,
    Sorry, I guess I jumped ahead to try to use a subform instead of a list box.
    I think what I want to do with Pain is what I have done with Current Medications subform in the attached Database.
    Another question. How can I tell what tables, queries, and etc. are used in my database and which ones are superflous so that I can find out what is not necessary and get rid of them. I want to clean up my database and get rid of old non-used stuff.
    Rick Broerman
    Attached Files Attached Files

Page 1 of 4 123 ... LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •