Refresh Query Field on form 1

[KLewisBPM]

I have a field on a form that shows a number, the number is a sum of records in a subform. When I fill in the subform I want to see the field on the main form update. However I have to shut the form and reopen it each time, adding a form refresh button does not work. Any help with this would be greatly appreciated.

Kind Regards

Kelley Lewis

 

[MajP]

In the subform you can capture the after update event to do something. It depends how your are getting the value on the main form. I put the following in the subform and my field on the main form was called "Text2"

Private Sub Form_AfterUpdate()
Me.Parent.Text2 = Me.RecordsetClone.RecordCount
Me.Parent.Text2.Requery
End Sub

 

[KLewisBPM]

Hi MajP thanks for the response.the value on the main form is based on a query of the table the sub form updates.

So I fill in the main form data, then the sub form data. I have a query that looks at the sub form data and sums values in a number field, then I have a this field that shows me that total on the main form. It works if you shut the form and reopen it but I want it to show straight away.

I will try your suggestion and get back soon.

Kind Regards

Kelley Lewis

 

[KLewisBPM]

This is the exact code entered into the properties of the subform call "Downtime Cause" the field being "Downtimemins"

Private Sub Form_AfterUpdate()

Me.Parent.Downtimemins = Me.RecordsetClone.RecordCount
Me.Parent.Downtimemins.Requery

End Sub

The main Form is called "Feedback6" and the lookup field is called "Downtimemins" also

When I add or change a record in the subform (Datasheet view) I get a runtime error

Kind Regards

Kelley Lewis

 

[KLewisBPM]

I created a button and assigned a requery macro, however it reloads the form and shows the first record, is there anyway I can just make it just refresh? the above cde didn't work, I dont mind a user having to click a button if it achieves the same result.

Kind Regards

Kelley Lewis

 

[MajP]

should be able to just requery the controls recordsource.

Private Sub Form_AfterUpdate()
Me.Parent.Downtimemins.Requery
End Sub

 

[TheAceMan1]

How are ya KLewisBPM . . .

In the [blue]OnCurrent event of the subform[/blue] try this:

[blue]   Forms![purple][b][i]MainFormName[/i][/b][/purple]!DownTimeMins = Me.RecordsetClone.RecordCount[/blue]

The above considers if your editing (updating or new) [blue]the recordcount won't be updated until the record is saved[/blue] (navigating off the record). Hence the OnCurrent event.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

 

[KLewisBPM]

MajP and TheAceMan1

Thanks for your help with this, i have tried both suggestions above but to no avail.

I created a button to run a requery on the main form this works great however I cant get it to lock the current record so it always reverts back to record 1 on the Main form.

If you I have been to vague please let me know and i will try and post my setup in more detail


Kind Regards

Kelley Lewis

 

[KLewisBPM]

I used this code on a button, it seems to work ok.

With Me
lngID = .[Record_ID]
.Requery
DoEvents
Set rs = .RecordsetClone
rs.FindFirst "[Record_ID] = " & lngID
If rs.NoMatch = False Then
.Bookmark = rs.Bookmark
End If
End With


Kind Regards

Kelley Lewis