Keyword Search for a Record and Have Results Show in Same Form

[Kiley]

I am trying to build a form where users can search for their customer account. We aren't always sure how the customer may be entered (sometimes "The" or "A" at the beginning can cause issues with filters), so I created a query to do a LIKE search, and I confirmed the query works with the form I bound it to. What I would like to do is, from one single form, run the keyword search query and then have the results show up in that same form, and then be able to open the customer account from those results. I have built continuous forms with success, and have built a form to open a customer from that continuous form, so I feel like I have almost all of the pieces, I just can't figure out how to put them all together...



Thanks in advance! Novice user here.

[ranman256]

in a continuous record form, put a text box in the header.
all record show until you type in the box, txtFind, then the afterupdate event will filter the results...

sub txtFind_afterupdate()
   if IsNull(txtFind) then
       me.filterOn = false
  else
       me.filter = "[LastName] like *" & txtFind & "*"
       me.filterOn = true
  endif
end sub

[Kiley]

I can't seem to be getting this to work. So I have created a form that uses a query as a record source. The control source I am using is "InsuredName" and in the data tab in the control source box I confirmed it is typed in there on the property sheet. In the header of this form I added an unbound text box and on its property sheet on the other tab, i named it "txtFind". on the event tab for this text box i opened to add code, and i pasted the code you provided and changed "[LastName]" to "[InsuredName]" to match my control source. when i save and close and open the form and type something in the box, then hit enter, it is giving me the error:

"Run-time error '3075: Syntax error (missing operator) in query expression '[InsuredName] like *camp*' " (camp is the beginning of the insured name i searched for. If i click debug to open, it highlights the line i changed the control name on, but since last name is not in any of my tables/queries/etc, i am not sure how i messed up... Also, I AM new to this, but i thought maybe you had sent a typo, as there was a " missing before your first * on that code line. i tried with and without and it still didnt work.

[orange]

Watch this free video on keyword search by Steve Bishop

[Kiley]

That is so funny you send that, that is exactly the video I watched yesterday and I was finally able to get it!! So excited Thank you!

[orange]

Glad you have it working.
Steve has over 70 videos related to Access and vba - All very good.

[Kiley]

Yes, I am going to have to check out some of the others. Thanks!

[orange]

I'm sure you'll also find this 8 part Database series by Dr. Daniel Soper useful also.

[Kiley]

Thanks! I started watching his tutorials and they are very helpful as well.