Requery subform after exiting popup

[FameAsser]

Hi

I have my main form which is f_main.

On there is a Subform called subfrmFront and that has a source object of the form f_front

A button on f_main opens up a popup. In this popup, the fields I am updating all relate to the same records that are being displayed in the subform. Everything updates OK in the popup (i.e I can see in the table that the updated information is in there), but the subform back on f_main still has the old data in it.

I need to requery that subform to show the new data I just inputted.

If I close f_main and re-open it, the latest data is in there, but surely there is a way to make sure it updates on the close of the popup form.

 

[vbaInet]

Are you talking about changes to the current record on the subform?

 

[FameAsser]

f_front (which is the source object of the subform), opens a recordset to qryLastMatch which just pulls all the previous match information through from t_fixtures

The popup opens a recordset to t_fixtures directly. In the Popup, there's a dropdown which allows me to choose any previous matches based on < Now().

I amend any data I need to in the popup. When I click on "Add Result" cmd button, it updates the data in t_fixtures with whatever is now in all the fields on the popup. t_fixtures at this point is fully up to date. The popup box closes.

So now I am back on f_main with the subform which is still showing the old data. If I close f_main and open it again, the subform shows the data which I updated earlier.

I know there's a way to open with acDialog, but I have tried that and it's still not requerying the subform on the close of the popup

 

[vbaInet]

All I wanted to know was whether you're referring to changes to the current record or a new record.

I think you're talking about changes to the current record so you can use either the Refresh or Requery method of the form. Do it on the subform itself. Try both to see which one suits.

 

[FameAsser]

Ah right yeah...it's making changes to the current record

 

[vbaInet]

Ok, try both of the properties I mentioned.

Forms![COLOR="Blue"]FormName[/COLOR]![COLOR="blue"]SubformControlName[/COLOR].Form.Refresh

Or

Forms![COLOR="Blue"]FormName[/COLOR]![COLOR="blue"]SubformControlName[/COLOR].Form.Requery

 

[FameAsser]

Ok, try both of the properties I mentioned.

Forms![COLOR="Blue"]FormName[/COLOR]![COLOR="blue"]SubformControlName[/COLOR].Form.Refresh

Or

Forms![COLOR="Blue"]FormName[/COLOR]![COLOR="blue"]SubformControlName[/COLOR].Form.Requery

Where would they go? In the sub for closing the Popup?

 

[vbaInet]

Only one of them. In the Close event of the Popup.

 

[FameAsser]

OK - I am in the close event :

Private Sub Form_Close()

Forms!f_main!f_Front.Form.Requery

End Sub

I have tried both requery and refresh I keep getting an error saying :

Microsoft Access can't find the field 'f_front' referred to in your expression

f_main - the Main form I am on which contains SubForm 'subfrmFront'
subfrmFront - the subform on f_main which is tied into f_front
f_front - the form with the recordset which needs to be requeried

 

[vbaInet]

1. In main form's design view click anywhere within the main form (but not the subform) so that the main form gets the focus
2. Click the subform control once, just once and you'll notice a yellow border surrounding it.

That's your subform control, now look in the property sheet for the correct name of the subform control because that's what you need - not the subform name.

 

[FameAsser]

1. In main form's design view click anywhere within the main form (but not the subform) so that the main form gets the focus
2. Click the subform control once, just once and you'll notice a yellow border surrounding it.

That's your subform control, now look in the property sheet for the correct name of the subform control because that's what you need - not the subform name.

Apologies...I feel like it's almost there but eluding me.

I am now using this in the code :

Forms!f_main!subfrmFront.Form.Requery

I tried refresh too and it didn't work either. The Popup form just closes and nothing changes in the subform on the f_Main.

The subformcontrolname is subfrmfront - I have attached an image to prove

 

[vbaInet]

Depends on how you've linked the tables up and which Record Sources you're using. Show me the Record Source of f_main and the subform.

 

[FameAsser]

No problem...here goes :

f_Main has nothing specific happening to it. It's just holding the subforms and the buttons as on f_main image attached.

img f_result popup is the opened popup. When I select the dropdown to be the last match (the dundee game) it populates with all the info as so :

Private Sub cmbMatch_AfterUpdate()

Tools.strPreviousMatch = Me.cmbMatch.Value

Dim rsResult As ADODB.Recordset
Set rsResult = New ADODB.Recordset

rsResult.Open "Select * from t_fixtures where MatchID = " & Tools.strPreviousMatch, CurrentProject.Connection, adOpenKeyset, adLockReadOnly

