more VBA and SQL 'problems'...

ok, i'm not sure what the best way to do this is, but what i want to do is to create a list of student_ids from the table tbl_student, and then whack them into an array, so that i can go down the list, and run queries based up the student_id (until the whole list of students has been processed)... i had a look for returning sql query results into an array, but i didnt turn up much useful, and i wasnt sure if that is the best way to go about it... i dont think making a table is useful, and tbh would rather keep put the students_id into an array rather than passing it into a table only to extract it again...

anyhow, do i want to be exporting the results of a query into an array, and if so how do i go about doing it, i read the other day that i cant do a straight "SELECT tbl_student.Student_ID
FROM tbl_student;" statement with VBA (only run insert/update etc statements), but i've managed to find and 'add' the Microsoft DAO 3.6 (maybe 3.5?) control, not sure if that helps tho..

any help for the VB newbie welcome :)

cheers

mark

What are the student id level queries meant to do?

erm, i want to take the student_ID, the day, and the period to put into another query (which i have made and tested - and works) which says what lesson they have on that particular day/time and whether they need a support teacher in the lesson (as i'm making a system to match up pupils with support teachers in a school, and its all a lot harder than i thought it would be heh).

i can provide more detail if you want, but its early and i've got to prepare my self for coping with a 7hours shift at tesco heh

cheers

Mark

edit..

slowly learning, managed some SQL which takes all the student IDs and puts them into a table which it makes, which is nice.. now i just need to make some code to loop through them, shouldnt be -too- hard :)

edit2..

done all that stuff that i wanted, now need to do some more coding, ah well i'm learning, slowly :)

Sorry for the delay, been a little busy. Heres a code snippet that shows you how to use ADO to run a query and then iterate through the result set using MoveNext. You should be able to cobble something together based on this.

Code:

---

Private Sub Command0_Click()

Dim conn As ADODB.Connection
Dim rst As New ADODB.Recordset
Set conn = CurrentProject.Connection
rst.ActiveConnection = conn
Dim szSQL As String

szSQL = "SELECT * FROM Users"

rst.Open szSQL, conn, adOpenKeyset, adLockOptimistic

If rst.EOF = True Then
    MsgBox ("No records were returned")
Else

    rst.MoveFirst

    While (rst.EOF = False)
        MsgBox (rst![FirstName])
        rst.MoveNext
    Wend
   
End If

rst.Close

End Sub

---

cheers Mart, tho i kinda figured something out:

Code:

---

Sub ReadStudents()

' Declare Variables
    Dim strSQL As String
    Dim rs As DAO.Recordset
    Dim Count As Integer
    Dim studentID As String
   
' Ignores error if tbl_studenids exists
    On Error Resume Next

' Deletes the temporary studentID table
    DoCmd.RunSQL "DROP TABLE tbl_studentids"

' Turns error checking back on
    On Error GoTo 0

' Create a tbl_studentids and fill with the student IDs from tbl_student

strSQL = "SELECT tbl_student.Student_ID " & _
        "INTO tbl_studentids " & _
        "FROM tbl_student;"
DoCmd.RunSQL strSQL

Set rs = CurrentDb.OpenRecordset("tbl_studentids")

With rs
  Do While Not .EOF
    gstrStudentID = rs!Student_ID
        test
    .MoveNext
  Loop

End With
End Sub

---

tho using some DAO thing, rather than the ADODB, like i said, i honestly dont know what i'm doing :)

mark

ok. i figured out some stuff which renders all the work i did over christmas pointless and obsolete :) replaced all those lines of code with a couple of procedures which are about 50 lines of code in all...

anyhow, i've run into a problem, which i'm sure it really easy to solve, but google isnt helping, nor is my lack of vb knowledge..

basically, when the user clicks the log off button, i want it to cycle through all open forms and close them, the 'docmd.close , nameofform' didnt really acheive anything, so i was wondering if there is either a way of just closing all forms using a docmd.close type thing (and setting the 'on error goto next' thing so that it'll ignore the fact that forms might already be closed) or a means of cycling through all the forms, and if they are open, close them.

cheers :)

mark

You can use the Forms collection like this...

Code:

---

While Forms.Count > 0
        DoCmd.Close acForm, Forms(0).Name
Wend

---

To close all of the forms apart from the current one....

Code:

---

