Filtering Subform

[Pierre]

I would like to apply a simple filter to a subform from the Mainform.
Main From = SearchPage
Sub Form = Cat Codes subform
Filter Box = Text49
Filter Field = Definition
Filter Command = Command51_Click

Code that I’m trying to use and is not working. Results is a blank subform
with no filtered records.

Private Sub Command51_Click()
Me.[Cat Codes Subform].Form.Filter = “Definition =’†& Me.Text49 & “’â€
Me.[Cat Codes Subform].Form.FilterOn = True
Me.[Cat Codes Subform].Form.Refresh
End Sub

NOTE: definition is a group of words so I need to be able to search for any
word within the field.

 

[Minton M]

I would like to apply a simple filter to a subform from the Mainform.
Main From = SearchPage
Sub Form = Cat Codes subform
Filter Box = Text49
Filter Field = Definition
Filter Command = Command51_Click

Code that I'm trying to use and is not working. Results is a blank subform
with no filtered records.

Private Sub Command51_Click()
Me.[Cat Codes Subform].Form.Filter = "Definition ='" & Me.Text49 & "'"
Me.[Cat Codes Subform].Form.FilterOn = True
Me.[Cat Codes Subform].Form.Refresh
End Sub

NOTE: definition is a group of words so I need to be able to search for any
word within the field.

Why not use the main form to generate a new recordsource for the
subform? Simply build your SQL query based upon the selection criteria
and set Me.sfrMySubform.Form.Recordsource=mySQL.

Btw, you don't need to do a refresh once calling FilterOn=TRUE. The
reason I prefer the recordsource approach is that it's easier to see
when you have a bad query that can result in a blank form.

-- James

 

[Pierre]

first of all, thank you for your reply. But I'm not sure I completely
understand what you mean by building a simply SQL. I am really not all that
good with Coding. I was trying to use the one I posted from one I found
searching for an answer to my current issue. So if it is not too much to
ask. Can you give me a run down of how I can accomplish that.
--
Work is sometimes hard....but someone has to do it.

I would like to apply a simple filter to a subform from the Mainform.
Main From = SearchPage
Sub Form = Cat Codes subform
Filter Box = Text49
Filter Field = Definition
Filter Command = Command51_Click

Code that I'm trying to use and is not working. Results is a blank subform
with no filtered records.

Private Sub Command51_Click()
Me.[Cat Codes Subform].Form.Filter = "Definition ='" & Me.Text49 & "'"
Me.[Cat Codes Subform].Form.FilterOn = True
Me.[Cat Codes Subform].Form.Refresh
End Sub

NOTE: definition is a group of words so I need to be able to search for any
word within the field.

Why not use the main form to generate a new recordsource for the
subform? Simply build your SQL query based upon the selection criteria
and set Me.sfrMySubform.Form.Recordsource=mySQL.

Btw, you don't need to do a refresh once calling FilterOn=TRUE. The
reason I prefer the recordsource approach is that it's easier to see
when you have a bad query that can result in a blank form.

-- James

 

[Minton M]

first of all, thank you for your reply. But I'm not sure I completely
understand what you mean by building a simply SQL. I am really not all that
good with Coding. I was trying to use the one I posted from one I found
searching for an answer to my current issue. So if it is not too much to
ask. Can you give me a run down of how I can accomplish that.
--
Work is sometimes hard....but someone has to do it.

I would like to apply a simple filter to a subform from the Mainform.
Main From = SearchPage
Sub Form = Cat Codes subform
Filter Box = Text49
Filter Field = Definition
Filter Command = Command51_Click
Code that I'm trying to use and is not working. Results is a blank subform
with no filtered records.
Private Sub Command51_Click()
Me.[Cat Codes Subform].Form.Filter = "Definition ='" & Me.Text49 & "'"
Me.[Cat Codes Subform].Form.FilterOn = True
Me.[Cat Codes Subform].Form.Refresh
End Sub
NOTE: definition is a group of words so I need to be able to search for any
word within the field.

Why not use the main form to generate a new recordsource for the
subform? Simply build your SQL query based upon the selection criteria
and set Me.sfrMySubform.Form.Recordsource=mySQL.

