Issue with independent form and subform.
Solved
samgione
Posted messages
36
Status
Member
-
castours -
castours -
Hello,
I greet you all, I have a big problem on Access.
I created a database for managing a vocational school. The issue is that I have a course subform that is totally independent and should display information from the course table according to the student number displayed on the main form (student form), which is also independent.
I have tried to do it in various ways, but nothing works; the last way that worked shows only one course for the current student, while there are multiple courses for this student.
Please help me solve this problem.
For this student, there are two courses, 1 and 2, but only one course is displayed.
The VBA codes used are as follows:
Option Compare Database
Option Explicit
Dim s As String ' variable declared to receive the value of the subform query
Dim rsEtud As DAO.Recordset ' for the main form
Dim rs As DAO.Recordset ' subform
Dim rsDb As DAO.Database
Sub AfficherDonnees()
' filling in the fields of the main form
Me.NoEtu = rsEtud("NoEtu")
Me.Nom = rsEtud("Nom")
Me.Prenom = rsEtud("Prenom")
Me.Sexe = rsEtud("Sexe")
Me.Option = rsEtud("Option")
Me.NoGrp = rsEtud("NoGrp")
s = "SELECT * FROM Cours WHERE NoEtud=" & Me.NoEtu & ";"
Set rs = rsDb.OpenRecordset(s, dbOpenDynaset, dbSeeChanges)
If rs.EOF = False Then
'filling in the fields of the subform
[sfCours].Form!NoCours = rs("NoCours")
[sfCours].Form!NomC = rs("NomC")
[sfCours].Form!DateC = rs("DateC")
[sfCours].Form!NoEtud = rs("NoEtud")
Else
[sfCours].Form!NoCours = ""
[sfCours].Form!NomC = ""
[sfCours].Form!DateC = ""
[sfCours].Form!NoEtud = ""
End If
End Sub
rivate Sub Form_Load()
Set rsDb = CurrentDb
Set rsEtud = rsDb.OpenRecordset("ReqEtudiants", dbOpenDynaset)
AfficherDonnees
End Sub
Private Sub Form_Close()
rs.Close
rsEtud.Close
rsDb.Close
Set rs = Nothing
Set rsEtud = Nothing
Set rsDb = Nothing
End Sub
I want all the courses (NoEtud) that have the same student number as the current student (Me.NoEtu) to appear in the subform.
I greet you all, I have a big problem on Access.
I created a database for managing a vocational school. The issue is that I have a course subform that is totally independent and should display information from the course table according to the student number displayed on the main form (student form), which is also independent.
I have tried to do it in various ways, but nothing works; the last way that worked shows only one course for the current student, while there are multiple courses for this student.
Please help me solve this problem.
For this student, there are two courses, 1 and 2, but only one course is displayed.
The VBA codes used are as follows:
Option Compare Database
Option Explicit
Dim s As String ' variable declared to receive the value of the subform query
Dim rsEtud As DAO.Recordset ' for the main form
Dim rs As DAO.Recordset ' subform
Dim rsDb As DAO.Database
Sub AfficherDonnees()
' filling in the fields of the main form
Me.NoEtu = rsEtud("NoEtu")
Me.Nom = rsEtud("Nom")
Me.Prenom = rsEtud("Prenom")
Me.Sexe = rsEtud("Sexe")
Me.Option = rsEtud("Option")
Me.NoGrp = rsEtud("NoGrp")
s = "SELECT * FROM Cours WHERE NoEtud=" & Me.NoEtu & ";"
Set rs = rsDb.OpenRecordset(s, dbOpenDynaset, dbSeeChanges)
If rs.EOF = False Then
'filling in the fields of the subform
[sfCours].Form!NoCours = rs("NoCours")
[sfCours].Form!NomC = rs("NomC")
[sfCours].Form!DateC = rs("DateC")
[sfCours].Form!NoEtud = rs("NoEtud")
Else
[sfCours].Form!NoCours = ""
[sfCours].Form!NomC = ""
[sfCours].Form!DateC = ""
[sfCours].Form!NoEtud = ""
End If
End Sub
rivate Sub Form_Load()
Set rsDb = CurrentDb
Set rsEtud = rsDb.OpenRecordset("ReqEtudiants", dbOpenDynaset)
AfficherDonnees
End Sub
Private Sub Form_Close()
rs.Close
rsEtud.Close
rsDb.Close
Set rs = Nothing
Set rsEtud = Nothing
Set rsDb = Nothing
End Sub
I want all the courses (NoEtud) that have the same student number as the current student (Me.NoEtu) to appear in the subform.
8 answers
-
Hello samgione
Do you already have a database?
If so, could you give me the composition of the tables?
Otherwise, I can help you design it and write the SQL queries. -
Hello samgione
I can't access your database.
Otherwise, I'll explain how I'll create the database
students
id_student
last_name
first_name
birth_date
address
...
subjects
id_course
name
teachers
id_teacher
name
phone
rooms
id_room
name
classes
id_class
name (e.g. seconde_1)
id_main_teacher
courses
id_course
id_subject
id_teacher
id_class
date
time
As for the form, if you want to have the list of courses followed by a student (or pupil as you prefer), I will create a form in Ajax.
At the beginning of the page, the list of students will load, and you just have to select the student you want.
Depending on your choice, you will get the list of courses related to them
Try it like that
@+ -
-
Hello everyone, especially Delphine and Castours.
Yes Castours, I would really like to take a look at your database; it could help me.
And yes Delphine, I already have a database on which the form above is built, here is the link where you can download the database: https://www.developpez.net/forums/d1478692/logiciels/microsoft-office/access/probleme-d-affichage-donnees-sous-formulaire-access-access-unbound-sub-form/
And if you could help me design the database and create the SQL queries, that would be very nice.
Thank you in advance to all of you. -
Please help me in one way or another, I am really stuck.
-
Good evening
I'm attaching the database in this link. This database does not come from me.
Let me know what you think
http://cjoint.com/?DKbtXGlAtEJ -
Hello
I modified your database by creating a new form and sub-form as well as new tables in this link.
Please respond to the 2 messages I sent you
http://cjoint.com/?DKdjTdtuBGZ -
Hello my brothers, hello castours,
I am almost satisfied with the help you have provided me, castours, I am truly inspired by the two examples you offered me, and I understand the logic of the F_Cours form and the subform S/F_Etudiants "a course can have multiple students," thank you for this brilliant idea.
But, can't the S/F_Etudiants and F_Cours be independent forms (unbound form)? i.e., can't we do the same thing with unrelated or independent forms by using the DAO?
Thank you greatly for all the help you have provided.