Search Text Field and Button

[Bizzy211]

So, I've been trying to accomplish this, but to no avail. I am trying to create a Button that would search a specific table for any text string I enter in an unbounded-text field. Can this be accomplished Via a macro? Also, I do not want same entry appearing multiple times. Can anyone assist? Thanks in advance.

 

[Alansidman]

When you search the specific table, are you wanting to search a specific field? Suggest you be more descriptive in your needs.

 

[Bizzy211]

When you search the specific table, are you wanting to search a specific field? Suggest you be more descriptive in your needs.

Thanks for your reply. No I want to search all fields withing the one form.

 

[Bizzy211]

Any help would be appreciated.

 

[Mihail]

The Find and Replace tool will work here too. (CTRL+F)

 

[Bizzy211]

To be more specific, is there an ApplyFilter macro I can use? Right now I am only able to search one field on a form using this command

[Notes] Like "*" & [Forms]![Search Query]![Text17] & "*"

The other fields are [Description], [Opened By], and [Assigned To].

I have not found out a way to filter through all fields to search for a text that matches what's in [Text17]

Any ideas?

 

[Mihail]

So, you need to filter NOT to search. This are two different tasks.
Also, I'm not sure that you know the difference between macro and VBA in Access (in Excel is the same thing).

By using VBA this can be a solution:
Design a public function (in a standard module) that return a boolean value.

Public Function FilterRecords(N As String, D As String, OB As String, AT As String) As Boolean

Dim FindWhat As String
  FindWhat = Forms!Search_Query!Text17 & ""

  If FindWhat = "" Then
    FilterRecords = True
Exit Function
  End If

  FilterRecords = True 'Perform OR . Change to [B]False [/B]if you wish AND

  If Instr(1,N,FindWhat) Then
Exit Function
  End If

  If Instr(1,D,FindWhat) Then
Exit Function
  End If

  If Instr(1,OB,FindWhat) Then
Exit Function
  End If

  If Instr(1,AT,FindWhat) Then
Exit Function
  End If

  FilterRecords = False 'Perform OR . Change to [B]True [/B]if you wish AND
End Function

Base your form to a query.
In this query add one more field SelectThisRecord:FilterRecords([Notes],[Description],[OpenedBy],[AssignedTo])
In the Criteria put True.

In the form use the AfterUpdate event for the Text17 control or (better) design a button and use the Click event to requery the form.

Note please two things:
1) I have removed the spaces from the names. You should do the same in your database. If you wish to keep actual names you must adapt the code to the names with spaces. Don't ask me how to do this because I don't know.
2) This code is not tested.

Good luck !

 

[Bizzy211]

Thanks for trying to help, but I am stuck. My knowledge is not there when it comes to Access and macros/vba/modules. I can share my database to give a better idea of what i'm trying to accomplish.

 

[Mihail]

Do it now.

 

[Bizzy211]

Click on the Search Tool button in case list and it will take you to my search (split) form. I want the button to search the Search Query for whatever is in the text box and display it down below. I also can't get the refresh to work correctly. Thanks for you help.

 

[Mihail]

Is this what you are looking for ?

I can't find Notes field in your query

 

[Bizzy211]

Is this what you are looking for ?

I can't find Notes field in your query

Mihail,

Once again thank you for your help. It's almost there. The other column is Comments Not Notes. For some reason if I search, let's say Jackson - only one result shows up, but in the unfiltered query there should be several that match Jackson (Opened by, Assigned To, some job descriptions have Jackson in it.. etc). Any idea?

Lastly, In the module I notice Fasignedto - is that spelled correctly?

Once again thank you for your help.

 

[Bizzy211]

I don't know if it's too late for this but it may be worth a look access-programmers.co.uk/forums/showthread.php?t=188663 here

Thanks for your reply AccessBlaster. The thing is, I want the results to be shown on the bottom of my split form. Not in a drop down box. The database Mihail assisted me on is almost there. A few issues occur, but it is really close to what I am looking for.