Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Sep 2003
    Location
    London, England
    Posts
    52

    Unanswered: Entering exam results

    I use Windows XP and Access 2002.
    I want to create a form for entering the results of a new examination for students from a particular class. The students must appear as a class list in datasheet view, enabling the results to be entered in any order by typing them (they are just numerical marks) into an empty box. Each time a result is entered a new record must be written in the StudentsExams table.

    The relevant tables and fields may be summarised as follows:
    [Table: PrimaryKey, FieldNames]
    Exams: ExamID, ExamName
    Date: DateID, Date
    StudentsExams: AutoID, Number, ExamID, DateID
    Students: Number, Surname
    StudentsClass: AutoID, Number, ClassID
    Class: ClassID, Code

    A record is uniquely defined in StudentsExams when its ExamID, DateID and Number are all known.
    Please ask for more clarification if needed. Relationships doc attached.

    Please can you advise me how I can do this?

  2. #2
    Join Date
    Mar 2004
    Location
    belgium
    Posts
    290
    create a recordset of the class for the form by sql, showing all necessary fields.
    add some unboud text boxes for the exam results etc
    code an On Exit-event at the appropriated field
    and perform the rsStudentExams.append
    replace the values to the appropriate fields
    and rsStudentExams. update

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    thanks for the mail - here is an outline of a solution that does not need recordsets. the promised DOC was not in your post so this outline cannot be 100% - hopefully it's enough to get you started.

    make a form.

    to select a particular class, add a combo box thisClass and set it's properties as:
    .rowsource: SELECT ClassID, Code FROM Class ORDER BY Code;
    .boundcolumn: 1 (in design view, 0 if you do this in code)
    .columncount: 2
    .columnwidths: 0cm; 4cm (or 2cm; 4cm if you want to see the ID)
    (the class doesn't seem to be invloved with the process at all, but you said you wanted to select a class, so that's how you do it! i am a little worried about your table structure - the exam results don't seem to belong to a student!!!!!)


    add a combo thisExam
    .rowSource: SELECT ExamID, ExamName FROM Exams ORDER BY ExamName;
    .boundcolumn: 1 (in design view, 0 if you do this in code)
    .columncount: 2
    .columnwidths: 0cm; 4cm (or 2cm; 4cm if you want to see the ID)

    add a combo thisDate
    .rowsource: SELECT DateID, Date FROM Date ORDER BY Date;!!!!STOP!!!!
    Date is a reserved word and you are using it for a tablename AND for a fieldname in the table - CHANGE BOTH IMMEDIATELY and adjust the .rowsource to suit your new names. never use reserved words as names and i also strongly discourage you from using a fieldname that is the same as the table name... only confusion can result.

    .boundcolumn: 1 (in design view, 0 if you do this in code)
    .columncount: 2
    .columnwidths: 0cm; 4cm (or 2cm; 4cm if you want to see the ID)

    add textbox: thisNumber

    add a button butSave with Caption Save

    in the event butSave_Click() use:

    dim strSQL as string
    strSQL = "INSERT INTO StudentsExams (Number, ExamID, DateID) VALUES ("
    strSQL = strSQL & thisNumber & ", " 'assuming it IS a number
    strSQL = strSQL & thisExam & ", " 'assuming ExamID IS a number
    strSQL = strSQL & thisDate & "); " 'assuming DateID IS a number
    docmd.setwarnings false
    docmd.runsql strSQL
    docmd.setwarnings true

    you will probably also want to check that something is selected in the combos before you start building the strSQL
    if isnull(thisExam) then
    msgbox "no exam"
    exit sub
    endif
    etc etc

    probably you will want to clear all the combos after the record is saved to prevent an accidental duplicate
    thiexam = null


    i am seriously worried that these exam results don't belong to a student!!!


    izy
    currently using SS 2008R2

  4. #4
    Join Date
    Sep 2003
    Location
    London, England
    Posts
    52

    Question Am I nearly there now?

    I have changed the tablename from Date to Dates. I have changed the fieldname from Date to Dated. The way that each exam result belongs to a student is through the field “number” which is the unique number that each student has and is the primary key from the Students table.

    I have made a form called fdlgMarks and as suggested this form has three combo boxes from which selection is made of thisClass, thisExam and thisDate. By making this selection you will have chosen the correct class, exam and date for which the marks are to be entered. I think that you are now in a position that, by clicking an OK button say, you can open another form in datasheet view containing the list of students for whom you wish to enter marks and there must also be a textbox thisNumber into which this can be done for each student.

    Presumably I must write a query that selects only those students that are in the class defined by the choice of thisClass in the form fdlgMarks and use this query to define what is in the form that opens to allow mark entry.

    Am I on the right track now?

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    i was confused by "Number" which occurs in several tables. i don't like it as a field name (but i don't think it is a reserved word so it should be technically ok).
    StudentsExams: AutoID, Number, ExamID, DateID
    ...i assumed Number was the exam result you were trying to type in.
    ...but now you clarify it is a foreign key on Number in
    Students: Number, Surname

    so, here your layout (with some fields renamed!)?

    Exams: ExamID (PK), ExamName
    Dates: DateID (PK), Dated
    StudentsExams: StuExaID (PK), StudentID (FK), ExamID (FK), DateID (FK)
    Students: StudentID (PK), Surname
    StudentsClass: StuClaID, StudentID (FK), ClassID(FK)
    Class: ClassID (FK), Code

    ...if yes, it's a fine layout but you need another field:
    StudentsExams: StuExaID (PK), StudentID (FK), ExamID (FK), DateID (FK), ExamResult

    i'm going a slightly different route from your plan:
    get a class -> get a student -> enter results for that student

    so a form1
    add a combo box thisClass and set it's properties as:
    .rowsource: SELECT ClassID, Code FROM Class ORDER BY Code;
    .boundcolumn: 1
    .columncount: 2
    .columnwidths: 0cm; 4cm


    add a listbox listStudents that has no recordsource
    .recordsource:
    .columncount: 2
    .boundcolumn: 1
    .columnwidths: 0cm; 6cm

    in the event thisClass_AfterUpdate()
    dim strSQL as string
    if isnull(thisClass) then exit sub
    strSQL = "SELECT Students.StudentID, Students.Surname "
    strSQL = strSQL & "FROM Students INNER JOIN StudentsClass ON "
    strSQL = strSQL & "Students.StudentID = StudentsClass.StudentID "
    strSQL = strSQL & "WHERE (((StudentsClass.ClassID)=" & thisClass & "));"
    listStudents.rowsource = strSQL
    listStudents.requery

    ...when you select something in thisClass you get the students in the list
    listStudents.value is the StudentID of the student selected in the list.

    in listStudents_DoubleClick()
    docmd.openform "form2"

    ...so you need a form2 with

    .popup: yes
    .modal: yes

    add a closeMe button with caption Close
    in the closeMe_Click() event
    docmd.close acform, "form2"

    add a combo thisExam
    .rowSource: SELECT ExamID, ExamName FROM Exams ORDER BY ExamName;
    .boundcolumn: 1
    .columncount: 2
    .columnwidths: 0cm; 4cm

    add a combo thisDate
    .rowsource: SELECT DateID, Dated FROM Dates ORDER BY Dated;
    (ORDER BY Dated DESC; if you want newest at the top)
    .boundcolumn: 1
    .columncount: 2
    .columnwidths: 0cm; 4cm

    add textbox: thisResult

    add a button butSave with Caption Save

    in the event butSave_Click() use:
    dim strSQL as string
    strSQL = "INSERT INTO StudentsExams (StudentID, ExamID, DateID, ExamResult) VALUES ("
    strSQL = strSQL & forms!form1.listStudents & ", "
    strSQL = strSQL & thisExam & ", "
    strSQL = strSQL & thisDate & " "
    strSQL = strSQL & thisResult & ";" 'if it is a numeric result
    docmd.setwarnings false
    docmd.runsql strSQL
    docmd.setwarnings true

    you will probably also want to check that something is selected in all the combos before you start building this strSQL
    if isnull(thisExam) then
    msgbox "no exam"
    exit sub
    endif
    etc etc

    probably you will want to clear all the combos after the record is saved to prevent an accidental duplicate
    thisExam = null
    etc etc

    you could show the class & name of the student on form2:
    add a couple of (locked, disabled) textboxes
    whichStudent, whichClass
    and in the _Load() event of form2
    whichStudent = forms!form1.listStudent.column(1)
    whichClass = forms!form1.thisClass.column(1)

    you could show the results already entered for the current student in a list with .rowsource...
    ...no, this is left as an exercise for you. build yourself a query on StudentsExams using forms!form1.listStudent as criteria and display it in a list on form2
    add a yourNewList.requery
    at the very end of butSave_Click()
    to show the new entry.

    i hope i didn't make too many typos (or wrong assumptions!)
    if you get stuck, post what you have and someone here will fix it for you.

    izy
    Last edited by izyrider; 10-31-04 at 05:21.
    currently using SS 2008R2

  6. #6
    Join Date
    Sep 2003
    Location
    London, England
    Posts
    52

    Starting out

    Hello Izy,

    I have started as you suggest (although I have kept the original field names where possible because of the danger of disabling prior queries, etc).
    My first problem is getting the list of students by using my own interpretation of your code (note that I have added a third row to the listStudents for [Known Name]):


    Private Sub thisClass_AfterUpdate()
    ' Find the student details for the selected class
    Dim strSQL As String
    If IsNull(Thisclass) Then Exit Sub
    strSQL = "SELECT Students.Number, Students.Surname, Students.[Known Name]"
    strSQL = strSQL & "FROM Students INNER JOIN StudentsClass ON "
    strSQL = strSQL & "Students.Number = StudentsClass.Number "
    strSQL = strSQL & "WHERE (((StudentsClass.ClassID)=" & Thisclass & "));"
    listStudents.RowSource = strSQL
    listStudents.Requery

    End Sub


    I think the problem could be because the ClassID is just a number but the EARS CODE held in thisClass is text (eg MTGC1B1). However I don’t get the list of students in the class. Please tell me where I have gone wrong so far.

    Thanks again

  7. #7
    Join Date
    Sep 2003
    Location
    London, England
    Posts
    52
    My next unsuccessful attempt to select the students is below (note that I have called form1 fdlgMarks and StudentID is still Number). I am not yet sure why it does not work:

    Private Sub thisClass_AfterUpdate()
    ' Find the student details for the selected class
    Dim strSQL As String
    If IsNull(Thisclass) Then Exit Sub
    strSQL = "SELECT Students.Number, Students.Surname, Students.[Known Name]"
    strSQL = strSQL & "FROM Class INNER JOIN (Students INNER JOIN StudentsClass ON"
    strSQL = strSQL & "Students.Number = StudentsClass.Number) ON"
    strSQL = strSQL & "Class.ClassID = StudentsClass.ClassID"
    strSQL = strSQL & "WHERE (((StudentsClass.ClassID)=[Forms]![fdlgMarks].[thisClass]));"
    listStudents.RowSource = strSQL
    listStudents.Requery

    End Sub

  8. #8
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    your 12:49

    add a combo box thisClass and set it's properties as:
    .rowsource: SELECT ClassID, Code FROM Class ORDER BY Code;
    .boundcolumn: 1
    .columncount: 2
    .columnwidths: 0cm; 4cm

    thisClass = thisClass.Value = thisClass.Column(n)
    where "n" is the boundcolumn (counting 1,2,3 in design, 0,1,2 in code - the sequence comes from the sequence in SELECT, so...
    thisClass = ClassID
    ...which you tell me is a number.

    but this is wrong:
    strSQL = strSQL & "WHERE (((StudentsClass.ClassID)=[Forms]![fdlgMarks].[thisClass]));"
    it needs to read
    strSQL = strSQL & "WHERE (((StudentsClass.ClassID)=" & [Forms]![fdlgMarks].[thisClass] & "));"

    you are trying to concatenate
    the literal string "WHERE (((StudentsClass.ClassID)="
    plus the form value [Forms]![fdlgMarks].[thisClass]
    plus the literal string "));"

    izy
    currently using SS 2008R2

  9. #9
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    your 16:00

    again, this is wrong for the same reason:
    strSQL = strSQL & "WHERE (((StudentsClass.ClassID)=[Forms]![fdlgMarks].[thisClass]));"

    same argument as before leads to:
    strSQL = strSQL & "WHERE (((StudentsClass.ClassID)=" & [Forms]![fdlgMarks].[thisClass] & "));"

    i guess that concatenating-in form values is one of the most frequently asked questions on this site - you are not alone!

    it goes like this:
    "blah blah WHERE myDate = #" & someDate & "# blah blah
    "blah blah WHERE myText = '" & someText & "' blah blah
    "blah blah WHERE myNumber = " & someNum & " blah blah

    izy
    currently using SS 2008R2

  10. #10
    Join Date
    Sep 2003
    Location
    London, England
    Posts
    52

    Another attempt

    I tried this query and it worked:

    SELECT Students.Number, Students.Surname, Students.[Known Name]
    FROM Class INNER JOIN (Students INNER JOIN StudentsClass ON
    Students.Number = StudentsClass.Number) ON
    Class.ClassID = StudentsClass.ClassID
    WHERE (((StudentsClass.ClassID)= [Forms]![fdlgMarks].[Thisclass]));

    Then I tried to correct the code and it did not work as:
    Private Sub thisClass_AfterUpdate()
    ' Find the student details for the selected class
    Dim strSQL As String
    If IsNull(Thisclass) Then Exit Sub
    strSQL = "SELECT Students.Number, Students.Surname, Students.[Known Name]"
    strSQL = strSQL & "FROM Class INNER JOIN (Students INNER JOIN StudentsClass ON"
    strSQL = strSQL & "Students.Number = StudentsClass.Number) ON"
    strSQL = strSQL & "Class.ClassID = StudentsClass.ClassID"
    strSQL = strSQL & "WHERE (((StudentsClass.ClassID)= " & [Forms]![fdlgMarks].[Thisclass] & "));"
    listStudents.RowSource = strSQL
    listStudents.Requery

    End Sub

    So I am still no wiser. Sorry.

  11. #11
    Join Date
    Sep 2003
    Location
    London, England
    Posts
    52

    Zipped Dbase attached

    Hi Izy,

    I hope you will find attached a zipped version of my database.

    Thank you again

  12. #12
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Since Izy isnt around just now i have a verquick suggestion for you,
    Try putting a Space at the end of each line of your sql
    i.e.
    strSQL = "SELECT Students.Number, Students.Surname, Students.[Known Name]"
    becomes
    strSQL = "SELECT Students.Number, Students.Surname, Students.[Known Name] "

    because the SQL your passing to access at the moment is

    SELECT Students.Number, Students.Surname, Students.[Known Name]FROM Class INNER JOIN (Students INNER JOIN StudentsClass ONStudents.Number = StudentsClass.Number) ON
    etc.

    So for example Access doesn't know what ONStudents.Number is
    Dave

  13. #13
    Join Date
    Sep 2003
    Location
    London, England
    Posts
    52

    spaces

    Thanks for your interest, David. You make a valid point about the concatenation but I have made sure that the code has spaces at the end of each line and it still is not working.

  14. #14
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    see if this is headed in the right direction. its an MDB containing just a single form fdlgMarksB. save it somewhere and open your original .MDB and use
    file ¦ get external data ¦ import
    to get my form into your .MDB

    what was wrong?
    have a look at the list properties in my version - rowsource type
    have a look at the SQL view of your qryIZY and compare with the SQL i'm building in the form ...a couple of tiny differences here & there.

    there is some stuff for you to delete & uncomment in the fdlgMarksB code once you are happy.

    hope this does it for you.

    izy
    Last edited by izyrider; 11-12-04 at 14:00. Reason: ATTACHED FILE WAS CORRUPT: REMOVED
    currently using SS 2008R2

  15. #15
    Join Date
    Sep 2003
    Location
    London, England
    Posts
    52
    Hi Izy,

    Thanks very much for the zipped file. However I believe it may unfortunately be corrupt because when I try to import it I get the following message:
    An error occurred while loading ‘Form-fdlgMarksB’. Do you want to continue loading the project?

    If I ignore the message and go ahead and load it I then get this message:
    The visual basic for applications project in the database is corrupt.

    On inspection I see that amongst other problems in the list box for ListStudents the VBA is merely:
    Private Sub listStudents_Click()

    End Sub

    Which suggests to me that the VBA has not come across.
    Please would you be so kind as to check it is OK and then attach it once more for me.

    Thank you again
    John

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •