Results 1 to 5 of 5

Thread: Looping problem

  1. #1
    Join Date
    Jul 2004
    Posts
    3

    Unanswered: Looping problem

    Hi everyone.. I desperately need some help... I have a database with the following tables. SUDENT TABLE, COURSE TABLE, SUBJECT TABLE AND RESULT TABLE. My problem is how doI insert the SUBJECT_ID INTO RESULT TABLE SUBJECT_ID in sequential order


    RESULT TABLE

    STUDENTID(NRIC) CLASS_ID SUBJECT ID RESULT ID MARKS
    ------------ ----------- ----------- ----------- -------
    S1 | 106A | A | 1 | 50
    S1 | 106A | B | 2 | 60
    S1 | 106A | C | 3 | 70
    S1 | 106A | D | 4 | 50

    S2 | 106A | A | 5 | 40
    S2 | 106A | B | 6 | 50
    S2 | 106A | C | 7 | 70
    S2 | 106A | D | 8 | 80


    Below is my coding
    strSQL = "SELECT * FROM SUBJECT WHERE train_Class = '" & Text1 & "'"
    strSQL2 = "SELECT * FROM RESULT"
    strSQL3 = "SELECT * FROM [Trainee DataBase] WHERE COURSEID= '" & Text1 & "'"

    Set db = CurrentDb()
    Set rst = db.OpenRecordset(strSQL)
    Set rst2 = db.OpenRecordset(strSQL2)
    Set rst3 = db.OpenRecordset(strSQL3)

    Do While Not rst3.EOF

    For I = 1 To rst.Fields.Count
    rst2.AddNew
    rst2.Fields("NRIC").Value = rst3.Fields("NRIC NO").Value
    rst2.Fields("CLASS_ID").Value = Text1.Value
    rst2.Update

    Do While Not rst.EOF
    For J = 1 To rst.Fields.Count
    MsgBox rst.Fields(1).Value
    rst.MoveNext
    Next J
    Loop

    Next I

    rst3.MoveNext
    Loop

    MsgBox "Updated successful!!"
    rst2.Close



    Please help me
    Last edited by ericyeo; 07-25-04 at 11:31.

  2. #2
    Join Date
    Nov 2002
    Location
    Norway
    Posts
    239
    Attach your tables in a database and I will try to give you an example.

    sincerely
    Vestigo

  3. #3
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    In your SQL statement, include "ORDER BY YourField"

    Code:
    strSQL = "SELECT * FROM SUBJECT WHERE train_Class = '" & Text1 & "' ORDER BY SUBJECT_ID"
    You basic SQL format is:
    SELECT something FROM somewhere WHERE conditions ORDER BY criteria

    A bit of advice: never use spaces in the names of objects. Although Access let's you get away with it, it complicates your code and will present problems when you build something that may be upsized to MSSQL or others later in life.


    have fun!
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  4. #4
    Join Date
    Jul 2004
    Posts
    3

    Re:

    Can someone Pls help me.. I have attached the file. I'm having this error called "no current record" 3021.

    I need to addnew records in result table by one click on the button.

    Let say if the class have 3 student. This class student have three subjects.
    This three subject will be key in only onces and the rest will be inserted into result table. Please advice. Thanks and appreciated
    Last edited by ericyeo; 07-26-04 at 10:19.

  5. #5
    Join Date
    Jul 2004
    Posts
    3
    Thanks all. I have solved the problem myself. lol

Posting Permissions

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