Strange query result.

[longmatch]

I am writing this code try to manipulate data using recordset.

Set db = CurrentDb
strSQL = "SELECT * FROM PtDx WHERE AdmitID=" & [Forms]![admitpt]![AdmitSubform]![Admit ID]
Set rstPtDx = db.OpenRecordset(strSQL)
rstPtDx.MoveFirst
Do Until rstPtDx.EOF
strDx = rstPtDx!Diagnosis & ", " & rstPtDx!ICD9 & " "
rstPtDx.MoveNext
Loop

When I ran the query

"SELECT * FROM PtDx WHERE AdmitID=" & [Forms]![admitpt]![AdmitSubform]![Admit ID]" in access database using query,
it returns correct record number. But when I ran it in module, it only shows 1 record. I don't know why.


Thanks for your help

Haijun

 

[StarPassing]

Your problem is that you are not populating the recordset.
Change your code to something like:

Set rstPtDx = db.OpenRecordset(strSQL)
If rstPtDx.recordset > 0 then
rstPtDx.Movelast ' populate recordset
rstPtDx.MoveFirst
Else
‘ show message box - no records
end if
Do while not rstPtDx.eof
….
loop


Tom

 

[longmatch]

Thank you very much. This problem was solved under your help.

Haijun