Btw, you don't need to do a refresh once calling FilterOn=TRUE. The
reason I prefer the recordsource approach is that it's easier to see
when you have a bad query that can result in a blank form.

-- James

Sure, no problem!

There's nothing wrong with the code you've written but there are a
couple of caveats. The filter is going to match your Definition field
exactly (just like a WHERE clause) so if Definition='Smith' and you're
searching for Smi and expecting a partial match, it won't work. You'll
need to use a LIKE evaluation instead of =. I don't know if this is
happening in your case. This is true also if the field really contains
"Smith " (with extra spaces).

The way to implement my cheap and cheerful way is to do the following:

Private Sub Command51_Click()

Me.[Cat Codes Subform].Form.Recordsource = "Select * from
MyTableName where Definition='" & Me.Text49 & "'"
End Sub

 

[Minton M]

first of all, thank you for your reply. But I'm not sure I completely
understand what you mean by building a simply SQL. I am really not all that
good with Coding. I was trying to use the one I posted from one I found
searching for an answer to my current issue. So if it is not too much to
ask. Can you give me a run down of how I can accomplish that.
--
Work is sometimes hard....but someone has to do it.

I would like to apply a simple filter to a subform from the Mainform.
Main From = SearchPage
Sub Form = Cat Codes subform
Filter Box = Text49
Filter Field = Definition
Filter Command = Command51_Click
Code that I'm trying to use and is not working. Results is a blank subform
with no filtered records.
Private Sub Command51_Click()
Me.[Cat Codes Subform].Form.Filter = "Definition ='" & Me.Text49 & "'"
Me.[Cat Codes Subform].Form.FilterOn = True
Me.[Cat Codes Subform].Form.Refresh
End Sub
NOTE: definition is a group of words so I need to be able to search for any
word within the field.

Why not use the main form to generate a new recordsource for the
subform? Simply build your SQL query based upon the selection criteria
and set Me.sfrMySubform.Form.Recordsource=mySQL.

Btw, you don't need to do a refresh once calling FilterOn=TRUE. The
reason I prefer the recordsource approach is that it's easier to see
when you have a bad query that can result in a blank form.

-- James

And of course I hit send before I meant to.... anyway, whenever the
recordsource is changed, it automatically reloads the recordset and
refreshes. If you want to use the Filter approach though, can you let
me know if:

1. The subform is bound (it should be but just check).
2. What's the behavior after the filter fails? Can you add to the
subform or does it appear to be locked?
3. Can you provide some sample data from the underlying table (one or
two rows) so I can test it out on my end?

-- James

 

[Ron2006]

first of all, thank you for your reply. But I'm not sure I completely
understand what you mean by building a simply SQL. I am really not all that
good with Coding. I was trying to use the one I posted from one I found
searching for an answer to my current issue. So if it is not too much to
ask. Can you give me a run down of how I can accomplish that.
--
Work is sometimes hard....but someone has to do it.

I would like to apply a simple filter to a subform from the Mainform.
Main From = SearchPage
Sub Form = Cat Codes subform
Filter Box = Text49
Filter Field = Definition
Filter Command = Command51_Click
Code that I'm trying to use and is not working. Results is a blank subform
with no filtered records.
Private Sub Command51_Click()
Me.[Cat Codes Subform].Form.Filter = "Definition ='" & Me.Text49 & "'"
Me.[Cat Codes Subform].Form.FilterOn = True
Me.[Cat Codes Subform].Form.Refresh
End Sub
NOTE: definition is a group of words so I need to be able to search for any
word within the field.

Why not use the main form to generate a new recordsource for the
subform? Simply build your SQL query based upon the selection criteria
and set Me.sfrMySubform.Form.Recordsource=mySQL.

Btw, you don't need to do a refresh once calling FilterOn=TRUE. The
reason I prefer the recordsource approach is that it's easier to see
when you have a bad query that can result in a blank form.

-- James- Hide quoted text -

- Show quoted text -

