Problem getting update to show on continuous form (1 Viewer)

[k!t]

Hello

I’ve got a continuous form which lists different types of tasks

This form is built on a query that uses UNIONs to merge tables values with identical column names.

There is a button on the form to update the task status, when this is clicked, some vba runs to figure out which table needs to be updated (because of the UNIONs used I can’t update through control source) and updates the record.

I have a separate control with conditional formatting that I want to change colour when the update is made.

The problem is that the form needs to be re-queried to get the update to show, which scrolls the form to the top – not ideal when dealing with a long task list.
I’ve tried getting the status control to re-query but this doesn’t make a difference.

Is there a way to get the update to show instantly without having to re-query the form (or stop it scrolling up)?

I’ve spent some time trying to rebuild the query using joins instead, but I was finding it tricky and don't want to waste time if there is a simpler solution. If there is no way to get the update to show on the form then I’ll go back to this. The reason I’ve gone for a UNION in the query is outlined in a previous question:

.access-programmers.co.uk/forums/showthread.php?t=280154

Thanks for your patience

 

[Pyro]

Not sure how you are updating your record, but if you run an SQL statement, then requery your control, the conditional formatting should update without requerying the entire dataset and taking you back to the top of your form.

Dim strSQL As String
Dim db As DAO.Database
Set db = CurrentDb()

strSQL = "UPDATE <Your Data> " & _
             "WHERE <Your Criteria> ;"

db.Execute strSQL, dbFailOnError
Me.<Field>.Requery

'Always clean up
Set db = Nothing

 

[k!t]

Thanks for your response Pyro

I'm using SQL to update it already... it's a subform, but wouldn't think that would affect it (I know that the control on the subform would need to be referred to like so: Forms![main form name]![subform control name].Form![control name]).

I'll check over the code again when I get a chance to see if I've made a stupid error somewhere.