Programatically refresh query

[PeDa]

My application changes the data in a table. When I immediately look at (the record count) of a query (not bound to a form control) based on that table, it reflects the situation prior to the change (if I exit and restart the application I do see the change). How can I programatically refresh the query?

 

[lameid]

Where how are you getting the recordcount?

Unless you have something weird going on it should be available immediately. The only way you should have a problem is if you already have the data you are counting open, which brings me back to my original question.

 

[PeDa]

Thank you for your response.

The dataset of my open form frmStudies is a union query between a table of archived data (tbArchiveStudies) and a table of unarchived data (tbStudies). I want to dearchive the (archived) Study currently shown on the form. This means setting the flag gArchive_b to false, copying the record from tbArchiveStudies to tbStudies, and then deleting it in the former table. At the same time I move associated data from tbArchiveInspecties to tbInspecties. I then reset the dataset of the form to only unarchived data and go to my (now unarchived) record. This all works fine.

gloNumber_n = Forms!frmStudies!gKEYFIELD 'remember this voor later
DoCmd.RunSQL ("UPDATE tbArchiveStudies SET gArchive_b = False WHERE (gKEYFIELD= " & Str(Forms!frmStudies!gKEYFIELD) & ");") 'set the archive flag to False 
DoCmd.RunSQL ("INSERT INTO tbStudies SELECT * FROM tbArchiveStudies WHERE (gArchive_b=False);")
'add the audit rapports of this study to tbInspecties
DoCmd.RunSQL ("INSERT INTO tbInspecties SELECT tbArchiveInspecties.* FROM tbArchiveInspecties LEFT JOIN tbArchiveStudies ON tbArchiveInspecties.iSTUDYKEY = tbArchiveStudies.gKEYFIELD WHERE (tbArchiveStudies.gArchive_b=False);")
'delete these auditrapports from tbArchiveInspecties
DoCmd.RunSQL ("DELETE tbArchiveInspecties.* FROM tbArchiveInspecties LEFT JOIN tbArchiveStudies ON tbArchiveInspecties.iSTUDYKEY = tbArchiveStudies.gKEYFIELD WHERE (tbArchiveStudies.gArchive_b=False);")
DoCmd.RunSQL ("DELETE FROM tbArchiveStudies WHERE (gArchive_b=False);")

The problem comes when I count the records in a query "SELECT * FROM tbInspecties WHERE iSTUDYKEY = gKEYFIELD). This returns 0, and not the number that have just been moved from tbArchiveInspecties. If I close the frmStudies, and reopen it, and go to the relevant Study, the recordcount is now correct.

If gloNumber_n <> 0 Then
  Application.Echo False
  Call MS_StudieExtra_KiesDataset(2) 'select only unarchived data in the form
  Set rstA1 = Me.Recordset.Clone
  rstA1.FindFirst "[gKeyfield] = " & Str(gloNumber_n)
  If Not rstA1.EOF Then Me.Bookmark = rstA1.Bookmark
  Application.Echo True
End If

 

[lameid]

How are you counting the records? Are you manually running the query after the procedure is done???

 

[PeDa]

Aaaaaargh; having just sent of my last message. I went back to the application, and all is now working as intended! Sorry for the Alarums and Excursions ;-((

 

[PeDa]

Upon further investigation it transpires I was barking up quite the wrong tree. I was still looking in Q_ArchiveInspecties (from which the data had just been removed), and not in Q_Inspecties to which they had just been moved. Sorry once again for rushing here without sufficient thinking.