Try this with the what you have,
Change
Me.[Cat Codes Subform].Form.Filter = "Definition ='" &
Me.Text49 & "'"
to
Me.[Cat Codes Subform].Form.Filter = "Definition = '" & Me.Text49 &
"'"


I added a space AFTER the =' to make it = '


Here is another way that even better

on the subform select Data properties
in the "Link Child Fields" type in "[Definition]"
in the "Link Parent Fields type text49

This makes is a parent child relationship and you don't have to
refresh or do anything or even press the command button. Just tab off
of the text49 field.

Parent child relationship wizard will NOT work, you have to type the
entries in.

Ron

 

[Pierre]

James
I tried that and it did not work. I’m sure I’m just a little slow and not
getting something. But as for the form. Its really just a Form with a text
box and subform on it. The subform has a table with 2 fields (total of 54
records): Cat Codes and Definition. Displayed something like below:

Cat Codes: 99200 Definition: Related to Personalities of others
Cat Codes: 99210 Definition: Related to Activates of others
Cat Codes: 99220 Definition: Leadership Driven by Personal Activates
Record |< 1 > >| >* of 54

The Main form just has the Textbox (text49) where I would like to enter a
word like: “Activates†and get all possible codes (records) to display in
the subform.
What really deicide if the form is bound or not. Its not link to any part
of the database at this point. Nor is the table[Cat Codes] link to any other
records.
HTH

--
Work is sometimes hard....but someone has to do it.

first of all, thank you for your reply. But I'm not sure I completely
understand what you mean by building a simply SQL. I am really not all that
good with Coding. I was trying to use the one I posted from one I found
searching for an answer to my current issue. So if it is not too much to
ask. Can you give me a run down of how I can accomplish that.
--
Work is sometimes hard....but someone has to do it.

I would like to apply a simple filter to a subform from the Mainform.
Main From = SearchPage
Sub Form = Cat Codes subform
Filter Box = Text49
Filter Field = Definition
Filter Command = Command51_Click

Code that I'm trying to use and is not working. Results is a blank subform
with no filtered records.

Private Sub Command51_Click()
Me.[Cat Codes Subform].Form.Filter = "Definition ='" & Me.Text49 & "'"
Me.[Cat Codes Subform].Form.FilterOn = True
Me.[Cat Codes Subform].Form.Refresh
End Sub

NOTE: definition is a group of words so I need to be able to search for any
word within the field.

Why not use the main form to generate a new recordsource for the
subform? Simply build your SQL query based upon the selection criteria
and set Me.sfrMySubform.Form.Recordsource=mySQL.

Btw, you don't need to do a refresh once calling FilterOn=TRUE. The
reason I prefer the recordsource approach is that it's easier to see
when you have a bad query that can result in a blank form.

-- James

And of course I hit send before I meant to.... anyway, whenever the
recordsource is changed, it automatically reloads the recordset and
refreshes. If you want to use the Filter approach though, can you let
me know if:

1. The subform is bound (it should be but just check).
2. What's the behavior after the filter fails? Can you add to the
subform or does it appear to be locked?
3. Can you provide some sample data from the underlying table (one or
two rows) so I can test it out on my end?

-- James

 

[Pierre]

I'm useing Access 2003 and when I go to the Form Page properties Data TAB all
I see is:
Record Source....
Filter.....
Order By....
Allow Filter....
Allow Edits....
Allow Deletions....
Data Entry.....
Recordset Type......
Record Locks.....
Fetch Defaults......
--
Work is sometimes hard....but someone has to do it.

first of all, thank you for your reply. But I'm not sure I completely
understand what you mean by building a simply SQL. I am really not all that
good with Coding. I was trying to use the one I posted from one I found
searching for an answer to my current issue. So if it is not too much to
ask. Can you give me a run down of how I can accomplish that.
--
Work is sometimes hard....but someone has to do it.

I would like to apply a simple filter to a subform from the Mainform.
Main From = SearchPage
Sub Form = Cat Codes subform
Filter Box = Text49
Filter Field = Definition
Filter Command = Command51_Click

Code that I'm trying to use and is not working. Results is a blank subform
with no filtered records.

