Search Form - Displays wrong record

[wilson1001]

Hi There. I am new and very inexperienced so please forgive any daft terminology.

Using access 2002 on XP.

I need some help with a form I have created to search a table.

I have created a search form with a text box and command button that search a table called "tapes" and a field called “agency”. It then displays the results in a listbox.
If a result is selected it opens a form called “tapes” and should display the corresponding record.

My form is based on the post by Mile-o-Phile - http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=40954&highlight=complex

-What Works-
I can enter text into the text box, hit the command button and the listbox will be populated with results.
If I click then on a result, it will open the correct form.

-My Problem-
Although the correct form is opened the record displayed is always the same, the first in my table. It will not display the record that corresponds to the result I clicked on.

-The code-
Private Sub cmdSearch_Click()
If IsNull(Me.txtfilter) Then
MsgBox "You have not entered any text.", vbExclamation, "Attention"
Exit Sub
End If
With LSTRESULTS
.RowSource = "Select * FROM tapes WHERE [agency]Like '*" & Me.txtfilter & "';"
.Requery
End With
End Sub

Private Sub Command54_Click()

End Sub

Private Sub lstResults_AfterUpdate()
DoCmd.OpenForm "tapes", acNormal, "[tape number]=" & LSTRESULTS.Column(0)

End Sub

Any help would be appreciated
Thanks
Pete

 

[Mile-O]

I've amended the example to actually open another form.

 

[Pat Hartman]

You don't have enough comas in your OpenForm statement so the filter is not being applied.

 

[Mile-O]

.RowSource = "Select * FROM tapes WHERE [agency]Like '*" & Me.txtfilter & "';"

You may have a problem also with [agency]Like rather than [agency] Like

 

[Only1Abhi]

Wilson... I know who u are!!!
And WHAT skool u go to... (BGS)

But donna why I'm feeling generous today...

Here's the code u need!

.RowSource = "SELECT * FROM tapes WHERE [agency] Like '" & Me.txtFilter & "';"

Just replace that with what u got & u should be fine!!!

Abhi

 

[Only1Abhi]

I have also noticed u have called a listbox LSTRESULTS in 1 place and lstResults in another.

Although caps may not create problems, it may well do. Keep everything in lower caps...

just a recommendation...

 

[Mile-O]

It won't cause problems...

 

[wilson1001]

Pat Hartman - Where should the additional commas go?

Mile - Thanks for your constructive input

Abhi- Thanks but that code didn't make any difference!

 

[Mile-O]

I've added the extra comma.

DoCmd.OpenForm "tapes", acNormal, , "[tape number] = " & lstResults.Column(0)

 

[Pat Hartman]

wilson1001, if all the code settings options are checked in the Editor tab of the options dialog, you should see help as you type the statement. Each time you type a coma, the argument that goes at that position is higlighted in the argument list.

The setting are this way by default. If you want to see them, open any code module, then select Tools/Options.

 

[wilson1001]

Success! Thanks Mile and Pat.

Another question.......Doh

Currently to obtain a result form text entered into the text box it has to be entered exactly as it appears in the agency field.
What terms should I use to make it a bit more wild?

I.E if I want to find "television productions" I could just type tele.

Code currently reads
.RowSource = "Select * FROM tapes WHERE [agency]Like '*" & Me.txtfilter & "';"

 

[Mile-O]

I.E if I want to find "television productions" I could just type tele.

Code currently reads
.RowSource = "Select * FROM tapes WHERE [agency] Like '*" & Me.txtfilter & "';"

If [agency] contains any records called Television Productions with the field, then changing this line should add further fleibility:

.RowSource = "Select * FROM tapes WHERE [agency] Like '*" & Me.txtfilter & "*';"

 

[wilson1001]

That worked perfectly.You really are quite a star. Thanks for all your help.

 

[wilson1001]

I think I'm going for the mile here

One more thing. If i wanted to search across all fields in the table and not just agency, how could I change this line to do so?

.RowSource = "Select * FROM tapes WHERE [agency] Like '*" & Me.txtfilter & "*';"


For instance if I wanted to add the fields [tape number] [format] and so on. Or is there a simpler term that will search the entire table, in a wild fashion of course.

I've tried adding them with square brackets here, commas there but I am struggling with the syntax.