Changing the query def doesn't update the recordset in a form

[Bill R via AccessMonster.com]

I use the following code:

With Forms!Drawing_Sign_Off_Form.Form
If .Controls("chkSTX") Then
strSQL = strSQL & "WHERE DOC_TYPE=""STX""" & vbCrLf
Else
strSQL = strSQL & "WHERE DOC_TYPE<>""STX""" & vbCrLf
End If
strSQL = strSQL & "ORDER BY Status.[Recovery Date], IPT_NO, DWG_NO;"
qdf.SQL = strSQL
.Requery
End With

To change the Where clause of a query underlying a form. The problem is, the
form just won't change the recordset to reflect the query results. I check
the query after running the program and it's recordset is changed, but the
form still shows the recordset which the query no longer returns. I tried
hitting F9 and Shift F9, but nothing happens. The chkbox is unbound. This
code runs on after update of the chkbox and the onload event of the form.

 

[Mark S via AccessMonster.com]

This is exactly the same problem that I have also just posted in the
microsoft.public.access.formscoding usergroup.

I'll keep you posted if I get anything that works on my thread.

Regards,

Mark

 

[Marshall Barton]

I use the following code:

With Forms!Drawing_Sign_Off_Form.Form
If .Controls("chkSTX") Then
strSQL = strSQL & "WHERE DOC_TYPE=""STX""" & vbCrLf
Else
strSQL = strSQL & "WHERE DOC_TYPE<>""STX""" & vbCrLf
End If
strSQL = strSQL & "ORDER BY Status.[Recovery Date], IPT_NO, DWG_NO;"
qdf.SQL = strSQL
.Requery
End With

To change the Where clause of a query underlying a form. The problem is, the
form just won't change the recordset to reflect the query results. I check
the query after running the program and it's recordset is changed, but the
form still shows the recordset which the query no longer returns. I tried
hitting F9 and Shift F9, but nothing happens. The chkbox is unbound. This
code runs on after update of the chkbox and the onload event of the form.

Sorry guys, I can't explain it, unless maybe you need a
DoEvents and/or a db.QueryDefs.Refresh, because I don't see
any reason to go to all the trouble of constructing the SQL
statement without assigning it directly to the form's
RecorSource property. Changing the RecordSource property
even requeries the form automatically.

 

[mqshaw]

Bill,

Here is a post on my thread that helped me fix the problem that I was having ;

Something *must* be using query [Customer Info] or else I am missing
something. Here is some code I put together as an example

Private Sub Combo1_Click()

Dim db As database
Dim qdf As querydef
Dim strsql As String

strsql = "SELECT Sheet1.* FROM Sheet1"

strsql = strsql & " Where Code = '" & Combo1.Column(0, Combo1.ListIndex) &
"';"

Set db = CurrentDb()
Set qdf = db.QueryDefs("Query1")
qdf.SQL = strsql
Me.Query1_subform1.Form.RecordSource = "Query1" '*******
Me.Query1_subform1.Requery
End Sub

If I omit the asterisked line, I get the same problem you describe