Help me create a serach to search my form PLEASE NO COMBO BOX

[KR]

My Table is called Phone Log
My Main Form is called Phone Log Form
My Subform is called Phone Log SUBFM

When I open my Main Form (Phone Log FM) I click on a command button I
created and my Subform pops up. On my subform I have 4 unbound text boxes
(Patients_Name, Callers_Name, Serial#, and Card#). I want to be able to type
in any of the unbound boxes and click on my search button at the bottom of my
form and if what I typed in matches I want my Main Form (Phone Log) to pop
back up with that record on it. Can anyone please help me I have been
surfing the internet for days and nights trying to figure this out.

I also have a another form 2009 SUBFM and I am trying to create a text box
search where i can type the date in and it will go to the record on this form
that matches the date I typed in.

Any help is MUCH MUCH MUCH appreciated!!!

 

[Jeanette Cunningham]

Hi KR,

Here is some code from one of my apps.
The code is for a text box called txtFilterPart and the field is called
PartDescr

You can just repeat these lines-->
If Len(Me.txtFilterPart & vbNullString) > 0 Then
strWhere = strWhere & "(PartDescr Like """ & Me.txtFilterPart &
"*"") AND "
End If

for each text box on your search form.

---------------------------
Private Function FilterMe()
' Statement (A) is a desirable precaution
' whenever manipulating focus on controls
On Error Resume Next ' (A)

Dim strWhere As String
Dim lngLen As Long
Dim ctlLast As Access.Control


' Last active control
Set ctlLast = Screen.ActiveControl

' Take focus away from filter controls so that
' the contents get updated
Me.cmdReset.SetFocus

If Len(Me.txtFilterPart & vbNullString) > 0 Then
strWhere = strWhere & "(PartDescr Like """ & Me.txtFilterPart &
"*"") AND "
End If

'Chop off the trailing " AND ", and use the string as the form's Filter.
'See if the string has more than 5 characters (a trailng " AND ") to
remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
Me.FilterOn = False

Else
strWhere = Left$(strWhere, lngLen)
'debug.Print strWhere
'Finally, apply the string as the form's Filter.
End If
ctlLast.SetFocus
With Me
.Filter = strWhere
.FilterOn = True
End With
End Function

 

[KR]

For some reason it's not working

Hi KR,

Here is some code from one of my apps.
The code is for a text box called txtFilterPart and the field is called
PartDescr

You can just repeat these lines-->
If Len(Me.txtFilterPart & vbNullString) > 0 Then
strWhere = strWhere & "(PartDescr Like """ & Me.txtFilterPart &
"*"") AND "
End If

for each text box on your search form.

---------------------------
Private Function FilterMe()
' Statement (A) is a desirable precaution
' whenever manipulating focus on controls
On Error Resume Next ' (A)

Dim strWhere As String
Dim lngLen As Long
Dim ctlLast As Access.Control


' Last active control
Set ctlLast = Screen.ActiveControl

' Take focus away from filter controls so that
' the contents get updated
Me.cmdReset.SetFocus

If Len(Me.txtFilterPart & vbNullString) > 0 Then
strWhere = strWhere & "(PartDescr Like """ & Me.txtFilterPart &
"*"") AND "
End If

'Chop off the trailing " AND ", and use the string as the form's Filter.
'See if the string has more than 5 characters (a trailng " AND ") to
remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
Me.FilterOn = False

Else
strWhere = Left$(strWhere, lngLen)
'debug.Print strWhere
'Finally, apply the string as the form's Filter.
End If
ctlLast.SetFocus
With Me
.Filter = strWhere
.FilterOn = True
End With
End Function

 

[John W. Vinson]

For some reason it's not working

"Not Working" isn't much help, KR. What is your actual code? What are your
actual control names? Do you get compile errors, wrong results, no results,
your monitor catches fire...?

Jeanette's very good but she can't solve a problem she can't see!

 

[Jeanette Cunningham]

That function FilterMe is called from the Change event of the filter text
box.
I also set the cursor to go to the end of the field for the database.
Each time user types in the text box, the cursor moves to the end of the
typing ready for the next letter.
That is in the access options for 'behavior entering field'.
Does this help?

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[AccessVandal via AccessMonster.com]

First, this subform is not a subform. It is a PopUp form without any record
source and the controls are unbound. “#†is a reserved character in Access
avoid naming them in your controls,fields in your tables and queries.

When I open my Main Form (Phone Log FM) I click on a command button I
created and my Subform pops up. On my subform I have 4 unbound text boxes

You can also refer oldblindpew post here.
http://groups.google.com.pk/group/m...s.forms/browse_thread/thread/6ee3933162a7a831
#

If you want to use Jeanette’s code, you have to modify the code for an
unbound form.

You’ll need to change this part
Dim frm as Form ‘add this line above the rest of the Dims
Set frm = Forms!YourFromName ‘add this line just below the Dims
With frm ‘change Me to frm
.Filter = strWhere
.FilterOn = True
End With

As for your 4 filter criteria, Jeanette’s code use only 1 control. You may
have to use total of 4 If Then.

Your function may now look like this.
---------------------------
Private Function FilterMe()

On Error Resume Next

Dim strWhere As String
Dim lngLen As Long
Dim frm as Form
Set frm = Forms!YourFormName ‘change “YourFormNameâ€
’------controls filtering-----------------------------------------------------
------------
If Len(Me.textbox1 & vbNullString) > 0 Then
strWhere = strWhere & "(column1 Like """ & Me.textbox1 &
"*"") AND "
End If

If Len(Me.textbox2 & vbNullString) > 0 Then
strWhere = strWhere & "(column2 Like """ & Me.textbox2 &
"*"") AND "
End If

If Len(Me.textbox3 & vbNullString) > 0 Then
strWhere = strWhere & "(column3 Like """ & Me.textbox3 &
"*"") AND "
End If

If Len(Me.textbox4 & vbNullString) > 0 Then
strWhere = strWhere & "(column Like """ & Me.textbox4 &
"*"") AND "
End If
‘-----------------------------------------------------------------------------
---------------
'Chop off the trailing " AND ", and use the string as the form's Filter.
'See if the string has more than 5 characters (a trailng " AND ") to
remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
frm.FilterOn = False

Else
strWhere = Left$(strWhere, lngLen)
debug.Print strWhere 'i had remove the single qoute,put back after
testing
'Finally, apply the string as the form's Filter.
End If

With frm
.Filter = strWhere
.FilterOn = True
End With
End Function
---------------------------

I’m not sure about the “Chop off the trailing†part, so you’ll have to check
in the immediate window (ctrl + g to view the window) of your strWhere and
correct the string if necessary.
It’s more likely I will use Right() or Right$() function instead of Left$()
to remove the unwanted strings.

You will also need to change if the datatype of each control if it is
different for the each “If Then†in the strWhere criteria or change it to
some thing like “(column1 = ‘†… or “(column1 <> “ ….

And in the command button event, call the function

Private sub command0_click()
FilterMe
End sub

PS. do not copy and paste the code as it may not work becos the the browser
word wrapping.