Form does not display updated querydef records

[Bill R via AccessMonster.com]

The last 2 lines of my code are:

db.QueryDefs("qryFrmPlanning").SQL = strSQL
frm.Requery

"frm" is a form based on the query "qryFrmPlanning". However, in order to see
the newly defined recordset, I have to open the form properties (not easy in
form view), hit the build button for the recordsource, run the query in the
grid, close the query, and move out of the recordsource property. Then the
form actually requeries and displays the correct records. Obviously, that's
not a usable solution.

Inasmuch as I know my code is returning a valid recordset, and the form is
set to the correct query, why doesn't it automatically update at the frm.
Requery command?

Thanks,

Bill

 

[Brendan Reynolds]

I found I had to make two changes to get this to work reliably. 1) I had to
use DBEngine.Workspaces(0).Databases(0) instead of CurrentDb. And 2) I had
to re-assign the query to the form's recordsource before requerying the
form. See the comments in the test code below ...


Private Sub cmdTest_Click()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String

'First change ...
'Set db = CurrentDb
Set db = DBEngine.Workspaces(0).Databases(0)

Set qdf = db.QueryDefs("qryTest")
strSQL = qdf.SQL
If InStr(1, strSQL, "1") > 0 Then
strSQL = "SELECT * FROM tblTest WHERE TestNum = 2"
Else
strSQL = "SELECT * FROM tblTest WHERE TestNum = 1"
End If
qdf.SQL = strSQL

'Second change ...
Me.RecordSource = "qryTest"

Me.Requery

End Sub

 

[Van T. Dinh]

I take a different tack from Brendan's method.

Instead of going through QueryDef to update the SQL String of the saved
Query being used as the RecordSource for the Form and THEN re-query the
Form, you can simply set the RecordSource of the Form using the SQL String
like:

frm.RecordSource = strSQL

Access will see that a new RecordSource is assigned and will re-quert the
Form automatically for you.

 

[Van T. Dinh]

Ignore this. I've just seen your earlier thread.

Please refrain from posting multiple threads on the same subject, especially
that you are still receiving replies to the earlier thread!

 

[Bill R via AccessMonster.com]

Thank you all for your help. I already confessed my sin and apologized. I'm
only human.

Ignore this. I've just seen your earlier thread.

Please refrain from posting multiple threads on the same subject, especially
that you are still receiving replies to the earlier thread!

I take a different tack from Brendan's method.

[quoted text clipped - 7 lines]

Access will see that a new RecordSource is assigned and will re-quert the
Form automatically for you.

 

[Bill R via AccessMonster.com]

I successfully implemented your solution. Thanks.

Bill

I found I had to make two changes to get this to work reliably. 1) I had to
use DBEngine.Workspaces(0).Databases(0) instead of CurrentDb. And 2) I had
to re-assign the query to the form's recordsource before requerying the
form. See the comments in the test code below ...

Private Sub cmdTest_Click()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String

'First change ...
'Set db = CurrentDb
Set db = DBEngine.Workspaces(0).Databases(0)

Set qdf = db.QueryDefs("qryTest")
strSQL = qdf.SQL
If InStr(1, strSQL, "1") > 0 Then
strSQL = "SELECT * FROM tblTest WHERE TestNum = 2"
Else
strSQL = "SELECT * FROM tblTest WHERE TestNum = 1"
End If
qdf.SQL = strSQL

'Second change ...
Me.RecordSource = "qryTest"

Me.Requery

End Sub

The last 2 lines of my code are:

[quoted text clipped - 19 lines]

Bill