Recordsets!!!!!!!!!

[scottian]

Can anyone tell me why i get an error with this.

All im trying to do is get the top number from a table to a variable to use in an automated e-mail. But i get type mismatch with the msgbox line highlighted.

'===============================
Function TopNum()
Dim Ndb As Database
Dim PNum As Recordset
Dim Nsql As String

PNumsql = "SELECT TOP 1 qryTopNum.NumberField FROM qryMyQuery;"

Set Ndb = CurrentDb()

Set PNum = Ndb.OpenRecordset(Nsql)

MsgBox PNum
End Function

"My God! It's full of stars...

 

[rjoubert]

You can't throw a recordset object up into a msgbox. You'll need to loop thru the recordset and place values from a field (or multiple fields) from your recordset into the msgbox.

 

[PHV]

MsgBox PNum.Fields(0)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[scottian]

Awesome, Thanks PHV.

the msgbox was just to test that the value was passed. If im to use this in a larger function (which will compile an e-mail based on this value) would i still need to use the 'MsgBox PNum.Fields(0)' solution?
Because, the number it returns needs to be referenced from within another SQL statement which is passed to another recordset, which is then (as you stated rjoubert) looped through, concatenating the records to produce an e-mail message body.

"My God! It's full of stars...