I
Iain
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
(numeric)
Form :
Photos
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!
Cheers,
Iain
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
(numeric)
Form :
Photos
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!
Cheers,
Iain