Sub Form Filter?

I have a form that has a Tab Control on it. On one of the tab control
pages, there are only 3 pages, is a combo box based on a field called
departments in a table.

When the user selects one of the departments from the combo box I want a
sub-form to open on the tab control page based on the department
selected.

The sub-form is based on a query that pulls all employee data, so if a
user selects admissions, I want the subform to open and only display
records for the admissions depart.

I can get the sub-form to open and display all the records, but I can
not figure out how to make it display only the records I want.

How would I do this?

Thanks in advance for any help!

Michael Charney

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

re: Sub Form Filter?

The department field on the sub form needs to reference the tab control,
then requery the sub-form whenever you click on a tab.

"Nothing" <me@you.com> wrote in message news:41ebf890$1_2@127.0.0.1...[color=blue]
> I have a form that has a Tab Control on it. On one of the tab control
> pages, there are only 3 pages, is a combo box based on a field called
> departments in a table.
>
> When the user selects one of the departments from the combo box I want a
> sub-form to open on the tab control page based on the department
> selected.
>
> The sub-form is based on a query that pulls all employee data, so if a
> user selects admissions, I want the subform to open and only display
> records for the admissions depart.
>
> I can get the sub-form to open and display all the records, but I can
> not figure out how to make it display only the records I want.
>
> How would I do this?
>
> Thanks in advance for any help!
>
> Michael Charney
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it![/color]

re: Sub Form Filter?

Nothing wrote:[color=blue]
> I have a form that has a Tab Control on it. On one of the tab control
> pages, there are only 3 pages, is a combo box based on a field called
> departments in a table.
>
> When the user selects one of the departments from the combo box I want a
> sub-form to open on the tab control page based on the department
> selected.
>
> The sub-form is based on a query that pulls all employee data, so if a
> user selects admissions, I want the subform to open and only display
> records for the admissions depart.
>
> I can get the sub-form to open and display all the records, but I can
> not figure out how to make it display only the records I want.
>
> How would I do this?
>
> Thanks in advance for any help!
>
> Michael Charney
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it![/color]

You could put the filter in the query of the subform. In the Criteria
line of Department you could enter something like
Forms!MainFormName!ComboDeptName

Then only records that match the dept combo will be displayed.

re: Sub Form Filter?

I have tried to add the filter in both the criteria of the query and on
the form in the filter property. (Not at the same time) but the form
doesnt change with the change in the combo box. It still displays all
the reords.

Am I doing something wrong? (Stupid question!) Is there some sample code
I can exaime somewhere?

Michael Charney

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

re: Sub Form Filter?

Nothing wrote:[color=blue]
> I have tried to add the filter in both the criteria of the query and on
> the form in the filter property. (Not at the same time) but the form
> doesnt change with the change in the combo box. It still displays all
> the reords.[/color]

In the after update event of the combo box, enter:

me.subFormName.Form.Requery

This will do it for you.

Note also, that if you are going to use standard filter menu items from
Access on the sub form, you may run into some of the difficulty I
mention on a post on Friday.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

re: Sub Form Filter?

I still can not get this dumb thing to work.

I have added the Me.sub-frmEmplyeeData.Form.Requery to teh combo box's
AfterUpdate event and the query re-runs but will not sort on the
department choice in the combo box.

I dont get it. I can manually query for a department name from the query
and it will accept the input and filter fine, but when I try to make the
sub form do the same thing, it just will not work.

Anyone else have any ideas cause I fresh out now. I must be missing
something somewhere but as for what I have no idea.

Michael Charney

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

re: Sub Form Filter?

You need to add it to the Link Child Fields and Link Master Fields in the
subform properties.

"Nothing" <me@you.com> wrote in message news:41ec23e5$1_1@127.0.0.1...[color=blue]
> I have tried to add the filter in both the criteria of the query and on
> the form in the filter property. (Not at the same time) but the form
> doesnt change with the change in the combo box. It still displays all
> the reords.
>
> Am I doing something wrong? (Stupid question!) Is there some sample code
> I can exaime somewhere?
>
> Michael Charney
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it![/color]

re: Sub Form Filter?

It will not allow me to because there is nothing attached to the main
form so there is no linking possible, at least that is what Access is
telling me.

