more VBA and SQL 'problems'...

[streetster]

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

[Mart]

What are the student id level queries meant to do?

[streetster]

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

[Mart]

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.

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

[streetster]

cheers Mart, tho i kinda figured something out:

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

[streetster]

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

[Mart]

You can use the Forms collection like this...

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

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

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

[streetster]

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:

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