sql statment help for access

[london01]

hello everybody,

I'm new to access and sql so i'm going to need some help with my project.

form1 has a text box to enter key words to search a table and a "search" button.
form2 has a list box to display the resutls based on the search crit.

example database name: exampledb1.
table names: db_table1.
table contains the following fields: search_text field, description field, results field

i need a sql statment in the button(located in form1), that will take the key words entered in text box(located in form1) and search the db_table1.search_text(field) and list the results it found in list box located in form2.

any help u can give me would be greatly appreciated.

 

[scriverb]

Hello there London01. First of all you don't need a command button to initiate this process. Also what is the reason for two forms. Just have your textbox and listbox on the same form. As you users enter the text into the text box and tab out of the control we can put the VBA code in the AfterUpdate event procedure of the textbox to refresh the Listbox.

The SQL statement only needs to be put into the RowSource for the ListBox and it will automatically select the correct records for you. Here are the steps to do this.

1. In the AfterUpdate event procedure of the TextBox put the following VBA code:

Me.[[i][red]listboxname[/red][/i]].requery

2. The SQL for the ListBox should look something like this:

Select A.* FROM [i][red]yourtablename[/red][/i] as A 
WHERE A.[[i][/red]searchfieldname[/red][/i]] = FORMS![[i][blue]formname[/blue][/i]]![[blue][i]txtcontrolname[/i][/blue]];

Post back with any questions.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[london01]

Hello.
I took your suggestion and decided to move the list box in the same form as the text box is located in.

I also tried your code and it doesn’t seem to be working for me

After update (text box property) I have the following: me.List17.requery

Row source (list box property) SELECT A.* FROM db_table1 AS A WHERE A. search_text=[FORMS]![form1]![form1_oneline_textfield1];

I know that the search_text field has the words records pretty much in all of the records but nothing appears.

 

[scriverb]

Are you putting multiple words in the textbox? If that is the case then we will have to modify somethings here greatly. Post back with if you are entering multiples and how you are designating beginning and ending of each search word.(i.e. , : ; etc.)

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[london01]

thanks for getting back to me...

Due to my understanding of vb and microsoft access i guess its not making this posting any easer..lol

anyways.. i'm only using that one text box to enter multiple words to search.. example would be like google: microsoft access tutorial for beginner, would search the search_text field and list all the records containing any or all the words in the field search_text...

 

[scriverb]

That's what I thought. I will work on this for you and see what I can come up with for a suggestion. Do not fret over the inability to communicate efficiently in your thread. It is as much the readers problem as the poster. We are both trying to get a point across as well as interpret effectively.

Fear not we will figure this out for you and see what can be done.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[scriverb]

Okay, let's give this code a try. Here is the new SQL for the ListBox RowSource:

Select A.* FROM yourtablename as A
WHERE SearchText([search_text]) = True;

You will see a new function being used in this SQL. Put the following Global variable and Function in a database module:

Global Words(100) as String

Public Function SearchText(txtSearchText) as Boolean
Dim i as Integer
For i = 1 to 100
   If Instr(1,txtSearchText,Words(i)) > 0 then
      SearchText = True
      Exit Function
   Else
      [i][green]this word not in text[/green][/i]
   End If
Next i
SearchText = False
End Function

Now in the command buttons AfterUpdate Event Procedure put the following:

Dim i as Integer, StrText as String
i = 1
StrText = Me.[search_text]
If Not IsNull(StrText) then
   Do
      Words(i) = Mid$(StrText,1,Instr(1,StrText," ")-1)
      StrText = Mid$(StrText, Instr(1,StrText," ")+1)
      i = i + 1
   Loop Until Len(StrText) = 0
Else
   MsgBox "No search words entered"
   Me.[search_text].setfocus
   Exit Sub
End If
Me.[[i][red]listboxname[/red][/i]].requery

This code loads up to 100 individual words from your search text control into a global array. Then the listbox is requeried and the SQL is run to select the records. The function is called for each record and the search field is passed to the function. Each of the words that have been parsed into the array are checked for being in the search text. If any one of them is true for a record then the function returns a True value otherwise False. The SQL for the listbox selects only True values for this column.

Post back with any questions or problems as I have not tested this code yet.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[london01]

hello scriverb,

Thanks for your hard work and the time you spent helping me with this problem but nothing seems to be working.. I think it has lot to do with my exp. with MS Access and vb. I'm going to give up on it and just keep my notes and documentations in word docs to make this easy on myself..lol

I greatly appreciated your help... u went out of your way to help me with this problem and i thank you again...
maybe i'll look at it more after i have little more understanding of access and vb programming...
thanks again
Robin

 

[scriverb]

Why don't you send me an email(see my profile) and I will pkg. up a demo database for you to review and see how I have done this.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[scriverb]

I had to make a couple of modifications:

Update the function in Module

Public Function SearchText(txtSearchText) As Boolean
Dim i As Integer
For i = 1 To 100
    [red]If Words(i) <> "" Then[/red]
        If InStr(1, txtSearchText, Words(i)) > 0 Then
            SearchText = True
            Exit Function
        Else
            'this word not in text
        End If
    [red]Else
        i = 100
    End If[/red]
Next i
SearchText = False
End Function

Command Button OnClick Event Procedure:

Dim i As Integer, StrText As String
i = 1
StrText = Me.[search_text]
[red]Erase Words[/red]
If Not IsNull(StrText) Then
   Do
      [red]Words(i) = Mid$(StrText, 1, IIf(InStr(1, StrText, " ") = 0, Len(StrText), InStr(1, StrText, " ") - 1))
      StrText = IIf(InStr(1, StrText, " ") = 0, "", Mid$(StrText, InStr(1, StrText, " ") + 1))[/red]
      i = i + 1
   Loop Until Len(StrText) = 0
Else
   MsgBox "No search words entered"
   Me.[search_text].SetFocus
   Exit Sub
End If
Me.[[i][blue]listboxname[/blue][/i]].Requery

Now after entering individual words in the text control [search_Text] just click the command button and the records that have any one of these words in the target text field in the table.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]