OpenReport fooling me

[tomking505]

I know just enough to be dangerous, but clearly not enough to be useful.

My WHERE clause in the following DoCmd.OpenReport statement doesn't work

Option Compare Database

Public Function Closer2()

Dim MyDB As DAO.Database
Dim rstParseNames As DAO.Recordset
Dim stJustOne

Set MyDB = CurrentDb
Set rstParseNames = MyDB.OpenRecordset("Clients", dbOpenDynaset)

With rstParseNames
  If Not .BOF And Not .EOF Then     'has at least 1 Record
    Do While Not .EOF

    stJustOne = ![Clientid]
    MsgBox stJustOne
     
'next line: msgbox increments correctly, report always shows first Client in table
DoCmd.OpenReport "All Checklists Report2", acViewNormal, , ![Clientid] = stJustOne, acDialog
   
        .MoveNext
    
    Loop
  Else
    'fall through
  End If
End With

rstParseNames.Close
Set rstParseNames = Nothing

End Function

The select statement that relates all the tables is in a query, and the query name is listed in the reports Record Source.

Any ideas, friends?

Thanks,

Tom

 

[boblarson]

Should be this if the field in the report is named ClientID:

DoCmd.OpenReport "All Checklists Report2", acViewNormal, , "[Clientid] =" &  stJustOne, acDialog

If ClientID is text then you would need delimiters:

DoCmd.OpenReport "All Checklists Report2", acViewNormal, , "[Clientid] =" & Chr(34) &  stJustOne & Chr(34), acDialog

 

[tomking505]

Holy Cow! I just posted that a few seconds ago! Thank you very much..

I pasted that in. Oh my god, it's working.

I've been a programmer (not VBA) for decades. This simple two hour project has gone on two days now, and I'm embarrassed to say I just wiped away a tear.

Thank you very, very much.

 

[boblarson]

Glad we could be of assistance and by the way: