wrong record opened

[wilson1001]

Hello

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 "parts" . It then displays the results in a listbox.
If a result is selected it opens a form called “parts” and should display the corresponding record.

My form is based on the post by Mile-o-Phile - http://www.access-programmers.co.uk...ghlight=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 parts WHERE ((([part no]) Like '*" & Me.txtfilter & "*') OR (([description]) Like '*" & Me.txtfilter & "*') OR (([bin location]) Like '*" & Me.txtfilter & "*')OR (([in stock]) Like '*" & Me.txtfilter & "*')OR (([price 1]) Like '*" & Me.txtfilter & "*')OR (([price 2]) Like '*" & Me.txtfilter & "*')OR (([price 3]) Like '*" & Me.txtfilter & "*') );"

.Requery

End With

End Sub


Private Sub lstResults_AfterUpdate()
DoCmd.OpenForm "parts", acNormal, "[part no] = " & lstresults.Column(0)
End Sub


Private Sub Close_search_form_Click()
On Error GoTo Err_Close_search_form_Click


DoCmd.Close

Exit_Close_search_form_Click:
Exit Sub

Err_Close_search_form_Click:
MsgBox Err.Description
Resume Exit_Close_search_form_Click

End Sub




Any help would be appreciated
Thanks
bob

 

[Jack Cowley]

This is what you have:

DoCmd.OpenForm "parts", acNormal, "[part no] = " & lstresults.Column(0)

Try this:

DoCmd.OpenForm "parts", acNormal, , "[part no] = " & lstresults.Column(0)

You are missing a comma.

hth,
Jack

 

[wilson1001]

Thanks for taking a look at the code.

For some reason when I put the extra comma in I get a run-time error 2501 and the message "the openform action was cancelled"

When I remove the extra comma the form will open again but only the first record.

I have had this code working on past projects but this time it doesn't want to play ball.

Any ideas?

 

[Jack Cowley]

Usually some other code is/was running and did not finish. I would suggest putting a Stop in your code and stepping through it to find out just where this is happening. Once you find it you can fix it.

hth,
Jack