Form does not display updated querydef records

  • Thread starter Bill R via AccessMonster.com
  • Start date

B

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
 
Ad

Advertisements

B

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
 
V

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.
 
V

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!
 
B

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.
 
Ad

Advertisements

B

Bill R via AccessMonster.com

I successfully implemented your solution. Thanks.

Bill

Brendan said:
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]
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top