473,320 Members | 1,991 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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!
Nov 13 '05 #1
14 2323
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:41**********@127.0.0.1...
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!

Nov 13 '05 #2
Nothing wrote:
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!


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.

Nov 13 '05 #3
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!
Nov 13 '05 #4
Nothing wrote:
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.


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
Nov 13 '05 #5
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!
Nov 13 '05 #6
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:41**********@127.0.0.1...
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!

Nov 13 '05 #7
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!
Nov 13 '05 #8
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.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
Nov 13 '05 #9
"Rick Brandt" <ri*********@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]

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
Nov 13 '05 #10
Nothing wrote:
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!


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.
Nov 13 '05 #11
Michael Charney wrote:
"Rick Brandt" <ri*********@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]


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.
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!


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
Nov 13 '05 #12
"Rick Brandt" <ri*********@hotmail.com> wrote in
news:35*************@individual.net:
Michael Charney wrote:
"Rick Brandt" <ri*********@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]


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.
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!


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.


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?
Nov 13 '05 #13
Michael Charney wrote:
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?


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
Nov 13 '05 #14
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" <no*****@me.com> wrote in message
news:Fm*****************@newssvr31.news.prodigy.co m...
"Rick Brandt" <ri*********@hotmail.com> wrote in
news:35*************@individual.net:
Michael Charney wrote:
"Rick Brandt" <ri*********@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]


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.
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!


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.


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?

Nov 13 '05 #15

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Robert Neville | last post by:
I would like to add filter functionality to my database whether through the Main form or the subform. This question may be rudimentary, yet I have not less experience with filtering data outside...
0
by: CSDunn | last post by:
Hello, I have a problem with field filtering between an Access 2000 Project form (the application is called CELDT), and the report that shows the results of the filter. Both the form and the...
2
by: Andante.in.Blue | last post by:
Hi everyone! I was wondering if there is a away to use Access 97's build in filter-by-form function but restrict its effect to just the subform. I have a parent form that shows the major...
3
by: Richard | last post by:
Hi, I have a form based on a table. When I filter the form I want to run a report based on the same table with the same filter as the form. No problem until I want to filter a combo box where...
2
by: Vic | last post by:
Dear All, I am getting the following error message : "You cannot assign a value to this object" ("Me.filter =" is highlighted) I have two comboboxes (ByGenes and BySpecies)with lists in them...
2
by: cefrancke | last post by:
I have a form (no underlying record set) that has two separate sub-forms on it. Each sub-form has data from two different tables. Above each sub-form there is one unbound combo box with a SQL...
2
by: Lenin Torres | last post by:
Hi everybody I have an Union Query that works fine. I used this query as the RecordSource for a Form. That Form is used as a subform in another form. Everything works fine, except for the "Filter...
2
NeoPa
by: NeoPa | last post by:
CHAPTER 1 - TABLE OF CONTENTS (Including attached database) CHAPTER 2 - INTRODUCTION CHAPTER 3 - TABLE LAYOUT CHAPTER 4 - FORM LAYOUT CHAPTER 5 - FORM MODULE CHAPTER 6 - CODE DISCUSSION (FILTER...
3
by: Henrootje | last post by:
I have a form based on the query: SELECT tblCreditGeld.CG_Selected, tblCreditGeld.CG_ClientSelected, tblCreditGeld.CG_ComplexSelected, tblCreditGeld.CG_OKGR_ID, tblCreditGeld.CG_OKGR_IDN,...
5
by: Thelma Roslyn Lubkin | last post by:
I am still having trouble trying to use a popup form to allow user to set filters for the main form. The main form is based on a single table. The popup contains 5 listboxes, so the user can...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.