I added the sub form by draging a query to the tab control and dropping.
Is this the best way to do this?

Michael Charney

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

re: Sub Form Filter?

Nothing wrote:[color=blue]
> It will not allow me to because there is nothing attached to the main
> form so there is no linking possible, at least that is what Access is
> telling me.[/color]

It's just telling you that you can't use the wizard for entering the link
properties. You can still just type them in yourself.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

re: Sub Form Filter?

"Rick Brandt" <rickbrandt2@hotmail.com> wrote in news:3556omF4if6qaU1
@individual.net:
[color=blue]
> Nothing wrote:[color=green]
>> It will not allow me to because there is nothing attached to the main
>> form so there is no linking possible, at least that is what Access is
>> telling me.[/color]
>
> It's just telling you that you can't use the wizard for entering the link
> properties. You can still just type them in yourself.
>[/color]

Ok I added the following to the Link Child Fields and the Link master
Fields:

Forms!frmEmployeeData.[Combo14]

Now when I go to the form and select the Tab the query is blank to start. I
select a department and it shows me ALL the records. It still is not seeing
the department name that is selected.

I even changed the combo box to look at a specail query that selects the
department name to make sure I am getting the department name.

I am about ready to just re-write the freakin thing another way. The tab
control in Access flat out sucks!

Sorry a little venting there...

Mike

re: Sub Form Filter?

Nothing wrote:[color=blue]
> I still can not get this dumb thing to work.
>
> I have added the Me.sub-frmEmplyeeData.Form.Requery to teh combo box's
> AfterUpdate event and the query re-runs but will not sort on the
> department choice in the combo box.
>
> I dont get it. I can manually query for a department name from the query
> and it will accept the input and filter fine, but when I try to make the
> sub form do the same thing, it just will not work.
>
> Anyone else have any ideas cause I fresh out now. I must be missing
> something somewhere but as for what I have no idea.
>
> Michael Charney
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it![/color]

In the Combo's AfterUpdate event you could try something like this
Dim strFilter As String
'assumes the dept is named dept and is a string
'value and not a number. You may be using a
'number in a lookup table, not sure what you
'are using. Thus the quotes around the ComboName.
strFilter = "Dept = '" & Me.ComboName & "'"
Forms!MainFormName!SubFormName.Form.Filter = strFilter
Forms!MainFormName!SubFormName.Form.FilterOn = True

As also mentioned regarding the Link Master/Child, single click on the
subform and pull up the property sheet for it. You will see the
properties to link the Master to Child.

re: Sub Form Filter?

Michael Charney wrote:[color=blue]
> "Rick Brandt" <rickbrandt2@hotmail.com> wrote in news:3556omF4if6qaU1
> @individual.net:
>[color=green]
>> Nothing wrote:[color=darkred]
>>> It will not allow me to because there is nothing attached to the
>>> main form so there is no linking possible, at least that is what
>>> Access is telling me.[/color]
>>
>> It's just telling you that you can't use the wizard for entering the
>> link properties. You can still just type them in yourself.
>>[/color]
>
> Ok I added the following to the Link Child Fields and the Link master
> Fields:
>
> Forms!frmEmployeeData.[Combo14][/color]

All you need is "Combo14" (without the quotes) in the MasterLink property
and the name of the corresponding field in the subform. For ChildLink it
has to be a field name and not just a control name.
[color=blue]
> Now when I go to the form and select the Tab the query is blank to
> start. I select a department and it shows me ALL the records. It
> still is not seeing the department name that is selected.
>
> I even changed the combo box to look at a specail query that selects
> the department name to make sure I am getting the department name.
>
> I am about ready to just re-write the freakin thing another way. The
> tab control in Access flat out sucks![/color]

None of this has anything to do with the TabControl. What you are "meesing
with" are properties and behaviors of a subform. If there were no
TabControl involved it would be exactly the same.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

re: Sub Form Filter?

