Editable Search Results.

[kacey8]

Okay, I haven't used Access for years, so please bare with me.

I have created a form to be used to search results, which works well.

I got the code online, set up the query and when I type the text in the search box, it narrows down the results in the list box.


CODE FOR THE SEARCH BOX

'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
    Me.SearchResults.Requery


'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)
    Me.SearchResults.SetFocus

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

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

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

However the next step I need is the record I click on in the list box is selected so it can be edited in the bellow fields. (see bellow)

http://s25.postimg.org/vsk2tqpsv/Untitled.png

I really hope someone can help.

[ranman256]

have it open a query on dbl-click with the criteria:
"...where [myField]='" & forms!frmFind!SearchResults & "'"

[rpeare]

The picture doesn't help with diagnosing the problem, but my guess is that you've got two tables with a one to many relationship, if that's the case the only way you're going to be able to get an editable query by joining the two is to make it a LEFT or RIGHT join and both tables have to have a primary key.

However, I do not think you need to have a query for this arrangement. Without knowing your data structure your search is giving you a list of items. when you click on that item you are requerying the subform. The MAIN form should be based on your 'master' table, the subform should be based on the 'child' table. Then when you click on the list of search items you requery the main form and the subform then both sets of data will be editable.