Searchable Tagged List

[muchobrento]

Here's a total newbie problem that I could use some help on:

I'm trying to create a list of items that allows me to tag each item with essentially unlimited tags and perhaps a limited number of other fields. Later, I'd like to be able to search that list for specific items by the tags that they have. Finally, I'd like to either export or copy/paste the results.

Easy, right? Not for me : )

Can anybody help me out?

MuchoBrento

 

[xenou]

You could create a table for tags:

Table_Tags (ItemID, TagValue)

Insert as many tags as you like. For instance, if your ItemID is 1, then you can insert tags:

Insert Into Table_Tags(ItemID, TagValue) Values (1, 'Good')
Insert Into Table_Tags(ItemID, TagValue) Values (1, 'For Later')
Insert Into Table_Tags(ItemID, TagValue) Values (1, 'Tips and Tricks')

In reverse, you can retrieve from your table by tag name (to get all items for a tag) or by itemID (to get all tags for an item)

 

[wob]

I've got something similar, but my approach was to:

- Have a field that stores tags. Users can put whatever tage they like in, and any number of tags.
- Have a llst of the tags that users have already added available to refer to.

I called the tags 'keywords'

To create the list of the tags already set, I set up some VBA.
I honestly can't rememember if I wrote the VBA myself, or adapted it from something I found on the net.

It will basically turn this (from query Qry_VBA_Keyword_Store):

apple, banana, orange
apple, orange, lemon

into table Tbl_Keywords

apple
banana
lemon
orange


Sub KeywordsToColumns()

Dim db As Database
Dim rs_qry As Recordset
Dim rs_tbl As Recordset

Dim txt_source As String
Dim WordCount As Integer
Dim txt_start As Integer
Dim txt_end As Integer
Dim z As Integer
Dim t As Integer

'Clears out existing data from Table
Call Tbl_Clear

'sets database, opens query, goes to first record
Set db = CurrentDb
Set rs_qry = db.OpenRecordset("Qry_VBA_Keyword_Store") 'Source Query
Set rs_tbl = db.OpenRecordset("Tbl_Keywords") 'Destination Table


If Not (rs_qry.EOF And rs_qry.BOF) Then 'Just in case, there are no records found
rs_qry.MoveFirst 'Start at first Record...
Do Until rs_qry.EOF = True '..and do the following, for all records

'''''''''''''''''''''''''''''
''''''String to Array''''''''
'''''''''''''''''''''''''''''

'Assigns result to variable
txt_source = rs_qry.Fields(0).Value & " "

txt_start = 1

'Count of Words in field, then less one (array starts at zero)
WordCount = Len(txt_source) - Len(Replace(txt_source, " ", ""))
z = WordCount - 1

ReDim txt_arr(z) As String 'Gets an array the size of the words in the string

For i = 0 To z
'finds the first space in the string, counts one back to get the last letter position
txt_end = Abs(InStr(1, txt_source, " ") - 1)
'String goes into array
txt_arr(i) = Left(txt_source, (txt_end))
'trim down source text for the next word
txt_source = LTrim(Right(txt_source, Len(txt_source) - Len(txt_arr(i))))
Next i


''''''''''''''''''''
''''Update Table''''
''''''''''''''''''''
'Updates the table with the array strings...

For k = 0 To UBound(txt_arr)
rs_tbl.AddNew
rs_tbl!Keyword = txt_arr(k) 'Assumes table had field 'Keyword'
rs_tbl.Update
Next k
rs_qry.MoveNext 'ALWAYS move to the next record to start the next loop!
Loop 'Next record

Else 'Do nothing if there where records found in the query

End If 'EOF found

rs_qry.Close: Set rs_qry = Nothing 'Close recordsets and db
rs_tbl.Close: Set rs_tbl = Nothing
db.Close

End Sub

Sub Tbl_Clear()



Dim MyTable As String

'Table to clear
MyTable = "Tbl_Keywords"
MySql = "DELETE * FROM " & MyTable & ";"

On Error Resume Next
DoCmd.SetWarnings False
DoCmd.RunSQL MySql
DoCmd.SetWarnings True
End Sub