Bring up a selected record based on Selection


I have a form that is Unbound that is a search form.  (based on this:

3 fields,

SearchFor field
SearchTXT field
SearchResults Field

SearchTXT is Hidden

SearchFor field has for the OnChange:

'Create a string (text) variable
    Dim vSearchString As String

'Populate the string variable with the text entered in the Text Box SearchFor
    vSearchString = SearchFor.Text

'Pass the value contained in the string variable to the hidden text box SrchText,
'that is used as the sear4ch criteria for the Query QRY_SearchAll
    SrchText.Value = vSearchString

'Requery the List Box to show the latest results for the text entered in Text Box SearchFor

'Tests for a trailing space and exits the sub routine at this point
'so as to preserve the trailing space, which would be lost if focus was shifted from Text Box SearchFor
    If Len(Me.SrchText) <> 0 And InStr(Len(SrchText), SrchText, " ", vbTextCompare) Then
        Exit Sub
    End If

'Set the focus on the first item in the list box
    Me.SearchResults = Me.SearchResults.ItemData(1)

'Requery the form to refresh the content of any unbound text box that might be feeding off the record source of  the List Box

'Returns the cursor to the the end of the text in Text Box SearchFor

    If Not IsNull(Len(Me.SearchFor)) Then
        Me.SearchFor.SelStart = Len(Me.SearchFor)
    End If

There is a qry that is setup for the searching on the field :  SearchResults

What I want to do is once I select  a record in the search results, have that record come up in another form or within the same form.

How can I do this?

Ernest GroggSecurity Management InfoSecAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Assuming you have the Primary Key value of the record in your listbox, you can use the Click event of that Listbox to show your record in another form:

DoCmd.Close acForm "YourForm"
DoCmd.OpenForm "YourForm", , , "YourPrimaryKeyField=" & Me.YourListbox.Column(0)

This assumes the FIRST column in your listbox contains the value for the Primary Key field. If not, you'll need to change the Column(0) to the correct value, and remember that the Columns collection is zero-based, so Column(0) is the first one, and Column(1) is the second, and so forth.

If the form where your listbox is bound

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Helen FeddemaCommented:
My Fancy Filters sample database may be of use -- it demonstrates using unbound controls to get values of various data types for use in filtering a recordset displayed in a datasheet subform:

Here is a screen shot of the form:

Fancy Filters form
Ernest GroggSecurity Management InfoSecAuthor Commented:
Awesome...thanks so much to both of you.

Sorry I haven't responded sooner, but the Easter Holiday put me away for a while with the family.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.