I'm receiving an error when I attmept to run the following code:

Private Sub ShowRecord_Click()
'Find a selected record, then close the search dialog box
Dim myvar
myvar = Me.lstSearch.Column(0)
MsgBox myvar
    DoCmd.OpenForm "PAPER_AUDIT_COMPLETED", , , "[Key] = '" & myvar & " '"

    'Close the dialog box
  ' DoCmd.Close acForm, "searchlist"

End Sub

I am attempt to pull the value out of a list box for a search query. The Me.listSearch.Column(0) returns the correct value and saves it to myvar, which I verify through the MsgBox. I am then trying to open the form "PAPER_AUDIT_COMPLETED" and run a filter on the Key field for my search results. When I run it I'm receiving "Run-time error 2501" \n "The OpenForm action was cenceled". I've been hacking it to pieces and it works if I hard code it, and it works if I break it so it asks me what myvar is. Anytime I set the code correctly (I think it's correct) it spews out this awefully generic message. I've looked around online and tried a few things to no avail. Any help would be appreciated.

OK ...But saving an value tempararly in an Dim string is not possible i think you should create an record set tp store the value in an string..

 Let me know wat the real programm u want to do list out the u want to open an form...

bye,
anukagni


Learn as you can..
------------------------
pap...

I am building search functionality for a form. The user puts in search criteria, then hits search. This opens my listbox form which runs a query based on the search criteria and returns the results. The search criteria form closes. I want the user to choose in the list box the record they would like, and then to run the code I have above to filter the records on a form to display it (by primary key field). I have searched online and many people are doing the same thing I am, and the code looks identical. My new thought this morning while typing this is that it may be a type mismatch since the field in the database is an autocomplete, maybe I should try to Dim as different types? Or will it only work with a string?

Change your code to this (I am assuming that the value in Me.lstSearch is a string):

Private Sub ShowRecord_Click()
Dim myvar, sLink As String

myvar = Me.lstSearch.Column(0)

sLink = "[Key] = '" & myvar & "'"

DoCmd.Close
DoCmd.OpenForm "PAPER_AUDIT_COMPLETED", , , sLink

End Sub


Did that work?

mmcdonal

You got me away from the 2501 error code. It's now pulling up the form and not giving any errors. My problem I think is that the results from Me.lstSearch is an int. (it's the record autonumber so I know I get the right record since every other field can duplicate) Now when the form comes up it doesent actually run the filter at all, it's just bringing up the first record in the table and not filtering the results.

Try this:

Dim myvar As Integer
Dim sLink As String

myvar = Me.lstSearch.Column(0)

sLink = "[Key] = " & myvar

Did that work?



mmcdonal

That works. I figured out what the problem was too. In my openform I had mistyped sLink as sSLink.

Thanks Mr. Wizard!