Requery subform

[Tom]

I use the function below (&&&&&) to insert 3 fields into
a "history location" table after the date field has
changed.

The date field resides on a form while I the "history
location" table resides on the same form via a SUBFORM.

I am having a bit of a problem with refreshing the
location history information in real-time. When I change
the date, I am prompted with a message to acknowlege the
data dump into the location history table.

However, unless I move to another record (or close/re-
open the current record), I won't see the latest update
to the subform (location history table).


&&&&&&&& PROPERLY WORKING FUNCTION &&&&&&&&&&&&&&

Private Sub DateHistory_AfterUpdate()
On Error GoTo Err_DateHistory_AfterUpdate

strSQL = "INSERT INTO HISTORY (Record_ID,
DateHistory, Location) VALUES ('" & Me!Record_ID.Value
& "', #" & Format(Date) & "#, '" & Me!Location.Value
& "')"

MsgBox strSQL, , "Location history table will be
updated!"

CurrentDb().Execute strSQL, dbFailOnError

End_DateHistory_AfterUpdate:
Exit Sub

Err_DateHistory_AfterUpdate:
MsgBox Err.Description & " (" & Err.Number & ")", _
vbOKOnly + vbCritical
Resume End_DateHistory_AfterUpdate

End Sub

&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&


Here's what I have attempted (unsuccessfully though):
1. Add line DoCmd.RunMacro "XYZ" after the 'CurrentDb()'
line. In the macro, the subform is opened, re-queryed,
then subformm is closed.

or

2. In the subform, requery the subform AfterUpdate

Again, both approaches don't give me the results as
expected... I won't see the new values in the "history
location" subform unless I close the current record or
navigate to another record back/forth.

Does anyone have some pointers as to how I should solve
this so that I can see the "dumped data" immediately
after I clicked OK to insert the records into the history
table.


Thanks in advance,
Tom

 

[Tim Ferguson]

I am having a bit of a problem with refreshing the
location history information in real-time. When I change
the date, I am prompted with a message to acknowlege the
data dump into the location history table.

The "prompt" is simply the MsgBox command, which allows you to check that
the SQL is correct. When you are happy that is the case, then you can
remove it (although the SQL is not quite right yet, but...)

However, unless I move to another record (or close/re-
open the current record), I won't see the latest update
to the subform (location history table).

That is because it is being called from the AfterUpdate event, which
happens when you move off a record and after it has been saved. This is
appropriate, because otherwise the user might update the History table and
then change his mind about saving the original record. It would make sense
to have the History table updated as an invisible side effect of updating
the main record.

If you want people to just write stuff into the history table willy-nilly,
then you may as well just create a form for that purpose. Remember the
advice, "One job, one form".

HTH


Tim F