Solved Include a Text Search Box in Form to obtain results from subfrm (1 Viewer)

[chickenwings23]

Hello,

I want to create an access file where associates can enter some essential data for later use. (Name etc.) Is there a way to include a Text Search box field within a form where it brings up the name I typed in? For example, in the tableResource I have the name John Doe, and I want to type that into the Search Box Field and display that name only below in the subform.

Would someone be able to help out with this?

I created a dummy format to test whether access works with my school idea project. I still have to fix some buttons to work, but I'd like the search box to allow me to search a name "first name," and then it auto-populates a name below in the subform.

Thank you.

 

[June7]

By "brings up the name" you mean display a record? Certainly. Apply filter to Filter property or code to find record. Unfortunately, I cannot open your db because I am using Access 2010.

 

[chickenwings23]

By "brings up the name" you mean display a record? Certainly. Apply filter to Filter property or code to find record. Unfortunately, I cannot open your db because I am using Access 2010.

Yes, display a record.

 

[MajP]

See if this works

 

[arnelgp]

i change QrysubfrmResource query and add criteria to ResourceName.
you check also the VBA Code on the Change event of the "search" textbox on the form.

 

[chickenwings23]

Thank you both; it worked! Just want I needed

 

[chickenwings23]

i change QrysubfrmResource query and add criteria to ResourceName.
you check also the VBA Code on the Change event of the "search" textbox on the form.

Hello, thanks for helping out, but is there a way for the Search box to search all the components within the table and be able to make edits to the data?

For example, right now, the Search Box allows a user to search "Resource Name," but I would like it to explore all the components
- Resource Name
- Department
- UD, etc.

So a user can type in anything within the search box and be able to edit data at the top and save. Is this possible to conduct?

 

[June7]

Would have to use same criteria for every field with OR operator. Or change data structure to entity-attribute-value model, which has its own challenges.

 

[chickenwings23]

Would have to use same criteria for every field with OR operator. Or change data structure to entity-attribute-value model, which has its own challenges.

MajPs code worked to allow a user to search for just the Name:

Private Sub txtSearch_Change()
Dim fltr As String
Dim rs As DAO.Recordset
fltr = "ResourceName Like '*" & Nz(txtSearch.Text, "") & "*'"
Me.subfrmResource.Form.Filter = fltr
Me.subfrmResource.Form.FilterOn = True
Me.txtSearch.SelStart = Len(Me.txtSearch)
Me.txtSearch.SelLength = 1
End Sub

but I'd like to include Department, etc., within the VBA code as well

 

[June7]

Concatenate for as many fields as you want:

fltr = "ResourceName Like '*" & Nz(txtSearch.Text, "") & "*' OR Department Like '*" & Nz(txtSearch.Text, "") & "*'"

Or build a parameterized query object and use that as RecordSource for form or report.

 

[MajP]

Private Sub frameSearch_AfterUpdate()
  FilterFields
End Sub

Private Sub txtSearch_Change()
  FilterFields
End Sub
Public Sub FilterFields()
  Dim AndOR As String
  Dim fltr As String
  Dim rs As DAO.Recordset
  Me.txtSearch.SetFocus
  Select Case Me.frameSearch
    Case 1
      AndOR = " OR "
    Case 2
      AndOR = " AND "
  End Select
  fltr = "ResourceName Like '*" & Nz(txtSearch.Text, "") & "*'" & AndOR & " ResourceDepartment like '*" & Nz(txtSearch.Text, "") & "*'" & AndOR & " UDorCI like '*" & Nz(txtSearch.Text, "") & "*'"
  Me.subfrmResource.Form.Filter = fltr
  Me.subfrmResource.Form.FilterOn = True
    Me.txtSearch.SelStart = Len(Me.txtSearch)
    Me.txtSearch.SelLength = 1
  Me.txtFilter = Me.Filter
End Sub

 

[arnelgp]

i also add the "criteria" to the Query, QrysubfrmResource.