Create a Subform to Search Main forms records

[KR]

I am having the worst time trying to figure this out. I have a main form
with all the information I want to be able to search and I want to create a
subform that pops up and lets me select a field from a drop down box and then
enter below in a text box criteria that matches any part of the field I have
selected but I want the main form to return my results with all the records
that match my search.

Here is all of my information:

Mainform:
Phone Log
Fields:
Phone_Call_Received (Date)
Patients_Name
Callers_Name
Phone_1
Phone_2
SerialN
CardN
Sent (this is a drop down that allows you to select "Card" or "Form")
Date_Sent (Date)
Call_Closed (this is a drop down that allows you to select "Y" or "N"

My Subform pops-up by a cmd button I created that says Search Records. On
the subform I have the drop-down box created with all of the field's names
and below I have an unbound text box to enter the information I am looking
for.

Subform Name:
Phone Log Search
Fields:
FSrch (this is the drop down box that lists the fields from the main form)
RSrch (unbound text box that info. is entered in to search any part of
the field selected)

Last but not least is there anyway that in the drop down box on my subform I
can have the fields that are listed listed as normal names instead of having
the (_) in the title but still have it refer to the field selected?
For instance:
Phone_Call_Received = would show in the drop down box as "Date Received"
Patients_Name = Patients Name
Callers_Name = Callers Name
Phone_1 = Phone Number
Phone_2 = Phone/Fax Number
Call_Closed = Call Closed
SerialN = Serial #
CardN = Card #
Left_Message = Left Message
Date_Sent = Date Sent
Sent = Sent
Anyone who can help me with all of this is a genius and I would be so
greatful!!!

Thanks,
KR

 

[Allen Browne]

Take a look at this and see if it will do what you want:
Find as you type
at:
http://allenbrowne.com/AppFindAsUType.html

The article contains an application you can download (free.) You copy the
code into your database, and then the combo (for selecting the field) and
the text box (for entering the value) onto any form. Then as you type a
value, your form filters so it shows only those records that match.

You do not need to modify the code.

 

[KR]

That is what I currently have but I really want the search form to be
seperate from the main form.

 

[Allen Browne]

You can do it, but you'll have to program your own.

Your code will need to build a filter string, and apply it to the other
form. This is the basic logic:

Dim strWhere As String
Dim strDelim As String
strDelim = """"
strWhere = Me.Combo1 & " = " strDelim & Me.txtWot2Find & strDelim
With Forms("Form1")
.Filter = strWhere
.FilterOn = True
End With

Assumptions:
a) Combo1 contains the field name, and txtWot2Find contains the value to
search for.

b) Combo1 refers to a field of type Text. If it's a Number field, use:
strDelim = ""
or if it's a Date/Time Field, use:
strDelim = "#"
and you may need to consider if the date format might be non-US.

c) Your code should check if the combo or text box are null.

d) Can you assume the other form will be open? Might it contain editing that
cannot be saved (e.g. required field not filled in)?

e) Needs error handling.