why doesn't my query refresh?

[Allen_N]

This seems ridiculous to me, but I can't figure out how to make the a query
window refresh itself when the underlying SQL is changed in code. (The SQL
simply changes the record number.) I can manually refresh it by clicking
Design View and then Datasheet View, but that is obviously cumbersome.

For iBaseKit = 1 To nKits ' must compare to all kits before and after
this record
strSQL = "SELECT ID AS bID, KitGroup1 AS bKitGrp, KitItem AS bKitPN
" _
& " FROM [All Kit Components]" _
& " WHERE ID = " & Format(iBaseKit)
qryAllComps.SQL = strSQL
Stop
Next iBaseKit

Thanks!

 

[Geoff]

If you are using DAO, then before "Stop" try:

objDB.QueryDefs.Refresh
Access.Application.RefreshDatabaseWindow

where objDB refers to the current database.

Geoff

 

[Marshall Barton]

This seems ridiculous to me, but I can't figure out how to make the a query
window refresh itself when the underlying SQL is changed in code. (The SQL
simply changes the record number.) I can manually refresh it by clicking
Design View and then Datasheet View, but that is obviously cumbersome.

For iBaseKit = 1 To nKits ' must compare to all kits before and after
this record
strSQL = "SELECT ID AS bID, KitGroup1 AS bKitGrp, KitItem AS bKitPN
" _
& " FROM [All Kit Components]" _
& " WHERE ID = " & Format(iBaseKit)
qryAllComps.SQL = strSQL
Stop
Next iBaseKit

It's not clear what you mean by "query window". If you are
displaying a query's data sheet, then you have to close the
window and reopen it (as you have found).

Using table/query sheet view is only useful for your own
debugging/testing. To display data to users, you need to
use a form, even if the form is displayed in Datasheet view.
Once this is done, you can just Requery the form.

OTOH, a form's RecordSource property can accept an SQL
statement, so modifying a QueryDef's SQL property is not the
only way to change the way the form's data is filtered. For
simple forms (i.e. w/o filtered subforms), another
alternative is to use the form's Filter (and FilterOn)
properties, so it's not even necessary to construct the
entire SQL statement. (Note that setting either the
RecordSource or Filter properties will automatically requery
the form.)

 

[Allen_N]

Thanks, Marshall.

Yes, I am only trying to do this while I am debugging (I am now only at the
2nd of several stages of expanding complexity in the project). And, I was
referring to the query's data sheet, as you correctly inferred.

I will follow your advice and implement a form before I go any further.

 

[Allen_N]

Hi Geoff,

That didn't work, so I'm about to implement a form.

Thanks!

If you are using DAO, then before "Stop" try:

objDB.QueryDefs.Refresh
Access.Application.RefreshDatabaseWindow

where objDB refers to the current database.

Geoff

This seems ridiculous to me, but I can't figure out how to make the a
query
window refresh itself when the underlying SQL is changed in code. (The SQL
simply changes the record number.) I can manually refresh it by clicking
Design View and then Datasheet View, but that is obviously cumbersome.

For iBaseKit = 1 To nKits ' must compare to all kits before and after
this record
strSQL = "SELECT ID AS bID, KitGroup1 AS bKitGrp, KitItem AS bKitPN
" _
& " FROM [All Kit Components]" _
& " WHERE ID = " & Format(iBaseKit)
qryAllComps.SQL = strSQL
Stop
Next iBaseKit

Thanks!