Memo Field won't refresh after update

[ffrippy]

Hi,
I have a Memo Field, which Users update to provide a running commentary on issues. The journal page is read-only and the comments are added to the table via a seperate comments form. The reason for this is that everyone needs to be able to read the journal but only authorised Users have access to the Comments Form i.e.can add comments. The problem is that after update I can't get the Memo Field to refresh. I've tried various forms / combinations of requery and refresh e.g. [Forms]![FormName]![fieldName].Requery among others but nothing other than closing and reopening the Form appears to work. Are there any other VBA commands which I could try, or any reasons why it might not want to refresh? It's really driving me bonkers....

Many thanks for your help in advance

ffripp

[Seth Schrock]

Instead of trying to refresh/requery the control, try to requery the form that the control is in.

[ffrippy]

Thanks for your reply Seth, as I said I've tried every combination I can think of, [Forms]![FormName]![fieldName].Requery,
[Forms]![FormName].Requery,
Requery,
Me![Fieldname].Requery
Formname.requery plus more..
oh... and all the same combinations again with Refresh....

[twinnyfo]

ffrippy,

You said this field is, in fact, updating, though, because when you close the form and re-open, the field is updated.

How are you accessing the data for this form? Kind of a silly question, I know, but there might be something preventing the record source from refreshing/requerying. I don't know what that might be.....

[ffrippy]

Hi twinnyfo,

I'm accessing the data via a combobox with the recordsource set in VBA with a refresh or and Requery statement after it(I tried it first in the properties box, but that didn't work either.

Many Thanks for your reply

ffripp

        JournalEntryValue = DLookup("[Journal Entry]", "tblACTIVITYJOURNAL", "tblACTIVITYJOURNAL![Activity ID] = Forms!frmACTIVITYDATA![Activity ID]")
       Me![JournalEntry] = JournalEntryValue 

[jforbes]

Nice job Twinnyfo. That's it.

Instead of calling Refresh or Requery, you'll need to run the DLookup again:

        JournalEntryValue = DLookup("[Journal Entry]", "tblACTIVITYJOURNAL", "tblACTIVITYJOURNAL![Activity ID] = Forms!frmACTIVITYDATA![Activity ID]")
        Me![JournalEntry] = JournalEntryValue 

By grabbing the value with a Dlookup, the form's Querying and Binding are bypassed leaving the Me!JournalEntry control Unbound. So it will need to be re-queried manually.

A couple options you can implement if you would rather the Control to be Bound, which would allow you to use Requery to display updated values:

Include tblACTIVITYJOURNAL![Journal Entry] in your Forms RecordSource. This would most likely change your RecordSource to a query which is sometimes undesirable… sometimes.

Create a Form based on tblACTIVITYJOURNAL to show [Journal Entry] and include it on your Main Form as Sub-Form linked on [Activity ID].

These are just options, there is nothing wrong with the way you are doing it.

[ffrippy]

jforbes,

thanks very much, I hadn't spotted that the unbound field wouldn't update on Requery, completely missed it. I solved it by using
Forms!frmactivityjournal!JournalEntry = ExistingAndAppendedText
where 'ExistingAndAppendedText' is the variable I used to store an earlier Dlookup as you suggested.

Many Thanks

ffripp