Subform Will Not Refresh When Records Added

[victorpeters]

Hi,
I have a form/sub-form. On the main form the user selects a record, and then clicks a command button which will insert fifteen new records in the related table which is displayed by the sub-form. The user can then edit those fifteen rows in the subform in order to provide additional data.
This all works fine, except I can not get the subform to refresh after the new records are added by the command button. The records are definitely being added to the table. The subform does correctly display the data if the user forces the subform to refresh either by pressing F9 or by going to the next record and then returning to the current record. But I can get the subform to automatically refresh from my code.
I have read a lot of forum posts on this issue and tried many different proposed solutions. I have called ReQuery on the sub-form, and Refresh on the subform. I even tried called Requery on the master form. I even tried calling Repaint on the subform. Nothing works!

I hope one of you can tell me what I'm doing or if there is a bug in Access. Here is the key part of my code from my command button:

'Add the new data to the related table (this works)
cn.BeginTrans
Do While Not rs.EOF
cmd.CommandText = "INSERT INTO [Survey Responses]... cmd.Execute
rs.MoveNext
Loop
cn.CommitTrans

'Alter the subform RecordSource to only display the new records (this works, if it will refresh)
Me.Survey_Responses_subform.Form.RecordSource = "SELECT [Survey Responses].[Schedule ID].....

'Attempting to refresh the subform
Me.Survey_Responses_subform.Form.Requery
Me.Survey_Responses_subform.Form.Refresh

Thanks in advance for your help! Let me know if you need additional information.

Victor

 

[TheAceMan1]

How are ya victorpeters . . .

When you change the [blue]RecordSource[/blue] of a form (or subform) it [blue]automatically requeries![/blue] The fact that your not updating means [blue]something's missing![/blue]
[ol][li]If the subform is linked to the mainform (Link Master/Child properties), you have to insure to append the [blue]linking field[/blue] (usually the foreignkey of the subform) as well as the [blue]primarykey of the subform.[/blue][/li]
[li]If the subform is [blue]not linked[/blue] you have to [blue]insure you append the primarykey.[/blue][/li][/ol]
In both cases above, unless the primarykey is autonumber it [blue]has to be incremented somehow[/blue] durring your append process. You can easily tell by looking at the underlying table where you may find orphaned records!

If your system involves an [blue]SQL Server[/blue] their could be other issues in play.

[blue]Your Thoughts! . . .[/blue]

See Ya! . . . . . .

 

[victorpeters]

Hi,
Thanks for the response! Unfortunately, I'm still having the same problem. I have now created a simplified version of my problem in order to narrow down my problem and better communicate it. Here is the new version of my situation which has the same problem:
I have two underlying tables which have a one to many relationship on the primary key/foreign key called "ReggieID". I have a form/subform which display the data of those two tables. The sub-form has "ReggieID" as the "Link Master Field" and the "Link Child Field". Below I have the entire code of my command button. When you click the button it should add fifteen related rows and they should appear in the subform. But, when you click the button, nothing changes on the subform. If you then immediately press F9 after pressing the button, it will show the fifteen new rows! So the rows are being added and the table linking is working. But I can't get my code to refresh the subform without the user presssing F9. Any ideas what the problem is?

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

Me.TEST_SUBFORM.Form.Requery
End Sub

Thanks for your help!

 

[Ed2020]

Just reset the recordsource of your subform and it should requery automatically:

Me.<sfrmName>.Form.RecordSource = "<qryName>"

Ed Metcalfe

Please do not feed the trolls.....

 

[victorpeters]

Hi,
Thanks for the suggestion.

I tried that too, but it still never refreshed. In my original example, my code changed the recordsource. In this simplified version I don't change the recordsource, but I manually call requery. Both ways, it won't refresh the display until the user presses F9.
Any other ideas?

Thanks,

Victor

 

[victorpeters]

I found a solution! But its not a good solution. Can anyone explain why this is happening?

I noticed that if I press F9 after I ran my command button it usually would refresh the subform. But I had to wait a couple second before pressing F9, or else it wouldn't work. So I added one line of code to the above example, between the commit and the requery lines:

Sleep 3000

So I tell my program to go to sleep for 3 seconds before it does the requery and now it works every time. If I only have it sleep for 1 second, it never works.

Does anyone know why it has to wait? Is there a way around this?

Thanks,

Victor