Private Sub Command51_Click()
Me.[Cat Codes Subform].Form.Filter = "Definition ='" & Me.Text49 & "'"
Me.[Cat Codes Subform].Form.FilterOn = True
Me.[Cat Codes Subform].Form.Refresh
End Sub

NOTE: definition is a group of words so I need to be able to search for any
word within the field.

Why not use the main form to generate a new recordsource for the
subform? Simply build your SQL query based upon the selection criteria
and set Me.sfrMySubform.Form.Recordsource=mySQL.

Btw, you don't need to do a refresh once calling FilterOn=TRUE. The
reason I prefer the recordsource approach is that it's easier to see
when you have a bad query that can result in a blank form.

-- James- Hide quoted text -

- Show quoted text -

Try this with the what you have,
Change
Me.[Cat Codes Subform].Form.Filter = "Definition ='" &
Me.Text49 & "'"
to
Me.[Cat Codes Subform].Form.Filter = "Definition = '" & Me.Text49 &
"'"


I added a space AFTER the =' to make it = '


Here is another way that even better

on the subform select Data properties
in the "Link Child Fields" type in "[Definition]"
in the "Link Parent Fields type text49

This makes is a parent child relationship and you don't have to
refresh or do anything or even press the command button. Just tab off
of the text49 field.

Parent child relationship wizard will NOT work, you have to type the
entries in.

Ron

 

[Ron2006]

I'm useing Access 2003 and when I go to the Form Page properties Data TAB all
I see is:
Record Source....
Filter.....
Order By....
Allow Filter....
Allow Edits....
Allow Deletions....
Data Entry.....
Recordset Type......
Record Locks.....
Fetch Defaults......
--
Work is sometimes hard....but someone has to do it.

first of all, thank you for your reply.  But I'm not sure I completely
understand what you mean by building a simply SQL.  I am really not all that
good with Coding.  I was trying to use the one I posted from one I found
searching for an answer to my current issue.  So if it is not too much to
ask.  Can you give me a run down of how I can accomplish that.
--
Work is sometimes hard....but someone has to do it.
:
I would like to apply a simple filter to a subform from the Mainform.
Main From = SearchPage
Sub Form = Cat Codes subform
Filter Box = Text49
Filter Field = Definition
Filter Command = Command51_Click
Code that I'm trying to use and is not working. Results is a blanksubform
with no filtered records.
Private Sub Command51_Click()
Me.[Cat Codes Subform].Form.Filter = "Definition ='" & Me.Text49 & "'"
Me.[Cat Codes Subform].Form.FilterOn = True
Me.[Cat Codes Subform].Form.Refresh
End Sub
NOTE: definition is a group of words so I need to be able to search for any
word within the field.
--
Work is sometimes hard....but someone has to do it.
Why not use the main form to generate a new recordsource for the
subform? Simply build your SQL query based upon the selection criteria
and set Me.sfrMySubform.Form.Recordsource=mySQL.
Btw, you don't need to do a refresh once calling FilterOn=TRUE. The
reason I prefer the recordsource approach is that it's easier to see
when you have a bad query that can result in a blank form.
-- James- Hide quoted text -
- Show quoted text -

Try this with the what you have,
Change
       Me.[Cat Codes Subform].Form.Filter = "Definition ='" &
Me.Text49 & "'"
 to
Me.[Cat Codes Subform].Form.Filter = "Definition = '" & Me.Text49 &
"'"

I added a space AFTER the   ='    to make it     = '

Here is another way that even better

on the subform select  Data properties
    in the  "Link Child Fields"    type in "[Definition]"
    in the  "Link Parent Fields   type     text49

This makes is a parent child relationship and you don't have to
refresh or do anything or even press the command button. Just tab off
of the text49 field.

Parent child relationship wizard will NOT work, you have to type the
entries in.

Ron- Hide quoted text -

- Show quoted text -

Try again. What you are seeing is the information for the form but
there is one higher level.

Click the subform, Then click it on the very edge itself, The little
box on the upper left corner should NOT have a black square in it.

