Access 2007 Hangs when Setting Recordset to a subfrom

[id1234]

The below code is searching for a record based on information entered on a form using a stored query. The results are in a recordset which is then sent back to a form.

I am having a problem with the code hanging when the recordset is passed back to the form (Put a *** at the begining of the line of code that hangs). Access does not throw an error it justs acts as though it is still processing or waiting for a db response and the only thing I can do is kill the program via Task Manager. It does not hang everytime but most of the time and I can not seem to find a pattern.


CODE
Public Sub SearchScorecard(frmScorecard As Form_Search_Scorecard_Form, frmSCDatasheet As SubForm)

Dim db As DAO.Database
Dim rstScoreCard As DAO.Recordset
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Set db = CurrentDb

If frmScorecard.txtScorecardID <> "" Then
Set qdf = db.QueryDefs("Q_SCORECARD_ByScorecardID")
qdf.Parameters("currScorecardID") = frmScorecard.txtScorecardID
Else
Set qdf = db.QueryDefs("Q_SCORECARD_SearchAll")
qdf.Parameters("currPeople") = frmScorecard.cboName
qdf.Parameters("currTeam") = frmScorecard.cboTeam
qdf.Parameters("currTicket") = frmScorecard.txtTicket
qdf.Parameters("currCategory") = frmScorecard.cboCategory
qdf.Parameters("currType") = frmScorecard.cboType
qdf.Parameters("currItem") = frmScorecard.cboItem
qdf.Parameters("currQueue") = frmScorecard.cboQueueType
qdf.Parameters("currServiceType") = frmScorecard.cboServiceType
qdf.Parameters("currEvalDate") = frmScorecard.txtEvalDate
qdf.Parameters("currCanceled") = frmScorecard.ChartSpace

End If
Set rstScoreCard = qdf.OpenRecordset(dbOpenDynaset)
If rstScoreCard.BOF And rstScoreCard.EOF Then
MsgBox ("No Record Found!")
Exit Sub
End If

***Set frmSCDatasheet.Form.Recordset = rstScoreCard 'Hangs here

End Sub
END CODE

 

[ajetrumpet]

***Set frmSCDatasheet.Form.Recordset = rstScoreCard 'Hangs here

End Sub[/SIZE]
END CODE

r u trying to change the recordSOURCE? i've never used the recrodset property, what is it used for? what are you trying to do with it?

if ur trying to use the SOURCE instead of the SET, u need not use the SET keyword...the equality is statement is good enuf, alog with a requery afterwards

 

[id1234]

I am setting the Recordset. I could not find a way to just assign the recordsource because I have paramaters in the Query that need to be assigned in the QueryDef.

 

[boblarson]

I am setting the Recordset. I could not find a way to just assign the recordsource because I have paramaters in the Query that need to be assigned in the QueryDef.

So you tried this:

Me.frmSCDatasheet.Form.RecordSource = qdf

 

[id1234]

So you tried this:

Me.frmSCDatasheet.Form.RecordSource = qdf

That line of code causes a Type Mismatch error. As best as I know the Recordsource property is looking for an SQL statement which can be assigned using qdf.SQL:

Me.frmSCDatasheet.Form.RecordSource = qdf.SQL
​

However, I can't use that because there are parameters in my SQL statement that are assigned from data on my form and the assigned values of these parameters do not get passed with the qdf.SQL statement.

 

[boblarson]

However, I can't use that because there are parameters in my SQL statement that are assigned from data on my form and the assigned values of these parameters do not get passed with the qdf.SQL statement.

Actually, you ARE setting the parameters using the QDF so if what I supplied you doesn't work, change to using

qdf.Name

instead of just qdf.


Me.frmSCDatasheet.Form.RecordSource = qdf.Name

 

[id1234]

I tried Me.frmSCDatasheet.Form.RecordSource = qdf.Name and it asked me to enter the parameters.

You are right that I am setting the parameters before getting to the above line of code but the parameters are set with in the QueryDef object qdf.Name is just the name of the Query that is being used with in the Query Def object.

Please correct me if I am wrong but the only thing I know to do with a Query Def is use it to set up a query then open a Recordset.