Retrieve data w SQL and display on Report

[sum01]

Hi,

Im new to this whold VB code, and cannot figure out what im sure is a little snippet of code that will run a query, retrieve a single item, and display it in an edit box etc. on a report.

heres what ive been playing with (and various versions of it) :


Private Sub Report_Open(Cancel As Integer)

'Dim Total As String
Dim rs As New Recordset


'Set Q = CurrentDb.CreateQueryDef("")
'Q.SQL = "SELECT CP FROM Table1 WHERE Block = 3"
'Set rs = Q.OpenRecordset

Set rs = db.OpenRecordset( _
"SELECT CP FROM Table1 WHERE Block = 1", dbOpenDynaset)


End Sub


I have an edit box named TotalBlocks, and i had thought i could run the query, save it in a recordset and then assign that to the edit box.

Any help would be very much appreciated!

Thank you.

 

[PHV]

You may also consider the DLookUp function:
=DLookUp("CP", "Table1", "Block=1")

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

 

[sum01]

Hi,

Thanks for the tip. The DLookup is for retrieving the entered name from a edit box and using in a query? I already have the required result from the query but just want that value to be displayed on a form or report.

Thanks!