VBA Do While RRS feed

  • Question


    If I have a loop like the following, do the non-explicit,
    loop-scoped variables get reused or recreated every time?
    Please explain the technical reasons for your answer.
    ‘ Option Explicit -- off ‘ Option Compare Database -- off Dim rs1 As DAO.Recordset Set rs1 = “SELECT DISTINCT Field1, Field2 FROM NameList;” ‘ List has 1000 names Do While rs1.EOF varTest1= rs1!Field1 'Variants occur because no explicit typing varTest2 = rs1!Field2 Next



    Saturday, July 7, 2012 5:25 PM

All replies

  • I always declare all variables explicitly, i.e. I always have Option Explicit in each and every module.

    Although you don't show it, the code that you posted would be in a procedure (sub) or function. Whether declared or not, the variables varTest1 and varTest2 will be created anew each time the procedure or function is started, and destroyed when the procedure or function ends.

    By the way, your code is not valid. A recordset is not a SQL string. The line

    Set rs1 = “SELECT DISTINCT Field1, Field2 FROM NameList;”

    should be something like

    Set rs1 = CurrentDb.OpenRecordset("SELECT DISTINCT Field1, Field2 FROM NameList;", dbOpenDynaset)

    Please note that I used straight quotes around the text string instead of curly quotes. VBA chokes on curly quotes.

    Regards, Hans Vogelaar

    Saturday, July 7, 2012 5:40 PM
  • Try this one in form.

    Private Sub Tablica_ActiveX()

    'add ActiveX Data Objects 2.5 library

    const ConStr as string = "Provider=..." '<-your connection

    Dim c As New ADODB.Connection
    Dim d As New ADODB.Recordset
    Dim pytanie$ pytanie = "SELECT DISTINCT Field1, Field2 FROM NameList" UserForm1.ListBox1.columncount = 2

    On Error GoTo koniec If c.state <> 1 Then c.Open ConnStr If d.state = 1 Then d.Close d.Open pytanie, c, adOpenStatic, adLockReadOnly While Not d.EOF with UserForm1.ListBox1

    .AddItem ""
        .Column(0, .ListCount - 1) = d!Field1
        .Column(1, .ListCount - 1) = d!Field2 end with

    d.MoveNext Wend d.Close UserForm1.Show vbModal Exit Sub koniec: MsgBox "Error connection" & err.decription, vbCritical, End Sub

    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved

    Saturday, July 7, 2012 6:24 PM