Form Find/filter

N

Nancy

I have a form which contains an:

id: [fld]
name: [fld]

Other from the same table below.

My user wants to search on either field (not both), the id or name field and
have the data associated with it display. They also want the ability to
insert and delete data into the datable from this form. They want to use a
text box not a combo box because there are hundreds of thousands of rows of
data and they do know what they are going after.

I tried the form by filter, but that allows me to only query off of the id
field. I am trying to mimic the same functionality, but allowing the users
to query on one of two fields.
Is there a simple way for me to do this? Is there something I need to do to
the table?

Thanks so much!
 
S

SteveS

Hi Nancy,

Here is one way you could make a search form.

I am guessing that ID is a number and Name is text . (duh)

In the following code examples, you will need to change the names of the
fields to your names.

----
**Create a new form. Make the Form Header/Footer visible:
In the menu, select VIEW -> Form Header/Footer
(I always set the form header background to white)

In the properties dialog for the form,
Click on the "Data" tab
Add a recordsource to the form. (if you use a query, the recordset can
be sorted)

Click on the "Format" tab.
Change the default view to Continuous Forms.
---

**Add two unbound text boxes to the form header
Name one of the text boxes "tbID". Edit the label

Add a "GotFocus" event:

Private Sub tbID_GotFocus()
Me.tbLName = Null
End Sub
---

Name the other text box "tbName". Edit the label
Add a "GotFocus" event:

Private Sub tbLName_GotFocus()
Me.tbID = Null
End Sub
---

**Add two buttons in the form header.

Change the caption property of one button to "Filter"
Change the name property to "btnDoFilter" (no spaces)

Create a "CLICK" event. Paste this code:

If Not IsNull(Me.tbID) Then
Me.Filter = "id = " & Me.tbID
Me.FilterOn = True
ElseIf Not IsNull(Me.tbName) Then
Me.Filter = "[lastname] Like '" & Me.tbName & "*'"
Me.FilterOn = True
Else
Me.Filter = ""
Me.FilterOn = False
Me.Requery
End If
Me.Requery

---

Change the caption property of the other button to "No Filter"
Change the name property to "btnNoFilter" (no spaces)

Create a "CLICK" event. Paste this code:

Me.tbID = Null
Me.tbLName = Null
Me.Filter = ""
Me.FilterOn = False
Me.Requery

---

Add the fields you want to see/edit to the details section

Save the form.

Open it and try searching......

In the name textbox, if you enter "BL", it will return every record that
*begins with* "BL". It searchs using "Like BL*". (case insensitive)

If you have problems, email me and I will send you an example.

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Nancy said:
I have a form which contains an:

id: [fld]
name: [fld]

Other from the same table below.

My user wants to search on either field (not both), the id or name field and
have the data associated with it display. They also want the ability to
insert and delete data into the datable from this form. They want to use a
text box not a combo box because there are hundreds of thousands of rows of
data and they do know what they are going after.

I tried the form by filter, but that allows me to only query off of the id
field. I am trying to mimic the same functionality, but allowing the users
to query on one of two fields.
Is there a simple way for me to do this? Is there something I need to do to
the table?

Thanks so much!
Click to expand...
 
Top