Filtering form

[Corinne]

I have a database for storing details of special needs
pupils in school. I need to filter some details out of
the form. (a)Some children have extra help for a while
and then they no longer need help so their details do not
need to be available generally also (b)pupils leave
school mid term to go to another school so I do not need
these either. But I do not want to delete these pupils
(a) these pupils may need help in the future so I don't
want to have to put all the details back in and (b) these
children have a habit of coming back to us.
What I have done so far is to give the children who no
longer need help a title 'class action'. The comb box
that I use to select pupil's records still holds that
pupil's name but the records do not come up on the form
so what I now want to do is to have a box come up telling
me the pupil I chose in the combo box is 'class action'
and offering me a choice of changing that. Is that
possible? If not, just the box with a message, I can then
go into the table and change the level of action, which
would then put the records back into the form.
The second problem is for those who have left. I have a
field that I put the date the pupil has gone off roll and
would somehow like to filter these out as well as the
class action ones, I thought I could do it as a filter in
the same query but I have tried and nothing seems to work.
Could anyone help me on this please.
Thank you in advance.

 

[Jeff Boyce]

Corinne

I'm having a bit of difficulty visualizing your data structure. Before
offering suggestions about queries or forms, it would help to have more
information about what data you are capturing, and what the data structure
looks like.

Jeff Boyce
<Access MVP>

 

[Corinne]

-----Original Message-----
Corinne

I'm having a bit of difficulty visualizing your data structure. Before
offering suggestions about queries or forms, it would help to have more
information about what data you are capturing, and what the data structure
looks like.

Jeff Boyce
<Access MVP>

This is the query that the form data is from there are
quite a lot of fields because when I first started all
this I had no idea about normalisation of tables. I have
got some idea about this now and am slowly changing
things to reflect this. There are also another two tables
that are related to this main table and they are linked
as subforms in my main form. Joined by an primary key
autonumber ID


SELECT [Whole School Table UPN].ID, [Whole School Table
UPN].Surname, [Whole School Table UPN].[First Name],
[Whole School Table UPN].[SEN Level], [Whole School Table
UPN].[Statement Hours], [Whole School Table UPN].[Seen Ed
Psyc], [Whole School Table UPN].SBR, [Whole School Table
UPN].WT, [Whole School Table UPN].CC, [Whole School Table
UPN].Reading, [Whole School Table UPN].[Speaking and
Listening], [Whole School Table UPN].Writing, [Whole
School Table UPN].Spelling, [Whole School Table UPN].
[Maths TA], [Whole School Table UPN].[Science TA], [Whole
School Table UPN].Behaviour, [Whole School Table
UPN].EWO, [Whole School Table UPN].Notes, [Whole School
Table UPN].[Cog & Learn], [Whole School Table UPN].
[Physical/Sensory], [Whole School Table UPN].
[Behav/Emotional/Social], [Whole School Table UPN].
[Commu/Interaction], [Whole School Table UPN].[Off Roll],
[Whole School Table UPN].[ALS module], [Whole School
Table UPN].FLS, [Whole School Table UPN].[Added to
Audit], [Whole School Table UPN].SS, [Whole School Table
UPN].[Unique Pupil Number], [Whole School Table UPN].
[Date of Birth]
FROM [Whole School Table UPN]
WHERE ((([Whole School Table UPN].[SEN Level])="School
Action" Or ([Whole School Table UPN].[SEN Level])="Action
Plus" Or ([Whole School Table UPN].[SEN Level])
="Statement"));

There is a unbound combo box from a query

SELECT [Whole School Table UPN].ID, [Whole School Table
UPN].Surname, [Whole School Table UPN].[First Name]
FROM [Whole School Table UPN]
ORDER BY [Whole School Table UPN].Surname, [Whole School
Table UPN].[First Name];

I select a pupil from the combo box and the relevant data
is shown for that pupil. If that pupil is SEN Level Class
Action then the records are not shown what I would like
to happen in this case is for a message to pop up to say
this pupil is on Class Action. Then if possible given the
opportunity to change the SEN Level, although this might
not be possible I don't know.

The second problem is for those that have gone off roll.
I don't want to lose the details in case these pupils do
return to our school in future but again I don't want
them to show up in the form or reports etc. But if the
names are still in the combo box a message might be good.

Does that make any sense at all?

Thanks for any help you might be able to shed on this for
me.

 

[Jeff Boyce]

Corinne

You may be going at it backwards. Until your data structure is settled
(?normalized), trying to run a query against it will be aiming at a moving
target.

You can use the combo box's AfterUpdate event to test for your condition,
and pop-up a message, and prevent display, and/or ...

If you keep the names but add a field to show "off rolls", you could include
that in the data displayed in your combo box, and again, use the AfterUpdate
event to prevent display.

Good luck

Jeff Boyce
<Access MVP>

 

[Corinne]

-----Original Message-----
Corinne

You may be going at it backwards. Until your data structure is settled
(?normalized), trying to run a query against it will be aiming at a moving
target.

You can use the combo box's AfterUpdate event to test for your condition,
and pop-up a message, and prevent display, and/or ...

If you keep the names but add a field to show "off rolls", you could include
that in the data displayed in your combo box, and again, use the AfterUpdate
event to prevent display.

Good luck

Jeff Boyce
<Access MVP>

.

Thanks for letting me know it is possible I will now
research how to set about getting a message to pop up
when necessary. At the moment I have a requery in the
AfterUpdate event so I will need to have two events in
the one section if that is possible.
But I will play around and see what I can do
Thanks for your help