'If there's already items in the recordset for the above stipulations,
If rsResult.RecordCount > 0 Then

    'populate the fields with the recordset data
    Me.txtResultHome.Value = rsResult.Fields("HomeGoals").Value
    Me.txtResultAway.Value = rsResult.Fields("AwayGoals").Value
    Me.txtResultScorer.Value = rsResult.Fields("FirstScorer").Value
    Me.txtResultTime.Value = rsResult.Fields("GoalTime").Value
    Me.txtResultAttendance.Value = rsResult.Fields("Attendance").Value

Else

    'Otherwise, set all the fields to be blank
    Me.txtResultHome.Value = ""
    Me.txtResultAway.Value = ""
    Me.txtResultScorer.Value = ""
    Me.txtResultTime.Value = ""
    Me.txtResultAttendance.Value = ""

rsResult.Close
Set rsResult = Nothing

End If


End Sub

When I click on "Add Result" it does this :

Private Sub cmdSendResult_Click()

DoCmd.SetWarnings False

DoCmd.RunSQL "Update t_fixtures Set HomeGoals = " & Me.txtResultHome.Value & ", AwayGoals = " & Me.txtResultAway.Value & ", FirstScorer = '" & Me.txtResultScorer.Value & "', GoalTime = '" & Me.txtResultTime.Value & "', Attendance = '" & Me.txtResultAttendance.Value & "' WHERE MatchID = " & Tools.strPreviousMatch

DoCmd.SetWarnings True


DoCmd.Close acForm, "f_result"

End Sub

But then it just goes back to the f_main which hasn't updated.

The subform is populated from a query as so :

Private Sub Form_Load()

Dim rsLastMatch As ADODB.Recordset
Set rsLastMatch = New ADODB.Recordset

rsLastMatch.Open "qryLastMatch", CurrentProject.Connection, adOpenKeyset, adLockReadOnly

If rsLastMatch.Fields("Attendance").Value <> "" Or IsNull(rsLastMatch.Fields("Attendance").Value) = False Then

    Me.lblLastResult.Caption = rsLastMatch.Fields("HomeTeam").Value & " " & rsLastMatch.Fields("HomeGoals").Value & "-" & rsLastMatch.Fields("AwayGoals").Value & " " & rsLastMatch.Fields("AwayTeam").Value
    Me.lblLastDate.Caption = rsLastMatch.Fields("MatchDate").Value
    Me.lblLast1stScorer.Caption = rsLastMatch.Fields("FirstScorer").Value
    Me.lblLastGoalTime.Caption = rsLastMatch.Fields("GoalTime").Value
    Me.lblLastAttendance.Caption = rsLastMatch.Fields("Attendance").Value

End If

'Close & Clear Recordset
rsLastMatch.Close
Set rsLastMatch = Nothing

End Sub

And here is what qryLastMatch is doing :

SELECT TOP 1 qryPreviousFixtures.MatchID, qryPreviousFixtures.MatchDate, qryPreviousFixtures.HomeTeam, qryPreviousFixtures.AwayTeam, [HomeTeam] & " vs " & [AwayTeam] AS Teams, qryPreviousFixtures.HomeGoals, qryPreviousFixtures.AwayGoals, qryPreviousFixtures.FirstScorer, qryPreviousFixtures.GoalTime, qryPreviousFixtures.Attendance
FROM qryPreviousFixtures;

and in turn...qrypreviousfixtures :

SELECT t_fixtures.MatchID, [HomeTeam] & " vs " & [AwayTeam] AS Teams, *
FROM t_fixtures
WHERE (((t_fixtures.MatchDate)<Now()))
ORDER BY t_fixtures.MatchDate;

So both the Subform and the popup form are pulling information through from the t_fixtures table...but for the recordset on the subform, it's going through a couple of queries on the way through.

Like I said, if I update the data on the popup, then send it so the popup closes, if I close and reopen f_main the details in the subform are perfect...but in essence I shouldn't need to open and close f_main to make it work should I?

 

[vbaInet]

Well you've decided to go down the unbound form way so you have to handle absolutely everything in code. If you update one form, a requery won't cut it so you need to regenerate the recordset and apply it to the subform's controls.

Why are you not binding the form anyway?

 

[FameAsser]

Well you've decided to go down the unbound form way so you have to handle absolutely everything in code. If you update one form, a requery won't cut it so you need to regenerate the recordset and apply it to the subform's controls.

Why are you not binding the form anyway?

I am very new to all this so it's a massive learning curve for me. Is it an easy-ish fix or a bit of a nightmare?

 

[vbaInet]

Binding the form is easier otherwise it will be a very steep learning curve for you.