Search form via filter

[SamT]

I can't seem to get my form to work. It is designed to search my DB for a
record where the key field is provided by the user.

I have a table that is listing employees. The key field is their NT login id
(as it will be different for every user.) As such, it would be easiest for my
users to be able to type in the NT login of the person in question and click
a button to pull up their record.

To accomplish this, I have created a form that I have named "Search Form"
and on this form, I have displayed all of the pertinent fields from my
primary table. I also have an unbound text box labeled "SearchControl"

I have a button next to the Search Control text box that is coded as follows:

Private Sub Command5_Click()
Me.Filter = "NT = " & Me.SearchControl
Me.FilterOn = TRUE
Me.Requery
End Sub

"NT" is the name of the existing field from my primary table that contains
employee's nt login ids. By my logic, what I have written is saying the
following:
"When someone clicks this button, set the filter of the form to NT = (what's
in the text box) and filter the form. You should now see the person whose NT
login you searched for."
Is that in fact what I have written? I am really just learning as I go along
here in Access, so any help would be appreciated.

 

[Steve Sanford]

A Filter is like a WHERE clause without the word WHERE. Therefore you need to
have the proper delimiters

If [NT] is text a text type field, try

Me.Filter = "[NT] = '" & Me.SearchControl & "'"


Expanded, it looks like "NT = ' " & Me.SearchControl & " ' "

HTH

 

[sebastico]

Steve
For me is very interesting your code. My question: It is possible to enter
more than one word in the unbound txtbox?

Many thanks

A Filter is like a WHERE clause without the word WHERE. Therefore you need to
have the proper delimiters

If [NT] is text a text type field, try

Me.Filter = "[NT] = '" & Me.SearchControl & "'"


Expanded, it looks like "NT = ' " & Me.SearchControl & " ' "

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

I can't seem to get my form to work. It is designed to search my DB for a
record where the key field is provided by the user.

I have a table that is listing employees. The key field is their NT login id
(as it will be different for every user.) As such, it would be easiest for my
users to be able to type in the NT login of the person in question and click
a button to pull up their record.

To accomplish this, I have created a form that I have named "Search Form"
and on this form, I have displayed all of the pertinent fields from my
primary table. I also have an unbound text box labeled "SearchControl"

I have a button next to the Search Control text box that is coded as follows:

Private Sub Command5_Click()
Me.Filter = "NT = " & Me.SearchControl
Me.FilterOn = TRUE
Me.Requery
End Sub

"NT" is the name of the existing field from my primary table that contains
employee's nt login ids. By my logic, what I have written is saying the
following:
"When someone clicks this button, set the filter of the form to NT = (what's
in the text box) and filter the form. You should now see the person whose NT
login you searched for."
Is that in fact what I have written? I am really just learning as I go along
here in Access, so any help would be appreciated.

 

[BruceM via AccessMonster.com]

Why not use a combo box that has the primary key (NT login iD) as the bound
(hidden) column, and the name as the second (visible column). Row Source
would be something like:

SELECT [NT_ID],[LastName] & ", " & [FirstName] as LastFirst
FROM tblEmployee

Bound Column is 1, Column Count 2, and Column Widths something like 0";1.5"

You would still use code Steve provided, but it would probably be easier for
users to select a name rather than type the ID.

Also, you could go to the record without having to filter the recordset. The
combo box wizard can get you started with that. If you use a filter you will
probably want a way to clear the filter, assuming the form is used also to
move from one record to another without filtering.

Steve
For me is very interesting your code. My question: It is possible to enter
more than one word in the unbound txtbox?

Many thanks

A Filter is like a WHERE clause without the word WHERE. Therefore you need to
have the proper delimiters

[quoted text clipped - 35 lines]

 

[Steve Sanford]

It is a text box, so you can enter whatever you want.

Are you talking about entering two words to search on? If so, would you give
an example??


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

Steve
For me is very interesting your code. My question: It is possible to enter
more than one word in the unbound txtbox?

Many thanks

A Filter is like a WHERE clause without the word WHERE. Therefore you need to
have the proper delimiters

If [NT] is text a text type field, try

Me.Filter = "[NT] = '" & Me.SearchControl & "'"


Expanded, it looks like "NT = ' " & Me.SearchControl & " ' "

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

I can't seem to get my form to work. It is designed to search my DB for a
record where the key field is provided by the user.

