Using a Textbox Field to search a Name, Then show a Continuous subform to show result

[RyanScott]

I can not for the life of me figure out how to Type in a Customer Name or Part of a Customer Name, Click findRecord_btn and it show the results in the CF_Result_sub form. and If there are no matching records then Show Label53. Here is what I am working with. Any help would be of great value!!!



MSACCESS 2002

Table: Customer_tbl
Fields: Customer_ID, First Name, Last Name

Main Form: FindCustomer_form
Fields: firstName_txt " This is where the customer would type in a customer first name like, (Justin, Just, Ju, or J)
Button: findRecord_btn "This is the button to search Records"
Label53: "Simply states, No Customers Found."

Sub Form: CF_Results_sub "This will show every record that matches "Justin, just, ju, or j"
On the subform I have three fields that have the (Customer_ID, First Name, and Last Name)

[John_G]

Hi -

You could try something like this.

Assume these:
The control containing the text to seach for is SearchText
The main form control containing the subform is ResultSubform


In the on-click event of the FindRecord_btn you might have:

Dim Where_SQL as string, CheckCount as integer
'
' Put together the Where clause
'
Where_SQL = "[First Name] Like '" & SearchText & "*'"
'
' Check if there are any records that qualify
'
CheckCount = Dcount("*","CustomerTbl", Where_SQL)
if CheckCount = 0 then
label53.visible=true
exit sub
else
label53.visible=false
endif
'
' Set the record source for the subform, and requery
' Retrieving all fields is more efficient, even if they are not displayed
'
Where_SQL = "Select * from Customertbl WHERE " & Where_SQL
me!ResultSubform.form.recordsource = Where_SQL
me!ResultSubform.form.requery


That should get you started. Note that this does not use the actual name of the sub-form anywhere.

HTH

John

[RyanScott]

Thanks for taking the time to help me out, I've spent two days on this and have never had to post a HELP, before... so thank you and I'm going to see if I can get this working with your help... I'll Let you know how it turned out... Thanks again!

-Ryan

[RyanScott]

Used the code, made a few adjustments as you suggested, and BINGO! Like magic it worked like a charm. Not to anyone trying to do the same... Make a form that is a blank form that has no Record Source, (This will stop Access from Linking the Form to the Subform), Then Remove any links from the subform to the New form you created.

Great Help my friend and I owe you one!
-Ryan

[John_G]

Hi Ryan -

Glad I could help you out.

All the best

John