Why Must It Wait Before SubForm Requery?

[victorpeters]

I have a simple form/sub-form that shows data from two related tables. I have a command button that adds 15 new records to the table that is displayed by the subform. The command button then calls requery on the subform so it will display the fifteen new records. But the subform will only properly refresh if I have my program sleep for at least 3 seconds before executing the requery. My code is shown below. In this format it works, but if you remove the "Sleep 3000" then the subform does not refresh.
I did an alternative experiment where I took both the sleep and the requery out of my code. With that version I would click the command button and then press F9 (refresh.) If I pressed F9 immediately after the command button, it woudl not work. But if I waited at least about 3 seconds before pressing F9 it worked!
Any ideas:
1) Why is this waiting necesary?
2) Is there a better solution to this problem than always delaying my code by 3 seconds?


Private Sub AddStudent_Click()

On Error GoTo Err_AddStudent_Click

Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset

Set cn = New ADODB.Connection
cn.ConnectionString = CurrentProject.Connection
cn.CursorLocation = adUseClient
cn.Open
Set cmd = New ADODB.Command
cmd.ActiveConnection = cn

cn.BeginTrans
Dim QuestionID As Integer
For QuestionID = 1 To 15
cmd.CommandText = "INSERT INTO SurveyResponses (ReggieID, QuestionID, StudentID) VALUES ('" & Me.ReggieID & "', '" & QuestionID & "' , '" & Me.StudentID & "')"
cmd.Execute
Next QuestionID
cn.CommitTrans

Sleep 3000

Me.TEST_SUBFORM.Form.Requery


Thanks in advance for your help!

Victor

 

[emik]

Try Parent_form.refresh (replace Parent_form by the name of the actual form).

Before the refresh set everything to nothing:

set cn = nothing
set cmd = nothing
set rs = nothing