Loop with SQL result

rafa.toshio · Feb 5, 2010

hi, I'm new here and my english is not good (I'm sorry). However, I have a doubt that I can't resolve:
I wanna make a "Loop" using the lines of a querry, but I don't wanna use a Form to do this because it is too slow.

I like something like that:

DomCmd.RunSql SQLstring

For i = 1 to <<sql_result_numberlines>>
CommandBars(<<sql_columm1>>).Controls(<<sql_columm2>>).Visible = False
Next i

Any ideia?

HiTechCoach · Feb 5, 2010

I would use a recordset and loop through the records.

Example:

Code:

        Dim db as Database
        Dim rst As DAO.Recordset

        Set db = CurrentDb()
        Set rst = db.OpenRecordset("MyTable")
        
        Do while Not rst.EOF
            If rst![MyField] <> Something Then  'The real loop exit condition.
                Exit Do
            End If
             ' Rest of your code here.
            rst.MoveNext
        Loop


        rst.Close            'Close what you opened.

See:
Using Recordsets

rafa.toshio · Feb 5, 2010

Tks HiTech it works perfectly! I think that this way is faster than work with forms, isn't is? Thanks again

rafa.toshio · Feb 5, 2010

Your code works, but I have another problem now

I tested use your code

Code:

        Set db = CurrentDb()
        Set rst = db.OpenRecordset("MyTable")

Instead "My Table" I put a SQL statement and works, but I would like put a querry and pass parameters. Like stored procedures in .NET

Set rst = db.OpenRecordset("querryName " & myParameter)

Is that possible?

HiTechCoach · Feb 6, 2010

AFAIK, it is not possible with the openrecordset command.

The way I do it in Access VBA is to modify the QuerDef before opening the recordset.

Loop with SQL result

rafa.toshio

New member

HiTechCoach

Well-known member

rafa.toshio

New member

rafa.toshio

New member

HiTechCoach

Well-known member

Similar threads

Users who are viewing this thread