Loop code (Access2003)

Topic

New Reply

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

Reply | Quote

Replies

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!

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

If you don’t want to add FileName and FileDate, it’s not clear what you DO want to see.

Reply | Quote

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

Reply | Quote

Try this:

sqlRecNameS = !Name_Display

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

Reply | Quote

Thanks so much, exactly what I needed!

Reply | Quote

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

Reply | Quote

You should probably use

…, WhereCondition:=”Name_Display = ” & Chr(34) & strRecruitersNames & Chr(34)

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

I am working with his code…it is a bomb! I had not seen where it is located before, thanks for the push!

Reply | Quote