Search code examples
ms-accessms-access-2016

Access 2016: Search result display in multiple text boxes

The work is being performed in Access 2016. Created a query and then created a form off of the query. Made a text box a search box to query a record. The search script is as follows.

Dim strsearch As String
Dim Task As String
'Check if a keyword entered or not
If IsNull(Me.txtSearch) Or Me.txtSearch = "" Then
   MsgBox "Please type in your search keyword.", vbOKOnly, "Keyword Needed"
   Me.txtSearch.SetFocus
Else
    strsearch = Me.txtSearch.Value
    Task = "SELECT * FROM [RSIDCircuit] WHERE ((RSID Like ""*" & strsearch & "*""))"
   Me.RecordSource = Task
End If

The search works as intended, but want to display the results in multiple different text boxes that can be edited. Would like column 1 to go to text4 and Column2 to text5 and so on. Cannot figure out how to get the results to appear in the various text boxes. When the search is submitted the other text box blanks out and when no search is done at all the other text boxes display the first record of the table. Any assistance is greatly appreciated.

Solution

  • Use a subform (create a continuous subform based on the base table, you can use the designer and thus not have to write code.

    Then, for a search, you just stuff in the sql into the the subform like this:

    Task = "SELECT * FROM [RSIDCircuit] WHERE ((RSID Like ""*" & strsearch & "*""))"
me.mySubForm.Form.RecordSource = Task

    The results will look somewhat like this: enter image description here

    And the results are able to be edited by you, or as in above I have the results, and if you click on the glasses icon, I run this code to launch a "drill down" details form to view the data

    docmd.OpenForm "frmDetails",,,"ID = " & me!id

    So to display repeating data, create + use what is commonly called a continuous form (multiple items form from the ribbon). It will like magic allow you to display rows of repeating data, but when you design the form, you only have to place/edit/design a form with one row of textboxes that is repeated over and over for you automatically.