Another way to get to it is to click on the main form and look in the
dropdown box in the upper left of the associated properties window,
Drop it down and scroll up and look for the Name of the subform and
select that. You want to be looking at the properties of the subform
control and not the form control within that subform.

Hope this is NOT too confusing.

Ron

 

[Pierre]

Doing it that way I do see Link Child Fileds....... But I do not see a Parent
Fields.....Is Link Master Fields...... the same as Parent. If so I entered
text49 into that and it just gave me a blank display in the subform. When i
type a word into the text box it displayed in the definition section of the
subform almost as a new record. Nothing else was displated.
--
Work is sometimes hard....but someone has to do it.

I'm useing Access 2003 and when I go to the Form Page properties Data TAB all
I see is:
Record Source....
Filter.....
Order By....
Allow Filter....
Allow Edits....
Allow Deletions....
Data Entry.....
Recordset Type......
Record Locks.....
Fetch Defaults......
--
Work is sometimes hard....but someone has to do it.

first of all, thank you for your reply. But I'm not sure I completely
understand what you mean by building a simply SQL. I am really not all that
good with Coding. I was trying to use the one I posted from one I found
searching for an answer to my current issue. So if it is not too much to
ask. Can you give me a run down of how I can accomplish that.

:
I would like to apply a simple filter to a subform from the Mainform.
Main From = SearchPage
Sub Form = Cat Codes subform
Filter Box = Text49
Filter Field = Definition
Filter Command = Command51_Click

Code that I'm trying to use and is not working. Results is a blank subform
with no filtered records.

Private Sub Command51_Click()
Me.[Cat Codes Subform].Form.Filter = "Definition ='" & Me.Text49 & "'"
Me.[Cat Codes Subform].Form.FilterOn = True
Me.[Cat Codes Subform].Form.Refresh
End Sub

NOTE: definition is a group of words so I need to be able to search for any
word within the field.

Why not use the main form to generate a new recordsource for the
subform? Simply build your SQL query based upon the selection criteria
and set Me.sfrMySubform.Form.Recordsource=mySQL.

Btw, you don't need to do a refresh once calling FilterOn=TRUE. The
reason I prefer the recordsource approach is that it's easier to see
when you have a bad query that can result in a blank form.

-- James- Hide quoted text -

- Show quoted text -

Try this with the what you have,
Change
Me.[Cat Codes Subform].Form.Filter = "Definition ='" &
Me.Text49 & "'"
to
Me.[Cat Codes Subform].Form.Filter = "Definition = '" & Me.Text49 &
"'"

I added a space AFTER the =' to make it = '

Here is another way that even better

on the subform select Data properties
in the "Link Child Fields" type in "[Definition]"
in the "Link Parent Fields type text49

This makes is a parent child relationship and you don't have to
refresh or do anything or even press the command button. Just tab off
of the text49 field.

Parent child relationship wizard will NOT work, you have to type the
entries in.

Ron- Hide quoted text -

- Show quoted text -

Try again. What you are seeing is the information for the form but
there is one higher level.

Click the subform, Then click it on the very edge itself, The little
box on the upper left corner should NOT have a black square in it.

Another way to get to it is to click on the main form and look in the
dropdown box in the upper left of the associated properties window,
Drop it down and scroll up and look for the Name of the subform and
select that. You want to be looking at the properties of the subform
control and not the form control within that subform.

Hope this is NOT too confusing.

Ron

 

[Ron2006]

OK, You seem to have done everything correctly. So far.

The fact that you got what looks like a new record implies something
is working. It also implies that the value of the description you
entered is NOT in the data selected by the underlying query.

If you don't want the subform to be able to create new records then
you will need to change the
Allow Additions part of what you saw earlier to NO.

Now, In the Record Source of the subform is there some type of
criteria that would stop the query from actually finding any records?

Next, If you are not going to allow the subform to create new records
then my suggestion is to change the text49 box to a combo box and have
the query behind it query the same table as the subform but have the
description field as the only field.
If there will be multiple records with the same description value then
in the resulting query in the combo box
add the word " Distinct " right after the "Select " word. This will
give you only one occurance for every description value in the table.

Ron