Requery problem

[Beetle]

I'm sure I'm missing something simple here, but I'm just not seeing it
right now.

A2003. I have a form which uses a query as it's recordset. I have a command
button on the form that resorts the records when clicked. The code all works
fine except that the form won't requery. The stored query gets the new SQL
an if I open it directly it shows the correct results, but the form won't
show the
new query records until I close and reopen it, even with Me.Requery in the
code.
Code (example) is as follows;

Dim strSQL As String
Dim qdf As QueryDef

strSQL = "blah blah blah a bunch of SQL"

set qdf = Currentdb.QueryDefs ("qryCustomerSort")

qdf.SQL = strSQL
Me.Requery '<<< this doesn't work

qdf.Close
Set qdf = Nothing

Like I said, I'm probably missing something completely obvious, but so far
all I'm getting is bruises on my forehead.

Any ideas?

 

[Maurice]

If me.requery doesn't work why not reset the recordsource to see if that helps.

I assume the querydef is the recordsource for the form.
After you've created the querydef set the recordsource to "" and then
re-attach the recordsource by setting it to the querydef.

me.recordsource="qryCustomerSort"
and the me.requery for a final tick

 

[Beetle]

Yeah, that worked. I just had to do;

qdf.SQL = strSQL
Me.RecordSource = "qryCustomerSort"

No need to set the RecordSource to "" or to requery.

Thanks for the jump.

 

[jlguitar287]

Beetle wrote on 09/23/2008 15:35 ET

I'm sure I'm missing something simple here, but I'm just not seeing i
right now

A2003. I have a form which uses a query as it's recordset. I have a comman
button on the form that resorts the records when clicked. The code all work
fine except that the form won't requery. The stored query gets the new SQ
an if I open it directly it shows the correct results, but the form won'
show th
new query records until I close and reopen it, even with Me.Requery in th
code
Code (example) is as follows

Dim strSQL As Strin
Dim qdf As QueryDe

strSQL = "blah blah blah a bunch of SQL

set qdf = Currentdb.QueryDefs ("qryCustomerSort"

qdf.SQL = strSQ
Me.Requery '<<< this doesn't wor

qdf.Clos
Set qdf = Nothin

Like I said, I'm probably missing something completely obvious, but so fa
all I'm getting is bruises on my forehead

Any ideas
________

Sean Baile

I have exactly the same problem, can you please help
It should be super simple to understand

Option Compare Databas

Private Sub btnRun_Click(

Dim db As DAO.Databas
Dim qdf As DAO.QueryDe
Dim varItem As Varian
Dim strCriteria As Strin
Dim strSQL As Strin

Set db = CurrentDb(
Set qdf = db.QueryDefs("Query"
For Each varItem In Me!listBox.ItemsSelecte
strCriteria = strCriteria & "," & Me!listBox.ItemData(varItem
& "
Next varIte
If Len(strCriteria) = 0 The
MsgBox "You did not select anything."
, vbExclamation, "Nothing to find!

Exit Su
End I

strCriteria = Right(strCriteria, Len(strCriteria) - 1
strSQL = "SELECT * FROM TestTable " &
"WHERE TestTable.Field1 IN(" & strCriteria & ");

strSQL = "SELECT * FROM TestTable Where TestTable.Field1 Lik
""" & strCriteria & """;
qdf.SQL = strSQ
MsgBox strSQ

DoCmd.OpenQuery "Query
Me.RecordSource = "Query" '<-----i press the button to query, i
goes to the correct form an
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''filter
everything, but i go back to the form, select something ne
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''from my lis
box, but it just stays the same as the first query..
Set db = Nothin
qdf.Clos
Set qdf = Nothing