QueryDef parameters and reports - Help please 1

[UHNSTrust]

I do not know if this is possible even but I am trying to use Querydef parameters to provide the recordsource for a report. I thought this would be far more efficient than using a filter on the report when the base table has thousands of records. I have tried the following code but the report still asks for a value for the parameter.

Public Function AddNewNotesTrace(ByVal pHospNo As String, _
ByRef pdbsCurrent As DAO.Database) As Boolean

'inserts a new notes trace record for the given patient
'returns a true value if successful

Dim qdfAddNew As DAO.QueryDef
Dim prmPatient As DAO.Parameter

On Error GoTo AddNewPatient_Err
AddNewNotesTrace = False

Set qdfAddNew = pdbsCurrent.QueryDefs("selHospNo")
Set prmPatient = qdfAddNew.Parameters("HospNo")
prmPatient.Value = pHospNo

DoCmd.OpenReport "rptHospNo", acViewPreview

Set prmPatient = Nothing
Set qdfAddNew = Nothing

AddNewNotesTrace = True
Exit Function

AddNewPatient_Err:
'error handler

End Function

Can it be done? Any help/advice would be appreciated.

Thanks

Jonathan

 

[LittleSmudge]

I'm really struggling to read the green text of your post - virtually no contrast with the blue on this monitor

I use a similar approach because I have tables in a MySQL database and I get to them via 'PassThrough' queries ( very quick and efficient because the MySQL server does the filtering work. ) so to pass parameters to the query I use the following

Public Sub ParamToPT(strQueryName As String, strClause As String)
Dim strSQL As String
Dim intPosn As Integer
   
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Set db = CurrentDb
Set qd = db.QueryDefs(strQueryName)

strSQL = qd.SQL
' in case the existing version has been saved with a Where Clause
' Strip the Where clause off the end
intPosn = InStr(strSQL, "WHERE")
If intPosn > 0 Then
    strSQL = Left(strSQL, intPosn - 1)
End If
' Now add the new Where clause
strSQL = Trim(strSQL) & " WHERE " & strClause
qd.SQL = strSQL

Set qd = Nothing
Set db = Nothing

End Sub

strQueryName is simply the name of the query

strClause is the Where clause without the WHERE word

I use it as follows

Dim clause as String

clause = "Field1 = " & variableToSelectOn

Call ParamToPT("qryPTvQueryName",clause)
DoCmd.OpenReport rptDemo, etc

Where the rptDemo has qryPTvQueryName as its Recordsource.



'ope-that-'elps.



G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.