Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Loop code (Access2003)

    All right...

    I have

    With qdfActiveRecruiter
    sqlRecNameS = "SELECT DISTINCT Candidate.Name_Display FROM Candidate ORDER BY Name_Display "
    Set rstRS = CurrentDb.OpenRecordset(sqlRecNameS, dbOpenSnapshot)
    rstRS.MoveFirst
    End With

    ' Build the output string.
    With rstRS
    Do While Not .EOF
    sqlRecNameS = sqlRecNameS '& " " & FileName & " " & FileDate
    '!au_id & ": $" & (10 * !royaltyper) & vbCr
    .MoveNext
    Loop
    End With

    My Loop is counting correctly 20 records BUT sqlRecNameS is not showing any. Why?


    Thanks

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Loop code (Access2003)

    2 problems.

    First of all, you used sqlRecNameS to build the SQL string you used in to open rstRS. You would want to clear it before loading it with the results of reading through the recordset. So you need to do this before reading: sqlRecNameS = "" (this is 2 quote marks)

    Next, is this line of code:

    sqlRecNameS = sqlRecNameS '& " " & FileName & " " & FileDate

    You seem to have a spare apostrophe before the & symbol, effectively making everything after it a comment!
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Loop code (Access2003)

    Thanks, however I am not understanding following.
    sqlRecNameS = "" had changed nothing.

    sqlRecNameS is still not getting any values after "" and .MoveNext

    I am trying to understand why is it my sql sqlRecNameS = "SELECT DISTINCT Candidate.Name_Display FROM Candidate ORDER BY Name_Display "
    is not showing records.

    When I am stepping trough with F8
    Set rstRS = CurrentDb.OpenRecordset(sqlRecNameS, dbOpenSnapshot)
    sqlRecNameS has value of SELECT DISTINCT Candidate.Name_Display FROM Candidate ORDER BY Name_Display
    and
    dbOpenSnapshot =4 (I am not sure what 4 is for - I have 20 records in a table)

    Please, look into this one more time, maybe there is something else missing. Thanks

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Loop code (Access2003)

    Look VERY carefully at this line of code you have:

    sqlRecNameS = sqlRecNameS '& " " & FileName & " " & FileDate


    EVERYTHING after the apostrophe (the single-quote character) is essentially just a comment. So your code is really just this:

    sqlRecNameS = sqlREcNameS
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Loop code (Access2003)

    I see this.
    What I want is not to add FileName and FileDate yet.
    I just want to see how is my F8 reading values from the table.

    Let say if I never had this part '& " " & FileName & " " & FileDate
    what would my correct code be look like - this part

    With rstRS
    Do While Not .EOF
    sqlRecNameS = ???????????
    .MoveNext
    Loop
    End With

    thanks

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Loop code (Access2003)

    If you don't want to add FileName and FileDate, it's not clear what you DO want to see. <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  7. #7
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Loop code (Access2003)

    My table containing names, so stepping trough I want to see my sqlRecNameS is taking upon itself a value of each record like round of Names
    .MoveNext first round
    sqlRecNameS=Name2
    ...

    .MoveNext second round
    sqlRecNameS=Name3

    I am probably mistaking something but I am researching and maybe you can just point me in correct direction.

    I will have sqlRecNameS=NameFromTheTable
    and Report will be printed for this name and then saved into C: with FileName & " " & FileDate
    but before I am adding FileName & " " & FileDate
    I want to make sure that I am looping trough the table and my loop is reading the names from the table.

    thanks

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Loop code (Access2003)

    Try this:

    sqlRecNameS = !Name_Display

    This will give you the value of Name_Display in each record.

  9. #9
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Loop code (Access2003)

    Thanks so much, exactly what I needed!

  10. #10
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Loop code (Access2003)

    Code seems working

    Set qdfActiveRecruiter = dbsCurrent.CreateQueryDef("")
    With qdfActiveRecruiter
    sqlRecNames = "SELECT DISTINCT Candidate.Name_Display FROM Candidate ORDER BY Name_Display"
    Set rstRS = CurrentDb.OpenRecordset(sqlRecNames, dbOpenSnapshot)
    rstRS.MoveFirst
    End With

    ' Build the output string.
    With rstRS
    Do While Not .EOF
    strRecruitersNames = !NAME_DISPLAY
    ReportName = FileName & " " & strRecruitersNames & "_" & FileDateFormat

    DoCmd.OpenReport ReportName:="Candidate_RecruiterNull", View:=acViewPreview, WhereCondition:=strRecruitersNames
    DoCmd.Save acReport, ReportName

    .MoveNext
    Loop
    End With
    ************************************************** *******************************************

    All is working but it seems I am having an issue with this line
    WhereCondition:=strRecruitersNames

    Report wouldn't open - it says one parameter in query '(John, Smith K)' is missing.

    But this is the name from the table for whom Report should be printed and I do not undertsand what is wrong.
    I had tried for hours and decided to ask you. Thanks for looking into it.

    P.S. When I will pass problem line I want to go to
    DoCmd.Save acReport, ReportName
    and save Report into C: directory in PDF format.
    Then it will continue to loop and open up another Report with another recruiter name and do this like 20 times...Thanks

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Loop code (Access2003)

    You should probably use

    ..., WhereCondition:="Name_Display = " & Chr(34) & strRecruitersNames & Chr(34)

  12. #12
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Loop code (Access2003)

    Awesome - how did you know?
    One more thing though...I passed the line, I am looking AT the open Access Report with 11 pages of it but when I go
    DoCmd.Save acReport, ReportName
    it gives me the error Report "Candidate_John, Smith_09_15_08" is not open

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Loop code (Access2003)

    DoCmd.Save does not save a file, it saves the design of the specified object in the database. To save as a PDF file, you need to print to a PDF printer such as that from Adobe or use Stephen Lebans' free utility ReportToPDF.

  14. #14
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Loop code (Access2003)

    Hans, thanks but Leban's site is very outdated. I am using PDF Dostoller to print out Reports and Lebanon does not mention it at all.
    I am still in searching - I use to do it years ago and I hope to find it again, if anyone has any idea, please, share. Thanks

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Loop code (Access2003)

    Stephen Lebans is undoubtedly aware of the existence of third-party products to create PDF files; he wanted to provide a utility that works entirely within Access.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •