Basic question, while loops and defining strings

[redwards465]

Hi guys, been searching all over the net and simply cannot work out how to do this. I have a while loop that I am running in order to open/print reports based on a filter.



My loop looks through a query I've set up using a Recordset and is supposed to take the values and use them as a report filter so that each unique value opens/prints its own report.

Here is the snippet where I am running into problems:

            Dim ProjectCode As String
  
            ProjectCode = rs.Fields("PROJECTPHASE")
            'takes relevant query field data to generate report for each desired PROJECTPHASE. ProjectCode is set up to change the PROJECTPHASE for each loop (so reports are opened for each unique PROJECTPHASE)


            DoCmd.OpenReport "MyReport", acViewReport, "", "[Staff_w_query2]![PROJECTPHASE]='ProjectCode'", acNormal
            'filter the report by the correct PROJECTPHASE as developed above

            
            .MoveNext
            'move onto next ProjectCode

The above syntax doesn't actually assign the new ProjectCode for each loop.

Would be extremely grateful if anyone could point me in the right direction - seems like a simple fix but I'm new to all of this.

Thanks!

[ranman256]

This will work with forms and list box.
on a form put a listbox of the items to report on. (the listbox query shows all phases)
then the report looks at this listbox and only reports on the one selected.

sub btnPrintRpts_click()
dim vPhase
dim i as integer
for i = 0 to lstBox.listcount -1
  vPhase = lstBox.itemdata(i)    'get next item in listbox
  lstBox = vPhase        'set the listbox to this item

      'get vitals off the listbox
  docmd.OpenReport "myReport",acViewPreview,,"[phase]='" & lstBox & "'"
next
end sub

[pbaldy]

That would work if you were printing. The second time through the loop, the report is already open; you can't open it again.

[ssanfu]

In the record source for the report, are there two fields with the name "PROJECTPHASE"? Why do you have "Staff_w_query2" in the WhereCondition clause?

You need to concatenate the value of the string variable "ProjectCode" to the field for the WhereCondition.
And since "ProjectCode" is declared as a string, you need delimiters.

Something like

DoCmd.OpenReport "MyReport", acViewReport, , "PROJECTPHASE = '" & ProjectCode & "'", acNormal