Search Form - Displays wrong record

wilson1001

Registered User.
Local time
Today, 08:23
Joined
Mar 24, 2003
Messages
17
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
:D
 
I've amended the example to actually open another form.
 

Attachments

You don't have enough comas in your OpenForm statement so the filter is not being applied.
 
wilson1001 said:
.RowSource = "Select * FROM tapes WHERE [agency]Like '*" & Me.txtfilter & "';"

You may have a problem also with [agency]Like rather than [agency] Like
 
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
 
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...
 
Pat Hartman - Where should the additional commas go?

Mile - Thanks for your constructive input

Abhi- Thanks but that code didn't make any difference!
 
I've added the extra comma.

DoCmd.OpenForm "tapes", acNormal, , "[tape number] = " & lstResults.Column(0)
 
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.
 
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 & "';"
 
wilson1001 said:

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 & "*';"
 
That worked perfectly.You really are quite a star. Thanks for all your help.
 
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.
 

Users who are viewing this thread

Back
Top Bottom