I have a table that is listing employees. The key field is their NT login id
(as it will be different for every user.) As such, it would be easiest for my
users to be able to type in the NT login of the person in question and click
a button to pull up their record.

To accomplish this, I have created a form that I have named "Search Form"
and on this form, I have displayed all of the pertinent fields from my
primary table. I also have an unbound text box labeled "SearchControl"

I have a button next to the Search Control text box that is coded as follows:

Private Sub Command5_Click()
Me.Filter = "NT = " & Me.SearchControl
Me.FilterOn = TRUE
Me.Requery
End Sub

"NT" is the name of the existing field from my primary table that contains
employee's nt login ids. By my logic, what I have written is saying the
following:
"When someone clicks this button, set the filter of the form to NT = (what's
in the text box) and filter the form. You should now see the person whose NT
login you searched for."
Is that in fact what I have written? I am really just learning as I go along
here in Access, so any help would be appreciated.

 

[sebastico]

Steve
I have 3 tables
TOOB (OBID as PK and Field1, Field2, FIeld3 ,Field4 all string), bounded 1
to many to TOOBTKrwds
TOOBTKrwds (OBID and WrdID as foreign Keys)
TWrds(WrdID as PK, WrdNam) bounded 1 to many to TOOBTKrwds

I'm trying this code
If Not IsNull(Me.txtWrds) Then
strWhere= & ([Wrd]""" & Me.txtMe.txtWrds & """) AND"
End If

User must enter words in txtWrds then cmd button trigger the search to
TOOBTKrwds and displays in Detail Section of the form.
The row source for the form is a query (Query1).

Thank you very much

It is a text box, so you can enter whatever you want.

Are you talking about entering two words to search on? If so, would you give
an example??


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

Steve
For me is very interesting your code. My question: It is possible to enter
more than one word in the unbound txtbox?

Many thanks

A Filter is like a WHERE clause without the word WHERE. Therefore you need to
have the proper delimiters

If [NT] is text a text type field, try

Me.Filter = "[NT] = '" & Me.SearchControl & "'"


Expanded, it looks like "NT = ' " & Me.SearchControl & " ' "

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


:

I can't seem to get my form to work. It is designed to search my DB for a
record where the key field is provided by the user.

I have a table that is listing employees. The key field is their NT login id
(as it will be different for every user.) As such, it would be easiest for my
users to be able to type in the NT login of the person in question and click
a button to pull up their record.

To accomplish this, I have created a form that I have named "Search Form"
and on this form, I have displayed all of the pertinent fields from my
primary table. I also have an unbound text box labeled "SearchControl"

I have a button next to the Search Control text box that is coded as follows:

Private Sub Command5_Click()
Me.Filter = "NT = " & Me.SearchControl
Me.FilterOn = TRUE
Me.Requery
End Sub

"NT" is the name of the existing field from my primary table that contains
employee's nt login ids. By my logic, what I have written is saying the
following:
"When someone clicks this button, set the filter of the form to NT = (what's
in the text box) and filter the form. You should now see the person whose NT
login you searched for."
Is that in fact what I have written? I am really just learning as I go along
here in Access, so any help would be appreciated.

 

[sebastico]

Bruce
In the table where database storages there are already 650 words each
codified. I think in my case combobox could be not easy to displays in the
form.
Following my post, user will have an idea of the words they have to enter in
the txtbox.

Your code seem to be quite useful and I will test it to learn more about
programming with VBA.
Any suggestion is very welcome

Thank you so much.

Why not use a combo box that has the primary key (NT login iD) as the bound
(hidden) column, and the name as the second (visible column). Row Source
would be something like:

SELECT [NT_ID],[LastName] & ", " & [FirstName] as LastFirst
FROM tblEmployee

Bound Column is 1, Column Count 2, and Column Widths something like 0";1.5"

You would still use code Steve provided, but it would probably be easier for
users to select a name rather than type the ID.

Also, you could go to the record without having to filter the recordset. The
combo box wizard can get you started with that. If you use a filter you will
probably want a way to clear the filter, assuming the form is used also to
move from one record to another without filtering.

Steve
For me is very interesting your code. My question: It is possible to enter
more than one word in the unbound txtbox?

Many thanks

A Filter is like a WHERE clause without the word WHERE. Therefore you need to
have the proper delimiters

[quoted text clipped - 35 lines]

Is that in fact what I have written? I am really just learning as I go along
here in Access, so any help would be appreciated.