Search Button that can search 1 or 2 key words at a time

[adminscr]

I created a work database that tracks patient information in access 2013. I created a form (PatientForm)which has 5 tab controls. I want to create a search button inside the Patient Form to the right of the tab controls by Last Name (PtLast) and First Name (PtFirst). I already have an add button, delete button, menu button and save button and each record is a patient. I also want to make sure I can search with just the first letter of last name too.

I do have a search button but it only searches the last name. I have to put the cursor on the last name text box and press search and the find and replace box shows up and you just type the last name and that person appears in the form. We have a lot of people with the same last name so entering next is a pain. I want the search button to search in the patient form and just go to that patient and still be able to go to the bottom and see all the records, is there a way to do this? I don't know any programming in database, I have done a few things but it was copying and pasting from others online posts.

Can a search button be created with this criteria? Any help would be much appreciated!



Thanks and Cheers, Lori

[Bulzie]

So you want to put in a value in Last name and or a value in First name and search for the record based on if something is entered in either or both fields? What is the key field in the table? Could you create a Combo box where you combine Lastname & "," & Firstname to search by?

[adminscr]

So you want to put in a value in Last name and or a value in First name and search for the record based on if something is entered in either or both fields? What is the key field in the table? Could you create a Combo box where you combine Lastname & "," & Firstname to search by?

Yes you are correct with what I would like to do. I assume when you ask what the key field in the table is, you mean the primary key which is an autonumber and is called PtDataID but we don't actually use this number to search because our charts are by their last and first names. When you ask if I could create a combo box, do you mean create the combo box as a search tool not changing my 2 fields into one? I hope this makes sense?

[cbende2]

There are a few different ways that you can do this. I have gone about it in this way...

Two blank text boxes(FNameTxt and LNameTxt), and three check boxes. One checkbox is "First Name Only Search" next one is "Last Name Only Search" and third one is "First and Last Name Search".

I then have a query that checks which box is checked off, and performs the search, which then refreshes form to show data.


I like having the option of check boxes, it allows me to know for sure what exactly I am searching.

Others may simply use two text boxes and a button that runs their query. The logic might be a little tougher with this one though.

[adminscr]

I like this idea with the check boxes, I will try this and let you know. Thanks!

[orange]

You may find this Allen Browne search article of value.

[adminscr]

This is way too complicated for me, I don't know any code. Thanks though. Lori

[adminscr]

You answered my post and it seems fairly simple but it has been along time when I have to use expressions which I assume I would need to do within the query, so I am working on it trying different things. So I have not really solved my issue quite yet. Thanks. Lori

[ortizimo]

Heres what I did for this...keep in mind im no expert.

This will look within the FORM which if you have queries with relationships it will work.

1. on your MAIN form, create an text box (unbound)
2. on your MAIN form, create a button (unbound)
3. go to DESIGN VIEW and click on the UNBOUND BUTTON and go to its properties
4. select the EVENT tab and click on ... icon
5. select MACRO BUILDER
6. in ADD NEW ACTION look for and select APPLYFILTER
7. give it a name if you want to (optional)
8. on WHERE CONDITION type the following: [First_Name] Like '*' & [Forms]![frmMain]![Search_Box] & '*' Or [Last_Name] Like '*' & [Forms]![frmMain]![Search_Box] & '*' Or [Address] Like '*' & [Forms]![frmMain]![Search_Box] & '*'
9. EXIT and SAVE
10 go back to the form and go into VIEW mode

Let me explain the string. The FIRST NAME is somewhere in my MAIN FORM then look for anything in it (Like '*') AND include FORMS and in it find (!) the main form and in it find (!) the text box that I'm using to search (SEARCH_BOX) AND in it type anything you are looking for.

1. Also place another UNBOUND button next to the Search button and in PROPERTIES in the EVENT TAB select CODE BUILDER.
2. in there place the following code:

Private Sub cmdClear_Click()
' Use (= Null or = "") to clear the Search_Box'
Me.Search_Box = Null
' Use (Filter = "" or Filter.On = FALSE) to reset the filtered datasheet '
Me.Filter = ""
End Sub

the lines that use a single quote are comments. I named my button cmdClear when I created it.

[orange]

adminscr,

There are some youtube videos by austin72406 that deal with Search. If you watch and follow his code/logic, they may help you with some concepts and coding.
There are many videos and articles on introduction to vba. Sooner or later you will want/need to use some vba.
Good luck.

[adminscr]

Thanks so much. It all worked out. Cheers, Lori