Dim szThisForm as String
szThisForm = "LogoutForm"

While Forms.Count > 1
    If Forms(0).Name = szThisForm Then
        DoCmd.Close acForm, Forms(1).Name
    Else
        DoCmd.Close acForm, Forms(0).Name
    End If
Wend

---

that really does look like a piece of cake :) cheers once again Mart, hmm might have to add you as a source of information in the documentation for this project :)

here is the code i produced which matches up the people, which i thought was quite fantabulous at the time:

Code:

---

Option Compare Database
Public gstrDay As String
Public gstrPeriod As String
Public gstrStudentID As String
Public rsStudentTimetable As DAO.Recordset
Public rsSupportTimetable As DAO.Recordset
Public rsSupport As DAO.Recordset

Sub StudentTimetable()

' Set Up Recordset (loads tbl_timetable_student into rsStudentTimetable)
Set rsStudentTimetable = CurrentDb.OpenRecordset("tbl_timetable_student")

'------------------------------------------------------------------
' If the student needs support in a lesson the code will assign
' the global variables Day/Period/StudentID to the values from the
' student's timetable and then run the SupportTimetable Module.
'
' If the student does not need support, the pointer moves onto the
' next record.
'------------------------------------------------------------------

With rsStudentTimetable
   
    Do While Not .EOF
        If rsStudentTimetable!Needs_Support = True Then
            gstrStudentID = rsStudentTimetable!Student_ID
            gstrDay = rsStudentTimetable!Day
            gstrPeriod = rsStudentTimetable!Period
            gstrTeacher = rsStudentTimetable!Form
            SupportTimetable
        End If
        .MoveNext
    Loop

End With
           
End Sub

Sub SupportTimetable()

' Private variable to declare whether a match has been made
Dim Supported As Boolean

' Set Up Recordsets
Set rsSupportTimetable = CurrentDb.OpenRecordset("tbl_timetable_support")
Set rsSupport = CurrentDb.OpenRecordset("tbl_support")

Supported = False

With rsSupportTimetable
 
    Do While ((Not .EOF) And (Supported = False))
      If rsSupportTimetable!Day = gstrDay _
        And rsSupportTimetable!Period = gstrPeriod _
        And IsNull(rsStudentTimetable!Support_ID) _
        And rsSupportTimetable!Supporting = False _
        And rsSupport!WeakSubject1 <> rsStudentTimetable!Subject Then
           
    ' If there is a match then add the Support_ID to the relevant field in tbl_timetable_student
        With rsStudentTimetable
            .Edit
            rsStudentTimetable!Support_ID = rsSupportTimetable!Support_ID
            .Update
        End With
    ' As there is a match, set Supported to True
        Supported = True
                       
    End If
         
    ' If there is a match update the tbl_timetable_support to show this.
   
    If Supported = True Then
        rsSupportTimetable.Edit
        rsSupportTimetable!Supporting = True
        rsSupportTimetable.Update
        rsSupportTimetable.MoveFirst
    End If
   
    ' If there is no match, move onto the next record from support timetable
    If Supported = False Then .MoveNext
   
    Loop
   
End With

End Sub

Sub ClearAndMatch()

Set rsSupportTimetable = CurrentDb.OpenRecordset("tbl_timetable_support")
Set rsSupport = CurrentDb.OpenRecordset("tbl_support")
Set rsStudentTimetable = CurrentDb.OpenRecordset("tbl_timetable_student")

' Clear all fields relating to the matching algorithm :
' tbl_timetable_student!Support_ID to Null
' tbl_timetable_support!Supporting to False

With rsStudentTimetable
    Do While Not .EOF
      .Edit
      rsStudentTimetable!Support_ID = Null
      .Update
      .MoveNext
   
    Loop
   
End With

With rsSupportTimetable
    Do While Not .EOF
      .Edit
      rsSupportTimetable!Supporting = False
      .Update
      .MoveNext
   
    Loop
End With

' Once fields have been cleared, load the StudentTimetable module

StudentTimetable

End Sub

---

and it works :) just need to do some other funky stuff, like some reports (which i just remembered i had to do about 10mins ago) which is annoying, because the information about each lesson is created dynamically from a query, which wont work for the report as it will want the info from 30 queries and i have no where to store the info, tho i'll sort that out another day :)

thanks again

Mark