[VBA] van PHP naar VBA

[Kage Musha]

Ik heb een stuk pseudo PHPcode die vertaald moet worden in VBA (Access).

Tot zover heb ik dit al vertaald

code:

Private Sub Command26_Click()
Dim cnn As Connection
Dim strSQL As String
Dim no_rows
Dim no_choices, max_courses, p, i As Integer
Dim Recordset As New ADODB.Recordset

    no_choices = 4   'the number of choices each student has
    max_courses = 2  'the number of courses a stundent is allowed (and has to) select p = 0
    Do While i <= no_choices
         p = p + 1
    
    Set cnn = CurrentProject.Connection

    Set Recordset = Command.Execute("SELECT _fd_id, choice" + p + " FROM N_Data_Tbl")
    Set no_rows = Command.Execute("SELECT COUNT(*) FROM N_Data_Tbl")
 

    ReDim all_fd_ids(no_rows) As Integer 'Array of all _fd_id's
    ReDim all_choices(no_rows) As String 'Array of Strings to save all 'p' choices...

    i = 0
    Do While i <= no_rows 'this fills the arrays with the data from the query
        i = i + 1
       all_fd_ids(i) = Recordset(i, 0)
       all_choices(i) = Recordset(i, 1)
    Loop
    Loop
   

End Sub

en dit is de PHP Code

code:

/* DON'T FORGET THAT THIS IS PSEUDO-CODE ITS NOT VB OR VBA !!! MORE LIKELY SOMETHING LIKE JAVA / PHP 
   It just tells you, what you should do to achieve what you want!*/

int no_choices=4;   //the number of choices each student has
int max_courses=2;  //the number of courses a stundent is allowed (and has to) select

for(int p=0;i<no_choices;p++)
{
    mysql_query("SELECT _fd_id, choice" + p + " FROM N_Data_Tbl");

    int     no_rows = mysql_num_rows(); 

    int all_fd_ids[no_rows];    //Array of all _fd_id's
    String  all_choices[no_rows];   //Array of Strings to save all 'p' choices...
    
    for(int i=0;i<no_rows;i++)  //this fills the arrays with the data from the query
    {
        all_fd_ids[i]  = mysql_result(i,0);
        all_choices[i] = mysql_result(i,1); 
    }

    for(int i=0;i<no_rows;i++)
    {
        mysql_query("SELECT MaxStudents FROM Course WHERE CourseName = '" + all_choices[i] + "'");
        int MaxStudents = mysql_result(0,0);
        mysql_query("SELECT * FROM Rel_SelectedCourses WHERE CourseName = '" + all_choices[i] + "'");
        if (MaxStudents > mysql_num_rows()) // if there are free places left (the maximum number of stundents is higher then the number of stundents which are assigned to the course) assing the student to the course
        {
            mysql_query("SELECT * FROM Rel_SelectedCourses WHERE _fd_id=" + all_fd_ids[i]);
            if (mysql_num_rows()<max_courses)
            {
                //the following line inserts 1 line to the Rel_SelectedCourses table containing the currently selected student and choice (selected by the index "i")
                mysql_query("INSERT INTO Rel_SelectedCourses (_fd_id, CourseName) VALUES (" + all_fd_ids[i] + ", '" + all_choices[i] +"')";
            }
        }
    }
}


////////////////////////////////  OUTPUT ROUTINE  ///////////////////////////

int max_courses=2;  //should be a global variable

mysql_query("SELECT _fd_id, gname, iname, lname FROM N_Data_Tbl ORDER BY _fd_id");

int no_rows = mysql_num_rows();

int     all_fd_ids[no_rows];    //Array of all _fd_id's
String  all_names[no_rows][3];  //2-dimensional Array, to save all needed strings... in this case names

for(int i=0;i<no_rows;i++)  //fills the upper arrays
{
    all_fd_ids[i] = mysql_result(i,0);
    all_names[i][0] = mysql_result(i,1);                
    all_names[i][1] = mysql_result(i,2);                
    all_names[i][2] = mysql_result(i,3);                
}

String  selected_courses[no_rows][max_courses];

for(int i=0;i<no_rows;i++)
{
    mysql_query("SELECT * FROM Rel_SelectedCourses WHERE _fd_id=" + all_fd_ids[i] + " ORDER BY _fd_id");
    num_rows=mysql_num_rows();
    for(p=0;p<num_rows;p++)
    {
        selected_courses[i][p]=mysql_result(1,p);
    }
}

//we have filled all arrays now with the data we need to achieve the following output:
// gname | iname | lname | course1 | course2

for(int i=0;i<no_rows;i++)
{
    print all_names[i][0];
    print all_names[i][1];
    print all_names[i][2];
    for(p=0;p<max_courses;p++)
    {
        print selected_courses[i][p];
    }
    print endline;
}

//Ofcourse you will have to put this in a table instead... this is kept dynamic, so that you are able to change the number of courses for each stundent, if you don't need this you can still keep it - it also works for 2 courses well ;)


 example for JOIN: "SELECT N_Data_Tbl.gname, N_Data_Tbl.iname, N_Data_Tbl.lname, Rel_SelectedCourses.CourseName FROM N_Data_Tbl, Rel_SelectedCourses WHERE N_Data_Tbl._fd_id=Rel_SelectedCourses._fd_id" <--- would be a possible but bad join.... doesn't give it well formated

Het stukje code wat ik vertaald heb, werkt zo ongeveer op dat ene stukje met Recordset na, als ik die stukje code compile dan krijg ik de volgende melding "Wrong number of arguments or invalid property assignement".
Hoe fix ik dit of zit ik helemaal verkeerd met Recordsets als vervanging van mysql_result?

[ Voor 5% gewijzigd door Kage Musha op 11-01-2005 15:13 ]

[henkleerssen]

welke vba parent app heb je? Word, excel of access? of wat? Scheelt weer een beetje vandaar.
Ook.. waar precies geeft die de fout als je debugt (f8)?
Kijk ook ff wat je in die recordset krijgt per record(i).. Kijk maar eens in de locals window.
Je hoeft niet met i++ te werken in vba.. je gaat gewoon alle records af in een recordset met
.. eeuh ja.. wat was het ook alweer? Tot laatste met recordcount ...kijk maar eens..

[Kage Musha]

Sorry, vergeten te vermelden ja. Ik werk met Access (2003)

Hij geeft hier de foutmelding
all_fd_ids(i) = Recordset(i, 0)

Als ik op F8 druk gebeurt er niks maar als ik voor Compile kies dan krijg ik die foutmelding.

in the Locals Window geeft ie niks weer

en met i++ bedoel je dan dat ik Next i moet gebruiken? (heb dat ergens gezien)