My question:

I've got a form, called "frmSearchKeyword", where the user can enter a word or string of words to search the database. The field where the data is entered is a text box, called : 'txtKeyword'. The user enteres the data / word that they wish to query the main table, called : tblTask , in the field Task_Description, called : Task_Description.

When the user enters what they want to query with, for example: appraisal, SGT, SFC, etc, they then click a command button, called: cmdSearch. This button then runs the query taking the information from the txtbox and running it through the entire db table field of Task_Description.

The results from the tblTask that I want to have shown are:

DateOriginated
Task_Description
Status

These results are displayed in a list box with three columns right below the search criteria on the form, frmSearchKeyword

I need to get that figured out first, and I'm working on it too, then I'm going to figure out how to make it so that the user can highlight the task they want and click a command button "Open" and it will open in the frmTask for editing purposes with all information in it, or double-click on the one they want and it will do the same.

The string of words you want to search for, do they have to follow each other, or can there be other words between them?

For exemple if you are looking for 'a b c', will it return:

p a b c f e
a b c d e f

or will it also have to return:

a e b c e d
a c b d d e

Done this with simple lettres to have an exemple so I am sure of what you want. If you need the first exemple and NOT the second one I can help you... otherwise I think I cant't

The first way to search is fine.

I was trying to make it so that the user would put in like "Test", "Print", "SFC", "CSM" , "Appraisal", or things of that nature. It doesn't matter the way the words appear, just as long as it searches for them.

Thanks,

Bryan

ok, this is what you should do:

First of all, leave your list box rowsource empty.

On the on click event of your command button, put this code:

dim sSQL as string
dim sInput as string

sInput = me.txtKeyword

sSQL = "SELECT tblTask.DateOriginated, tblTask.Task_Description, tblTask.Status " & _
"FROM tblTask " & _
"WHERE Task_Description like '" & "*" & sInput & "*" & "'" & _
"ORDER BY tblTask.DateOriginated;"

Me.NameOfYourListBox.RowSource = sSQL

This should get you the data you need in your list box. I have sorted the data on the Date, remove or change this to what you need...

In this case the list box is empty when you open the form and it only shows data after you have pressed the command button. If you want all the data to be shown before pressing the button, then you should give the name of your table (or query) in the list box. It believe a query is better, cause I don't know it the fields in your table will be in the correct order to be shown in the list box...

Let me know if this is not working for you.

Ok, here's the code that I put on the command button "Find!":

Private Sub cmdSearch_Click()
'this is the 'find us!' command button

Dim sSQL As String
Dim sInput As String

sInput = Me.txtKeyword

sSQL = "SELECT tblTask.DateOriginated, tblTask.Task_Description, tblTask.Status " & _
"FROM tblTask " & _
"WHERE Task_Description like '" & "*" & sInput & "*" & "'" & _
"ORDER BY tblTask.DateOriginated;"

Me.List7.RowSource = sSQL
Private Sub cmdMainMenu_Click()
On Error GoTo Err_cmdMainMenu_Click


**It gives a compile error and highlights the "sSQL" in the Me.List7.RowSource=

It is having problems with the commas probably. You are not going to get this to work properly in this format, unless you can guarantee that your users will put in a comma between each word.

How about giving them a series of text boxes and allowing them to add a word to each text box? Then just check for a value and build your sSQL string from there.

They way you have it now, it looks like this is what your string is going to look like:

SELECT tblTask.DateOriginated, tblTask.Task_Description, tblTask.Status
FROM tblTask
WHERE Task_Description like '*WordOne, wordtwo, wordthree*'
ORDER BY tblTask.DateOriginated;

This is not going to work unless you find this in your string

...WordOne, wordtwo, wordthree...

Also, there is no need for the semicolon at the end of the string.

You can turn this into an array and build your string from the array IF you can guarantee your users will ALWAYS use commas between words. This is not likely.




mmcdonal

I got it to work with the code below, took for ever and a day but oh well....


Dim strSQL As String
Dim strDescription As String

strDescription = Nz(Me.txtKeyword, "")

strSQL = "SELECT tblTask.Task_ID, tblTask.TaskDescription, " & _
            "tblTask.DateOriginated, tblTask.Status " & _
            "FROM tblTask " & _
            "WHERE (((tblTask.TaskDescription) Like '*" & strDescription & "*'));"
Me.List7.RowSource = strSQL
Me.List7.Requery

So they are only entering one word at a time then. That is more like what I am used to.


mmcdonal

Yeah, I only wanted them to be able to enter one word. They'd get all confuxed if I let them search by more than one word.

I am replying to unsubscribe. Where is the freakin moderator?!?!


mmcdonal