Welcome to SPN

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

Sign Up Now!

Selecting keywords from drop down lists, After Update expressions

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

  1. Iain

    Expand Collapse

    I recently got some help with this, and it's very nearly working exactly as
    I'd like it too, but have one outstanding issue I'd ideally like to resolve.

    What I have so far is....

    Tables :

    Photos : PhotoID, Photo etc including 'Category' (numeric)

    Categories : CategoryID (numeric) & Category (text)

    Keywords : KeywordID (auto); Keyword (text); Category (text); CategoryID

    Form :


    Contains the various fields for each Photo.

    Category is a combo box :

    Row source type : Table/Query
    Row source : tbl_categories
    Column count : 2
    Column headers : No
    Column widths : 0cm;5cm

    So this basically is a drop down list displaying the various categories of
    Keyword. The idea being that grouping the keywords like this makes them more
    structured and managable.

    The idea of having something like this rather than have people enter them
    freely is to keep consistancy and reduce typos.

    From there, there is some code as an After Update expression :

    Private Sub Categories_AfterUpdate()
    Me.Keywords.RowSource = "SELECT Keyword FROM" & _
    " Keywords WHERE CategoryID = " & Me.Categories & _
    " ORDER BY Keyword"
    Me.Keywords = Me.Keywords.ItemData(0)
    End Sub

    This populates an unbound list box with the keywords from Keywords table.

    So if the Flora and Fauna category is selected it displays all the relevant
    Flora and Fauna keywords etc.

    This unbound list box has the following After Update expression :

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

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

    ' 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

    Which in turn populates the KeywordsNew field of the Photos table with the
    Keywords selected, and sorts them alphabetically.

    So far so good - this is almost exactly what I was trying to achieve.

    However, the only problem now is that you can only select keywords from a
    single category for each photo, as if you select one category, select any
    keywords, and then select a second category, when you select any keywords
    from that list it overwrites any existing keywords.

    I don't really come from a coding background, and have only gotten this far
    following some much appreciated help on forums - but if anyone knows how I
    can have what I have now, but be able to populate the KeywordNew field with
    keywords from multiple categories, that would be very much appreciated.

    Hope it makes sense, and someone can figure out what I mean!


Share This Page