"Rick Brandt" <rickbrandt2@hotmail.com> wrote in
news:35583eF4ihfmsU1@individual.net:
[color=blue]
> Michael Charney wrote:[color=green]
>> "Rick Brandt" <rickbrandt2@hotmail.com> wrote in news:3556omF4if6qaU1
>> @individual.net:
>>[color=darkred]
>>> Nothing wrote:
>>>> It will not allow me to because there is nothing attached to the
>>>> main form so there is no linking possible, at least that is what
>>>> Access is telling me.
>>>
>>> It's just telling you that you can't use the wizard for entering the
>>> link properties. You can still just type them in yourself.
>>>[/color]
>>
>> Ok I added the following to the Link Child Fields and the Link master
>> Fields:
>>
>> Forms!frmEmployeeData.[Combo14][/color]
>
> All you need is "Combo14" (without the quotes) in the MasterLink
> property and the name of the corresponding field in the subform. For
> ChildLink it has to be a field name and not just a control name.
>[color=green]
>> Now when I go to the form and select the Tab the query is blank to
>> start. I select a department and it shows me ALL the records. It
>> still is not seeing the department name that is selected.
>>
>> I even changed the combo box to look at a specail query that selects
>> the department name to make sure I am getting the department name.
>>
>> I am about ready to just re-write the freakin thing another way. The
>> tab control in Access flat out sucks![/color]
>
> None of this has anything to do with the TabControl. What you are
> "meesing with" are properties and behaviors of a subform. If there
> were no TabControl involved it would be exactly the same.
>[/color]

In the Link master Field I put "Combo14" without the quotes.
In the Link Child Fields I put "[dept]" without the quotes. I also tried
jusst dept.

When I start the form and select the Departments tab, the sub form comes
up blank to start with. I then choose a department but it still shows no
records. Is there anything else you can think of?

re: Sub Form Filter?

Michael Charney wrote:[color=blue]
> In the Link master Field I put "Combo14" without the quotes.
> In the Link Child Fields I put "[dept]" without the quotes. I also
> tried jusst dept.
>
> When I start the form and select the Departments tab, the sub form
> comes up blank to start with. I then choose a department but it still
> shows no records. Is there anything else you can think of?[/color]

Are you sure your ComboBox is not a multi-column one where it can show one
column, but actually be retaining the value of a different one?

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

re: Sub Form Filter?

Try placing a hidden field on the form then update it to the department
whenever you change tabs. Reference this field with the subform. Set the
default value to whatever is the default tab.

"Michael Charney" <nothing@me.com> wrote in message
news:FmeHd.8111$O%5.6422@newssvr31.news.prodigy.co m...[color=blue]
> "Rick Brandt" <rickbrandt2@hotmail.com> wrote in
> news:35583eF4ihfmsU1@individual.net:
>[color=green]
> > Michael Charney wrote:[color=darkred]
> >> "Rick Brandt" <rickbrandt2@hotmail.com> wrote in news:3556omF4if6qaU1
> >> @individual.net:
> >>
> >>> Nothing wrote:
> >>>> It will not allow me to because there is nothing attached to the
> >>>> main form so there is no linking possible, at least that is what
> >>>> Access is telling me.
> >>>
> >>> It's just telling you that you can't use the wizard for entering the
> >>> link properties. You can still just type them in yourself.
> >>>
> >>
> >> Ok I added the following to the Link Child Fields and the Link master
> >> Fields:
> >>
> >> Forms!frmEmployeeData.[Combo14][/color]
> >
> > All you need is "Combo14" (without the quotes) in the MasterLink
> > property and the name of the corresponding field in the subform. For
> > ChildLink it has to be a field name and not just a control name.
> >[color=darkred]
> >> Now when I go to the form and select the Tab the query is blank to
> >> start. I select a department and it shows me ALL the records. It
> >> still is not seeing the department name that is selected.
> >>
> >> I even changed the combo box to look at a specail query that selects
> >> the department name to make sure I am getting the department name.
> >>
> >> I am about ready to just re-write the freakin thing another way. The
> >> tab control in Access flat out sucks![/color]
> >
> > None of this has anything to do with the TabControl. What you are
> > "meesing with" are properties and behaviors of a subform. If there
> > were no TabControl involved it would be exactly the same.
> >[/color]
>
> In the Link master Field I put "Combo14" without the quotes.
> In the Link Child Fields I put "[dept]" without the quotes. I also tried
> jusst dept.
>
> When I start the form and select the Departments tab, the sub form comes
> up blank to start with. I then choose a department but it still shows no
> records. Is there anything else you can think of?[/color]