Am in the planning/unknown stage and just cannot visualise what I want/need to do.

I want to have a text field where people can enter search words/tags for a record.

Obviously people can put a word in this field and filter for results, but I also want to have a combobox which is populated with all these words for the people with a little less immagination.

Using this forum it seems that I will use the split functions and arrays to get each word separated and then enter them into a table. (i think at least). Then later this table will be used to give me the source for my combobox.

WHAT table to put each word in.

I think for you to answer I need to give you some example material.

Imagine I have a table called "claims" and it has a primary key of "claimsID". It will obviously have other fields.

Then I imagine I will need a second table named "claimstags" which is linked with claimsID.

The questions is what type of table do I need to receive each word that I will have from the array given that I will not know how many words I might have.

I wonder if a multi select list box might be a better bet.

I wonder if a multi select list box might be a better bet.
Not a problem am always open to better suggestions. But at this stage (unless I do not understand you) it is the storage of all of these words that I am thinking about. I am having trouble visualising how to save each word in a field in a table.

You shouldn't have more than one piece of data in a field. So each word should be a new record.

You shouldn't have more than one piece of data in a field. So each word should be a new record.

Yep that is exactly what I want. But how. Eg following my earlier example

Table Claims

ClaimsID = 1
claimstags = 2008,communications,handy,HTC,printer (entered by user)

So I will "split" this field up and put it into a second table. But how should this second table look. I know it will have the following as one column but they all relate to the one ClaimsID=1;

2008
communications
handy
HTC
printer

or in this second table do I NOT need a primary key? so that it would look like this? Then I like the first column of this table to the 1st table?

1 -- 2008
1 -- communications
1 -- handy
1 -- HTC
1 -- printer

i would have thought you WERE better giving the users a freeformat search box, rather than a combo box

but then splitting the text into multiple strings, and searching for all of them is quite a complex bit of code to write.

however, even if you use a combo box, searching for the required string, and offering the selection is still tricky

--
in general finding/random searching anything - customers, product descriptions is not simple - cause you can't use something like really fast like "grep" on a ISAM file - instead you have to parse records individually

is that strictly true for access? is there a faster way?

i would have thought you WERE better giving the users a freeformat search box, rather than a combo box
I was hoping to give them both. First for the people that know what they are looking for they can enter a word in a free field.

But then I was thinking I would be able to easily generate a list of all of the words that people entered in a field.

I thought that there would be no difference in the search that I use as I will either filter for the word they enter or filter for the word they choose.

Just throwing this out there ....

Let's say your query that returns the search has 8 fields of data. Let's say that you want to have the capability to apply different criteria to all 8 fields and/or mix-n-match (all for some fields, a specific text/number for others). And let's say you aren't opposed to using combo boxes.

On the unbound search form, you could populate each combo box from the table and field it will be searching with unique values. This will fetch every word/number that could possibly be searched on that particular field.

On the query, for the criteria for those fields, just reference the unbound forms combo box using Like ... & "*" where necessary.

The combo box will still allow a sort of 'free fill' because it will automatically fill in as they type. The only caveat I can think of is that each field will only have one criteria against it. For example, if a field name was a person's name then they could only search John or Jill but not both John and Jill unless you do some of the fancier coding.

-dK

pretty sure you are on the right track I think. But to be sure I will answer as follows;

taking my example further:

Table "Claims"

ClaimsID = 1
claimstags = 2008,communications,handy,HTC,printer (entered by user)

So I will "split" this field up and put it into a second table "Claimstags". But how should this second table look. I know it will have the following as one column but they all relate to the one ClaimsID=1;

Table "Claimstags" with a column name "allclaimtags"


2008
communications
handy
HTC
printer

or in this second table do I NOT need a primary key? so that it would look like this? Then I like the first column of this table to the 1st table?

1 -- 2008
1 -- communications
1 -- handy
1 -- HTC
1 -- printer On my search form there will be a combo box which will list the five things above ie RowSource = Claimtags.allclaimtags

The user chooses something from the list; say "handy"

Then my filter is (or build up of the filter will be)

strWhere = strWhere & "([claimstags] Like """ & Me.[combobox] & """) " & strandor & " "Meaning that it simply takes the word they have choosen and filters it against the original field where all the items are.

Actually after writing this I think my question is answered. I simply create a table with no linking or primary key at all. It will have just one field which will have as many records as words that people have entered.

Just wanted to put here what I have done.

For each record I first delete what is saved and then resave it each time.

Dim sParts() As String
Dim i As Integer
Dim sql As String
Dim rs As DAO.Recordset
Dim DB As DAO.Database

If Len(Me.cctags) > 0 Then

sParts = Split(Me.cctags, ",")

Set DB = CurrentDb
Set rs = DB.OpenRecordset("claimcharttags", dbOpenDynaset)
DoCmd.SetWarnings False
sql = "Delete from [claimcharttags] where cctagrecordid = " & Me.ccid
DoCmd.RunSQL sql
DoCmd.SetWarnings True

For i = 0 To UBound(sParts)
'If DCount("[cctagid]", "[claimcharttags]", "[cctagtag]='" & sParts(i) & "'") = 0 And Len(Trim(sParts(i))) > 2 Then
If Len(Trim(sParts(i))) > 2 Then
rs.AddNew
rs.Fields(1) = Trim(sParts(i))
rs.Fields(2) = Trim(Me.ccid)
rs.Update
End If
Next i

rs.Close
DB.Close
Set rs = Nothing
Set DB = Nothing

End If