Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Drop down lists to send multiple selections to a text field

Discussion in 'Information Technology' started by Iain, Jul 28, 2006.

  1. Iain

    Iain
    Expand Collapse
    Guest

    Hope someone can help with this. I am currently working on a project to
    create a database of stock photography that people can use to search for
    photos based on keywords.

    The idea is to split the photos into 6-8 categories, and for each category
    have 20-40 keywords. But rather than have the keywords typed in manually, we
    want to automate it, so as to keep consistency, and eliminate typos.

    The ideal would be to have one drop down list containg the categories, and a
    second drop down list that populated with the keywords dependent on the
    category selected in the first list.

    And from there be able to select multiple keywords that would populate a
    Keywords field.

    Would something like this be possible within an Access form?

    Any pointers greatly appreciated.

    Cheers,
    Iain
     
  2. Loading...


  3. Tom Wickerath

    Tom Wickerath
    Expand Collapse
    Guest

    Hi Iain,

    > The ideal would be to have one drop down list containg the categories, and a
    > second drop down list that populated with the keywords dependent on the
    > category selected in the first list.


    Check out this KB article on using synchronized combo boxes:

    How to synchronize two combo boxes on a form in Access 2002 or in Access 2003
    http://support.microsoft.com/?id=289670

    Note that you can use a list box, with the multiselect property set to Yes,
    in place of the second combo box. Thus, you can have a combo box containg the
    categories, and a filtered list box containing the keywords. The categories
    would be in a parent table, related 1:M to keywords in a child table.

    > And from there be able to select multiple keywords that would populate a
    > Keywords field.


    This gets a little bit trickier, but is still do-able. It sounds like you
    might want to implement the idea of a paired multi-select list box. Here is a
    tutorial on how to do that:

    Paired Multi-Select Listboxes
    http://www.helenfeddema.com/access.htm (see article # 72)


    There are also very good examples of paired multi-select list boxes in the
    Access Developer's Handbook, by Litwin, Getz and Gunderloy, published by
    Sybex.


    Tom Wickerath
    Microsoft Access MVP

    http://www.access.qbuilt.com/html/expert_contributors.html
    http://www.access.qbuilt.com/html/search.html
    __________________________________________

    "Iain" wrote:

    > Hope someone can help with this. I am currently working on a project to
    > create a database of stock photography that people can use to search for
    > photos based on keywords.
    >
    > The idea is to split the photos into 6-8 categories, and for each category
    > have 20-40 keywords. But rather than have the keywords typed in manually, we
    > want to automate it, so as to keep consistency, and eliminate typos.
    >
    > The ideal would be to have one drop down list containg the categories, and a
    > second drop down list that populated with the keywords dependent on the
    > category selected in the first list.
    >
    > And from there be able to select multiple keywords that would populate a
    > Keywords field.
    >
    > Would something like this be possible within an Access form?
    >
    > Any pointers greatly appreciated.
    >
    > Cheers,
    > Iain
     
  4. Arvin Meyer [MVP]

    Arvin Meyer [MVP]
    Expand Collapse
    Guest

    To avoid violation of database normalization, it would be better (and easier
    to search) if you made each keyword a separate row in a junction or detail
    table, displayed as a subform. Even if you did decide on using multiple
    keywords in a field, more than a few of them would be hard to read and
    confusing.
    --
    Arvin Meyer, MCP, MVP
    Microsoft Access
    Free Access downloads
    http://www.datastrat.com
    http://www.mvps.org/access

    "Iain" <Iain@discussions.microsoft.com> wrote in message
    news:A4DB2B87-C50C-4811-93DB-0804235D39E2@microsoft.com...
    > Hope someone can help with this. I am currently working on a project to
    > create a database of stock photography that people can use to search for
    > photos based on keywords.
    >
    > The idea is to split the photos into 6-8 categories, and for each category
    > have 20-40 keywords. But rather than have the keywords typed in manually,

    we
    > want to automate it, so as to keep consistency, and eliminate typos.
    >
    > The ideal would be to have one drop down list containg the categories, and

    a
    > second drop down list that populated with the keywords dependent on the
    > category selected in the first list.
    >
    > And from there be able to select multiple keywords that would populate a
    > Keywords field.
    >
    > Would something like this be possible within an Access form?
    >
    > Any pointers greatly appreciated.
    >
    > Cheers,
    > Iain
     
  5. Iain

    Iain
    Expand Collapse
    Guest

    Thanks Tom -

    That helped a lot - I now have the dependent drop downs working - where the
    first combo box populates a second list box.

    I actually had the second part working from when I looked at this a few
    months ago, but annoyingly can't get it to work in conjunction with this part.

    The list box had an After Update expression of :

    Private Sub List9_AfterUpdate()
    Dim ctlSource As Control
    Dim ctlDest As Control
    Dim strItems As String
    Dim intCurrentRow As Integer

    Set ctlSource = Me![List9]
    Set ctlDest = Me![Keywords]

    ' Loop through selected items; add to string
    For intCurrentRow = 0 To ctlSource.ListCount - 1
    If ctlSource.Selected(intCurrentRow) Then
    strItems = strItems & ctlSource.Column(0, _
    intCurrentRow) & ";"
    End If
    Next intCurrentRow

    ' Set destination control to string
    ctlDest = strItems

    ' Clear memory
    Set ctlSource = Nothing
    Set ctlDest = Nothing

    End Sub

    Where List9 is the list box, and Keywords is the text field the selections
    are populating.

    I figured it would be a case of adding the same expression to my new list
    box, and just change the source and destination names - but it doesn't like
    it.

    I did try creating a button that would copy and paste with a macro (goto -
    this works, but only a single selection - if you select another it pastes
    over the first selection.

    I went to look at that second link you posted, but just got a 404 tho'...

    Cheers,
    Iain

    "Tom Wickerath" wrote:

    > Hi Iain,
    >
    > > The ideal would be to have one drop down list containg the categories, and a
    > > second drop down list that populated with the keywords dependent on the
    > > category selected in the first list.

    >
    > Check out this KB article on using synchronized combo boxes:
    >
    > How to synchronize two combo boxes on a form in Access 2002 or in Access 2003
    > http://support.microsoft.com/?id=289670
    >
    > Note that you can use a list box, with the multiselect property set to Yes,
    > in place of the second combo box. Thus, you can have a combo box containg the
    > categories, and a filtered list box containing the keywords. The categories
    > would be in a parent table, related 1:M to keywords in a child table.
    >
    > > And from there be able to select multiple keywords that would populate a
    > > Keywords field.

    >
    > This gets a little bit trickier, but is still do-able. It sounds like you
    > might want to implement the idea of a paired multi-select list box. Here is a
    > tutorial on how to do that:
    >
    > Paired Multi-Select Listboxes
    > http://www.helenfeddema.com/access.htm (see article # 72)
    >
    >
    > There are also very good examples of paired multi-select list boxes in the
    > Access Developer's Handbook, by Litwin, Getz and Gunderloy, published by
    > Sybex.
    >
    >
    > Tom Wickerath
    > Microsoft Access MVP
    >
    > http://www.access.qbuilt.com/html/expert_contributors.html
    > http://www.access.qbuilt.com/html/search.html
    > __________________________________________
    >
    > "Iain" wrote:
    >
    > > Hope someone can help with this. I am currently working on a project to
    > > create a database of stock photography that people can use to search for
    > > photos based on keywords.
    > >
    > > The idea is to split the photos into 6-8 categories, and for each category
    > > have 20-40 keywords. But rather than have the keywords typed in manually, we
    > > want to automate it, so as to keep consistency, and eliminate typos.
    > >
    > > The ideal would be to have one drop down list containg the categories, and a
    > > second drop down list that populated with the keywords dependent on the
    > > category selected in the first list.
    > >
    > > And from there be able to select multiple keywords that would populate a
    > > Keywords field.
    > >
    > > Would something like this be possible within an Access form?
    > >
    > > Any pointers greatly appreciated.
    > >
    > > Cheers,
    > > Iain
     
  6. Tom Wickerath

    Tom Wickerath
    Expand Collapse
    Guest

    Hi Iain,

    On that second link, I somehow pasted in a white space after the "m" in
    ..htm. So it's actually looking for ".htm ", not ".htm". With your cursor,
    backspace over the blank character, and the page should open okay.

    I forgot to mention it in my first post, but I fully intended to recommend,
    just like Arvin did, that you want to store each keyword in a separate row in
    a junction table. The Helen Feddema sample that I pointed you to does just
    that.

    By the way, I recommend giving your list box a more reasonable name, such as
    "lstKeywords". The current name, "List9", is not exactly descriptive of the
    control. Your code will be a lot more readable in the future if you take the
    time to assign better names up front, instead of just accepting the default
    names when you drop a new control onto a page.


    Tom Wickerath
    Microsoft Access MVP

    http://www.access.qbuilt.com/html/expert_contributors.html
    http://www.access.qbuilt.com/html/search.html
    __________________________________________

    "Iain" wrote:

    > Thanks Tom -
    >
    > That helped a lot - I now have the dependent drop downs working - where the
    > first combo box populates a second list box.
    >
    > I actually had the second part working from when I looked at this a few
    > months ago, but annoyingly can't get it to work in conjunction with this part.
    >
    > The list box had an After Update expression of :
    >
    > Private Sub List9_AfterUpdate()
    > Dim ctlSource As Control
    > Dim ctlDest As Control
    > Dim strItems As String
    > Dim intCurrentRow As Integer
    >
    > Set ctlSource = Me![List9]
    > Set ctlDest = Me![Keywords]
    >
    > ' Loop through selected items; add to string
    > For intCurrentRow = 0 To ctlSource.ListCount - 1
    > If ctlSource.Selected(intCurrentRow) Then
    > strItems = strItems & ctlSource.Column(0, _
    > intCurrentRow) & ";"
    > End If
    > Next intCurrentRow
    >
    > ' Set destination control to string
    > ctlDest = strItems
    >
    > ' Clear memory
    > Set ctlSource = Nothing
    > Set ctlDest = Nothing
    >
    > End Sub
    >
    > Where List9 is the list box, and Keywords is the text field the selections
    > are populating.
    >
    > I figured it would be a case of adding the same expression to my new list
    > box, and just change the source and destination names - but it doesn't like
    > it.
    >
    > I did try creating a button that would copy and paste with a macro (goto -
    > this works, but only a single selection - if you select another it pastes
    > over the first selection.
    >
    > I went to look at that second link you posted, but just got a 404 tho'...
    >
    > Cheers,
    > Iain
    >
    > "Tom Wickerath" wrote:
    >
    > > Hi Iain,
    > >
    > > > The ideal would be to have one drop down list containg the categories, and a
    > > > second drop down list that populated with the keywords dependent on the
    > > > category selected in the first list.

    > >
    > > Check out this KB article on using synchronized combo boxes:
    > >
    > > How to synchronize two combo boxes on a form in Access 2002 or in Access 2003
    > > http://support.microsoft.com/?id=289670
    > >
    > > Note that you can use a list box, with the multiselect property set to Yes,
    > > in place of the second combo box. Thus, you can have a combo box containg the
    > > categories, and a filtered list box containing the keywords. The categories
    > > would be in a parent table, related 1:M to keywords in a child table.
    > >
    > > > And from there be able to select multiple keywords that would populate a
    > > > Keywords field.

    > >
    > > This gets a little bit trickier, but is still do-able. It sounds like you
    > > might want to implement the idea of a paired multi-select list box. Here is a
    > > tutorial on how to do that:
    > >
    > > Paired Multi-Select Listboxes
    > > http://www.helenfeddema.com/access.htm (see article # 72)
    > >
    > >
    > > There are also very good examples of paired multi-select list boxes in the
    > > Access Developer's Handbook, by Litwin, Getz and Gunderloy, published by
    > > Sybex.
    > >
    > >
    > > Tom Wickerath
    > > Microsoft Access MVP
    > >
    > > http://www.access.qbuilt.com/html/expert_contributors.html
    > > http://www.access.qbuilt.com/html/search.html
    > > __________________________________________
    > >
    > > "Iain" wrote:
    > >
    > > > Hope someone can help with this. I am currently working on a project to
    > > > create a database of stock photography that people can use to search for
    > > > photos based on keywords.
    > > >
    > > > The idea is to split the photos into 6-8 categories, and for each category
    > > > have 20-40 keywords. But rather than have the keywords typed in manually, we
    > > > want to automate it, so as to keep consistency, and eliminate typos.
    > > >
    > > > The ideal would be to have one drop down list containg the categories, and a
    > > > second drop down list that populated with the keywords dependent on the
    > > > category selected in the first list.
    > > >
    > > > And from there be able to select multiple keywords that would populate a
    > > > Keywords field.
    > > >
    > > > Would something like this be possible within an Access form?
    > > >
    > > > Any pointers greatly appreciated.
    > > >
    > > > Cheers,
    > > > Iain
     
  7. Iain

    Iain
    Expand Collapse
    Guest

    Thanks guys -

    I've managed to get it working for now - I went back and did step one over
    my existing Form that had step two working, and it works a treat.

    My List9 was a rogue from when I did it originally - so would get that
    changed.

    That's me off til Monday now, so will come back and look at the database
    structure and you're suggestions then Arvin - but for now, thanks very much -
    very impressed to get that working this morning!

    Iain
     
  8. Iain

    Iain
    Expand Collapse
    Guest

    Hi there,

    Just coming back to this. I see what you mean about the separate table, but
    this seems to work quite well for us - there shouldn't be too many keywords
    per photo so they should be fine comma separated.

    Plus, the idea is to have the front end of this as an online search tool, so
    from a development point of view it seems easier to have them all in a
    searchable Keywords field.

    Iain
     

Share This Page