Hide inactive data and show active data only. (1 Viewer)

[victorlindh]

Hi everyone. I am making school database. I am defeated on how to show only active data. For example we have 3 terms in a year. I want to hide data for previous years for example in 2025 term 3 to be shown and hide for previous years and terms. I wish when the form(FORM4) is opened, the user should be promted to select YEAR, TERM, GRADE,STREAM and when the user selects then that active should be shown as you add students. Thanks in advance. Look at the attached database.

 

[Gasman]

Create a function to calculate your start and end dates for the current day.
Then filter the data for the form using those dates.

 

[June7]

If you don't want to show prior year data, why have a subform that shows them?

Maybe should have main form bound to tblTermStreams with subform bound to tblStreamStudents. Select students in subform via combobox. If student not in list, use combobox NotInList event to open student form and add record.

Could have code automatically open form to existing current term/stream record or if doesn't exist create it.

 

[moke123]

Might be easier if you have dates associated with the school year . Does school start on some consistent date? like the first Thursday after labor day? Does each term last x number of weeks? You can then calculate where you are in a school year relative to the current date.

 

[Pat Hartman]

This is a situation where you don't "never" want to show the old data, but you normally just want to see the current data. If you have an Id you can sort school terms on, that would solve the problem. You can have an unbound combo that defaults to the current session when the form opens and then your recordsource query which runs in the form's load event can have a where clause that selects the term from the combo. Then the only other code is in the click event of the combo, you requery the form. That allows you to switch to old terms when you need to.

 

[victorlindh]

This is a situation where you don't "never" want to show the old data, but you normally just want to see the current data. If you have an Id you can sort school terms on, that would solve the problem. You can have an unbound combo that defaults to the current session when the form opens and then your recordsource query which runs in the form's load event can have a where clause that selects the term from the combo. Then the only other code is in the click event of the combo, you requery the form. That allows you to switch to old terms when you need to.

Kindly please assist me with an example. This is the best approach. Thanks in advance.

 

[Pat Hartman]

If I had an example I would have posted it. Please at least try to follow the directions.

 

[raziel3]

One thing I learned the hard way was NEVER make Forms based on your Table. Make a query then use that as the RecordSource for your Forms/Reports

Purchases Table Schema:

Purchases Form RecordSource:

If you notice on the Purchases Form RecordSource query I've included a MonthEnd and DueDate field so when my form loads I can include a filter to show the Current Month or Invoices that are due.

DataEntry is still possible but what the Form displays is totally under your control.

 

[The_Doc_Man]

There is an "old programmer's rule" that applies here. "Access won't tell you anything you didn't tell it first, or at least tell it how to know it."

If you want to not see obsolete data, you must have a method handy for Access to recognize obsolete data. Per some of the suggestions from other forum members, you need either some kind of date field that would provide this answer, or you need some kind of code or ID field to convey obsolescence. If you don't have any way in your data to provide Access with a way to tell, then you can't do what you asked.

I can't advise you on which one to choose because it is your project and you know what you do or don't have. But if you recognize that you DO, in fact, have such a thing, then you can use it as a filter on a form or in a WHERE clause in a query. It is strictly your call.

 

[plog]

I wish when the form(FORM4) is opened, the user should be promted to select YEAR, TERM, GRADE,STREAM and when the user selects then that active should be shown as you add students.

1-->If you want to add students to Year/Term/Grade/Streams you have set up your forms wrong. 2-->Looking at your relationships, I think you've overcomplicated this and have set up your tables incorrectly as well.

1: Main forms/subforms are used for 1-many relationships. The main form represents the 1 side and the subform holds the many. As it is now your main form is based on students and the subform is for the year/term/grade/stream. It sounds like you want 1 year/term/grade/stream and have many students. In that case your main form should be based on year/term/grade/stream and the subform should be streamstudents.

2: You've got a ton of tables that needn't exist. Tables with only 1 real data field (autonumbers are not real data--they are used internally by the database) shouldn't exist. That's especially true when you are just storing numbers in those tables. tblTerms is just storing numbers, although as text (Term 1, Term 2, Term 3). Instead that data should just live in the next table up the hierarchy, tblTermYears. Instead of a foreign key to tblTerms in tblTermYears (which itself is a number), just store the number of the term in tblTermYears and do away with tblTerms. Same applies to tblYears, tblGrades and possibly tblStream.

Now, since both tblYears and tblTerms as well as tblStream and tblGrades shouldn't exist that calls into question the need for tblYearTerms and tblGradeStream. Why can't tblTermStreams be like this:

tblTermStreams
TeamStreamsID, autonumber, primary key, same as now
ts_Year, number, this will hold the numeric value of the year (which now ultimately comes from tblYears)
ts_Term, number, this will hold the numeric value of the term (which now ultimately comes from tblTerms)
ts_Grade, number, this will hold the numeric value of the grade (which now ultimately comes from tblGrades)
ts_STream, number or text, this will hold the value of the stream (which now ultimately comes from tblStream)
ts_Select, Yes/No, don't know what this does but it remains if necessary


That gets you to 3 tables, from there it should be easy to set up a main form based on tblTermStreams, a subform on tblSTreamStudents and assign students to a termstream.

 

[CarlettoFed]

In the attachment you can see the example.
The form that manages the student's data is poorly made.

 

[victorlindh]

In the attachment you can see the example.
The form that manages the student's data is poorly made.

Thanks for this. Working on it now

 

[victorlindh]

In the attachment you can see the example.
The form that manages the student's data is poorly made.

Hi. If i may ask what is Maschere?

 

[CarlettoFed]

He is the Italian correspondent of Forms.

 

[victorlindh]

He is the Italian correspondent of Forms.

I am defeated. Kindly please assist me.

 

[CarlettoFed]

What should be the help?