473,320 Members | 1,916 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.

Filtering/searching through records

Hi, I'm having problem with filtering option. I will try to explain what i need on an example.

I have a form with subform in it.
Let say that main form contains info about single student: Name, Surname, Address, Phone number,..., and subform contains info about courses this exact student took as continuous form: English, grade, academic year, then German, grade, academic year, then next course..
So when i go through records on main form, it will jump to different student and will show all courses they took.

I dont know what is the simplest way to search students based on taken course.
I would like to find out find out what student took German course, and i dont want to see it like datasheet, but again as I described in the beginning - Single student data on form with ALL courses taken in subform (including German of course). Then, by clicking Next record I would like to see next student data & all courses.
This would enable me to quickly search through records of people that took german course and to correct some data about that course.
Sometimes I will search by course name and grade at once.

Is something like that possible without some hard programming?

Thanks
Mar 9 '16 #1
7 1583
twinnyfo
3,653 Expert Mod 2GB
The best way to do this would be through VBA. On the main form, you could have a "search section" where you would select either the course name or grade or both. Once those combo boxes were updated, the VBA would find all students who took those courses (with those grades), and then the VBA would set the record source of the form to those students.

The subform would still list all courses, but the list of students would be limited to only those who took particular courses.

I hope this makes sense. This is not terribly complicated, but I don't know your level of expertise in programming with VBA.

This is very doable.
Mar 10 '16 #2
Thanks for answering.

I was trying to make it work for a few days but with no success. Not sure anymore how to do it since i'm out of options and i will need a guide for that.
Mar 15 '16 #3
PhilOfWalton
1,430 Expert 1GB
Are you using 3 tables - Students, Courses and JoinStudentsCourses?
Mar 15 '16 #4
Uhm, no, more tables..
Main form consists of one table, but subform consists of three tables - grades table, courses table and third table with contact, notes and dates data
Mar 15 '16 #5
PhilOfWalton
1,430 Expert 1GB
Up to you, but with a join table, you can have a mainform for Students and courses on a subform, or the main form for Courses and the students on the subform.
Phil
Mar 15 '16 #6
I'm afraid I musn't change those tables. It's the way it is, and we will add few more tables into subform at some point (not my decision). So, join table unfortunately can't be created.
Mar 16 '16 #7
jforbes
1,107 Expert 1GB
Filtering and Searching are two different functions in Access. Filtering limits the records on a Form to only those that match some specified criteria. Searching (or Finding) navigates through the records on a Form by skipping records that don't match the specified criteria and going straight to a matching record.

I'm going to guess that you want Filtering as that is more common, but think nearly everything that can be done with Filtering will translate to Finding/Searching as they both work with a Where Clause

You'll probably want to use the IN() operator. You can the IN() operator in the Where Clause of a Select Statement, which means it can also be used in the Filtering of a Form.

I have no solid idea of your table structure, so I'm going to make one up for you:
  • tblStudent - with a Primary Key of StudentID contains all the Student Info and is the basis of the MainForm.
  • tblStudentClasses - contains a list of Classes that each Student takes. It has a Foreign Key back to tblStudent on StudentID

So a basic Select statement to get Students that have taken a particular Class would be:
Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM tblStudent
  2. WHERE StudentID IN
  3. (
  4.    SELECT StudentID FROM tblStudentClasses
  5.    WHERE Class='English'
  6. )
The greyed part above is an example of the Filter string that can be used for the MainForm.

Based on that, you could create something that resembles this. It's code for the After Update of a TextBox that when a class is typed in to it, the MainForm is Filtered to Students that have that Class:
Expand|Select|Wrap|Line Numbers
  1. Private Sub txtFilter_AfterUpdate()
  2.  
  3.     Dim sWhere As String
  4.     Dim sClass As String
  5.  
  6.     sClass = Nz(Me.txtFilter.Value, "")
  7.  
  8.     If Len(sClass) > 0 Then
  9.         sWhere = "StudentID IN (SELECT StudentID FROM tblStudentClasses WHERE Class='" & sClass & "')"
  10.         Me.Filter = sWhere
  11.         Me.FilterOn = True
  12.     Else
  13.         Me.FilterOn = False
  14.     End If
  15.  
  16. End Sub
Hopefully this will get you going.
Mar 16 '16 #8

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: Martin Williams | last post by:
How can you get the child records of a master record and then further filter the returned array down to more specific records? For instance, I search the array containing the child records for...
1
by: zidrax03 | last post by:
i have trouble in filtering... im creating a enrollment system. which filtered by school year.. how can i filtered the data. that only the active year will be displayed on the form... ...
3
by: dlowry | last post by:
I'm currently using access 97. I'm relatively new to all this, so have attempted to create a company Asset Register when i've come across a problem im unsure of. I'm trying to create some sort of...
11
by: TheDataGuy | last post by:
Using MS ACCESS 2002 I developed a FORM, and within that FORM, I created and added a SEARCH BUTTON, and then I created a SUB-FORM, so that when an the END USER clicks on the FORM it will POP-UP...
3
by: paquer | last post by:
On my Main form I have a Command Button that opens a Subform in order to create a new Subform record. At this point I want the subform to show only the new record being created. Not all the...
0
by: cannonpm | last post by:
Greetings and salutations. I have developed an A2K3 MDB and have a search form modeled after Allen Browne's search form (http:// allenbrowne.com/ser-62.html). It works well except for records which...
4
by: novoselent | last post by:
This seems like it should be an easy thing, but I think I'm missing something simple here...or I'm just going about it all wrong... Using Access 2003 I have a form that lists vehicle service...
2
by: Toothpick | last post by:
For some reason the searching isn't comparing strings right, according to my debugging they should be, something else must be wrong? The actual search method when user clicks search //SEARCH...
5
by: BabliS1 | last post by:
I am using Vb.net2005. I have a datagridview which displays some data. I want to filter the records based on some criteria given by the user at runtime. How can I do this. Please Help. Thanks in...
12
by: Sophia Olivas | last post by:
I used to be able to do this in 2003... but it has been a while. I want a text box at the top of the form that will allow the user to type in an asset# to search the database I know this is...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
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: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
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)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
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...

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.