Requery after you modify the SQL statement in VBA Access?

J

jlguitar287

Problem: I press the button to query, it goes to the correct form and filter
everything, but i go back to the form (without closing the Query), selec
something new from my list box, but it just stays the same as the firs
query..

Note: DoCmd.Requery doesn't wor

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, it goes to the correct form and filter
'everything, but i go back to the form, select something new from 'my list box
but it just stays the same as the first query..

Set db = Nothin
qdf.Clos
Set qdf = Nothin

'Please Help!
 
J

JHB

Have you tried
Have you tried Me.Requery (not DoCmd.Requery) after Me.RecordSource = "Query"?

Here some note from the Help file.

The Requery method of the DoCmd object is different from the Requery method in Visual Basic. The Requery method of the DoCmd object was added to provide backwards compatibility for running the Requery action in Visual Basic code in Microsoft Access 95. If you want to requery a control that's not on the active object, you must use the Requery method in Visual Basic, not the Requery action or its corresponding Requery method of the DoCmd object. The Requery method of a form or control in Visual Basic is faster than the Requery action or the DoCmd.Requery method. In addition, when you use the Requery action or the DoCmd.Requery method, Microsoft Access closes the query and reloads it from the database, but when you use the Requery method, Microsoft Access reruns the query without closing and reloading it.

Else I'll suggest, set the Me.RecordSource to a zero string in the top of you procedure.

Me.RecordSource = ""



Regards

Jørn
 

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

Similar Threads

Query Inside an SQL statement 3
MS ACCESS: SEARCH in a split form 0
Multi Selection List Box Query 6
data type mismatch 4
Append Query with VBA and SQL 1
Problems with SQL in vba code 2
Query Input must contain at least one table or query 2
query based on multiselctbox 1

Top