Return records by function

[Gina]

Hi all.

I am calling my function DisplayDetails as below from a form:

res_Details = Modul1.DisplayDetails(SQL_Details)

and do get an empty string back ... what is wrong ??
_______________________________________________
Public Function DisplayDetails(ByVal recs As String) As String
Dim DB As DAO.Database
Dim rs As DAO.Recordset
Dim mySQL, rst, mat As String
Dim numRec As Integer

mySQL = recs
mat = ""
Set DB = CurrentDb()
Set rs = DB.OpenRecordset(mySQL)
numRec = rs.RecordCount

If rs("Amount") <> "" Then
rs.MoveFirst
Do
rst = rs("Amount")
rst = rst & vbTab & rs("Material").Value
rst = rst & vbTab & FormatCurrency(rs("Cost"), 2)
mat = mat & rst & vbCrLf
rs.MoveNext
Loop Until rs.EOF
End If

rs.MoveLast
rs.Close
DB.Close

End Function
_______________________________________

Thanks Gina

 

[Nikos Yannacopoulos]

Gina,

To begin with, you have forgotten to include something like:

DisplayDetails = rst

after the loop, so you actually assign a value to the function; as is
you are not assigning a value, which is a first good reason why it
returns Null.

An additional possible reason (once the above is fixed), is that the SQL
expression passed as a parameter may return no records. You might need
to check this after fixing the above.

Third point to notice is that you are acually resetting rst at each
record; was that the aim? I doubt it, or you wouldn't loop. Change
rst = rs("Amount")
to
rst = rst & vbTab & rs("Amount")

It's good practice to add:
Set rs = Nothing
Set DB = Nothing
after closing the objects.

Finally, I don't see NumRec used anywhere (left behind from a previous
attempt to loop not utilising rs.EOF?), but if you do need it, make sure
you precede the rs.RecordCount with an rs.MoveLast; the RecordCount has
this nasty habit of returning 1 regardless of the actual count, if the
recordset is not forced to go beyong the first record!

HTH,
Nikos

 

[Gina]

Hi Nikos.

done all what you suggested and yes, it works fine now ...

what I've tried to do with the loop is to collect all the stuff returned by
rs in one string and return it by the actual function DisplayDetails = mat
so that I can further put it into a word document - that will be another
story

everything is returned as expected
Big Thanks ... you were very helpful to me

Gina