Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

For <variable> in (list) ??

Discussion in 'Information Technology' started by charles.kendricks@charter.net, Jul 28, 2006.

  1. charles.kendricks@charter.net

    Guest

    I'm sure there must be something like the For loop in Unix shell
    programming available in VBA. I'm relatively new to VBA and I'm trying
    to open and print a report for several clients at once. The report is
    opened with the DoCmd.OpenReport "reportname",,,[SSN] = <variable>. I
    want to be able to extract the SSN field from a recordset and assign
    the value of SSN field from each record to the variable each time
    through the loop (I hope I'm making myself clear)
     
  2. Albert D.Kallal

    Albert D.Kallal
    Expand Collapse
    Guest

    If you build the report correctly, you can have it kick out a new page for
    each customer/client/whatever.

    If you do the above, then you don't need a loop.

    Further, do note that the "where" clause of the report (or form) is actually
    a VALID SQL WHERE clause (without the word where).

    That means you can feed the sqlWhere a "list", or even base it on another
    query!!!

    eg:

    dim strWhere as string

    strWhere = "SSN in ('123','456',789')

    Docmd.OpenReprot "myReprot",acViewPreview,,strWhere

    And, you can even use a query as the data source...

    strWhere = "SSN in (select SSN from MyQueryWithNamesToPrint)"

    And, the REAL question here is how did you generate the list of SSN numbers
    anyway? You are asking for a for...next loop. But, from what table, or what
    list is this loop to come from? Don't go back to old fashing way of
    programming with a loop, when you can simply use the "where" clause here,
    and have the report send out all of the data in one shot.

    strWhere = "City = 'Edmonton'"

    Docmd.OpenReprot "myReprot",acViewPreview,,strWhere

    So, no loop is needed.

    The ONLY reason I can think of needing a loop would be that you need to
    create a separate file, or pdf, or perhaps generate a separate text file for
    EACH individual in records you need to process.

    The "basic" record processing loop in ms-access looks like

    dim strSql as string
    dim rstRecs as dao.RecordSet
    dim strSSN as string


    strSql = "select * from tblEmployees where ActiveEmployee = True"

    set rstRecs = currentdb.OpenReocrdSet(strSql)

    do while rstRecs.EOF = false
    strSSN = rstRecs!SSN
    '....do whatever you want here
    rstRecs.MoveNext
    loop
    rstRecs.Close
    set restRecs = nothing

    So, the above is a basic loop that will process the resulting records from a
    given sql statement. As mentioned, there is likely little reason to
    re-launch a report for each interaction of the loop when you can setup your
    report to kick out a new page for each employee anyway. (it is also 100's of
    time faster in terms of processing also).


    --
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada
    pleaseNOOSpamKallal@msn.com
    http://www.members.shaw.ca/AlbertKallal
     
  3. charles.kendricks@charter.net

    Guest

    Thanks,

    Basing my strWhere string on the SQL Select query seems like a very
    logical approach to accomplish what I want to do. I haven't tried it
    yet, but I sure that you have provided just the help I needed...Thanks
    Again

    Albert D.Kallal wrote:
    > If you build the report correctly, you can have it kick out a new page for
    > each customer/client/whatever.
    >
    > If you do the above, then you don't need a loop.
    >
    > Further, do note that the "where" clause of the report (or form) is actually
    > a VALID SQL WHERE clause (without the word where).
    >
    > That means you can feed the sqlWhere a "list", or even base it on another
    > query!!!
    >
    > eg:
    >
    > dim strWhere as string
    >
    > strWhere = "SSN in ('123','456',789')
    >
    > Docmd.OpenReprot "myReprot",acViewPreview,,strWhere
    >
    > And, you can even use a query as the data source...
    >
    > strWhere = "SSN in (select SSN from MyQueryWithNamesToPrint)"
    >
    > And, the REAL question here is how did you generate the list of SSN numbers
    > anyway? You are asking for a for...next loop. But, from what table, or what
    > list is this loop to come from? Don't go back to old fashing way of
    > programming with a loop, when you can simply use the "where" clause here,
    > and have the report send out all of the data in one shot.
    >
    > strWhere = "City = 'Edmonton'"
    >
    > Docmd.OpenReprot "myReprot",acViewPreview,,strWhere
    >
    > So, no loop is needed.
    >
    > The ONLY reason I can think of needing a loop would be that you need to
    > create a separate file, or pdf, or perhaps generate a separate text file for
    > EACH individual in records you need to process.
    >
    > The "basic" record processing loop in ms-access looks like
    >
    > dim strSql as string
    > dim rstRecs as dao.RecordSet
    > dim strSSN as string
    >
    >
    > strSql = "select * from tblEmployees where ActiveEmployee = True"
    >
    > set rstRecs = currentdb.OpenReocrdSet(strSql)
    >
    > do while rstRecs.EOF = false
    > strSSN = rstRecs!SSN
    > '....do whatever you want here
    > rstRecs.MoveNext
    > loop
    > rstRecs.Close
    > set restRecs = nothing
    >
    > So, the above is a basic loop that will process the resulting records from a
    > given sql statement. As mentioned, there is likely little reason to
    > re-launch a report for each interaction of the loop when you can setup your
    > report to kick out a new page for each employee anyway. (it is also 100's of
    > time faster in terms of processing also).
    >
    >
    > --
    > Albert D. Kallal (Access MVP)
    > Edmonton, Alberta Canada
    > pleaseNOOSpamKallal@msn.com
    > http://www.members.shaw.ca/AlbertKallal
     

Share This Page