Update Main from after Data Entry in Subform

[MaryIrl]

Hi all,

I used to do a good bit of access but haven't touched it in a while and am stuck on something I feel I should be able to do.

When I enter a date on a subform, I want the main form to refresh to reflect the new data. When I use requery, the data is updated ok, but the main form returns to the first record. How do I refresh the data and stay on the current form?

(Apologies if this has been covered several times, I went back several pages and couldn't find a similar question.)

Thanks!

 

[RuralGuy]

I guess I would like to know why there is information that is on both the SubForm and the MainForm? Data only exists in one location in a properly normalized Relational Database.

 

[MaryIrl]

Data

I do realise that, but here's my problem.

The database was originally designed to track maintenance records and simply included last date completed and then it calculated the next due date. So the last date completed was simply overwritten each time and new due date generated.

However, now a need has been identified to record the history. So I was planning on doing this by adding a simple subform where the latest date completed is entered. Then I was going to update the existing "last completed" date on the main form from that data.

I know it's not ideal but but the database has been in operation for a number of years and over this time multitudes of reports etc have been created.
My intention in doing it this way was to minimise the amount of report re-building etc, as everything will still operate as it was doing.

Hope you can follow that!

Cheers

 

[RuralGuy]

Requery pulls in a completely new RecordSet and as a result the record pointer is reset to the first record. Refresh will update just the current RecordSet and not move the record pointer. Have you tried that? Otherwise you will need to capture the PrimaryKey of the MainForm and do a FindFirst after the Requery.

 

[MaryIrl]

Form

Think I'm going to change direction and just leave form as it is, but append the date back to a history table instead.

Thanks for input.