Form_AfterUpdate event to programmatically enter data in [result] field

I am using Microsoft Office 365 --- Microsoft Access

I have included a simple database with one table and one form.  [tbl Table]  and [frm Table]

[tbl Table] has four fields .....[PrimaryKeyField], [Field 2], [Field 3] and [Result]

These four fields will be visible in [frm Table]. but [Result] will not be accessible to the user.  Instead,
 I wish to call a function in the Form_AfterUpdate event that will use [Field 2] and [Field 3] and arguments. The result of the function will be stored in [Result].

All the code in the form is shown below
Option Compare Database
Option Explicit

Private Sub Form_AfterUpdate()
    [result] = CreateItemDescription([primarykeyfield])
End Sub
Public Function CreateItemDescription(ByVal TheKey As String) As String
    Dim rs_Record As DAO.Recordset
    Set rs_Record = CurrentDb.OpenRecordset("Select * from [tbl Table] where [PrimaryKeyField] = '" & TheKey & "'")
    CreateItemDescription = rs_Record![Field 2] & " : " & rs_Record![Field 3]
    rs_Record.Close: Set rs_Record = Nothing:
End Function

Open in new window

This clearly does not work.  When I try to change data in [Field 2] and / or [Field 3], the form won't let me get out of edit mode.   I have some idea of the problem, but am unsure on how to solve the issue.  I have included the database described here as an attachment.

I am sure that most of you wizards will know the solution in a snap. This is what makes this service so invaluable for me.  Looking for great advice...... like usual.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
try this revision..
peispudAuthor Commented:
Thank you for your reply.

In your proposed solution, you have the [Result] field recalculated after Form_bbb_AfterUpdate and Form_ccc_AfterUpdate.  Indeed, this works fine.

In my real database,  there are 20 fields.   That would mean 19 calls to the function in the Form_AfterUpdate events. (One call for each field when entering a new record.)   I can do this, but would prefer just one call to the the function CreateItemDescription just before exiting record edit / creation.
Rey Obrero (Capricorn1)Commented:
<  there are 20 fields.  >

then, you should have uploaded a sample db with the exact copy of the form and table
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Dale FyeOwner, Developing Solutions LLCCommented:
First, why do you want to store the same data twice.  This is generally considered to be poor database design, when you can recreate this concatenated value (if you really need it) anytime you want in a query?

If you insist in pursuing this route, why not just put the [Result] field in the recordsource of your form, but set its visible property to No. Then write a little function:
Public Function fnFillResult()

    me.txt_Result = me.txt_Field1 & " : " & me.txt_Field2 & " : " _
                   & me.txt_Field3 & " : " & me.txt_Field4 & " : " _
                   & me.txt_Field5 & " : " & me.txt_Field6 & " : "
   'and as many more fields as you need

End Function

Open in new window

Then set the AfterUpdate event of each of those controls to:

= fnFillResult()

This would cause that controls value to change as each field value is entered, and when you save the record, it will already contain that value, with no need to open a recordset.
peispudAuthor Commented:
I've requested that this question be deleted for the following reason:

These two experts have been good to me before.   I do not wish to offend them.

I thought that my first question was very clear.  No one asked for clarification.  I gave an example database to illustrate the problem.

I feel that neither of them gave an answer to the same question that I asked.

One was impolite.
Dale FyeOwner, Developing Solutions LLCCommented:
Neither Rey or I was impolite.  If you want an answer to a question, then you need to be specific.  In this case, you only mentioned two fields, but when Rey went to the trouble of modifying your code to give you a solution, you then told him that you had over 20 fields and his solution was not what you wanted to do, even though it was a valid solution.

The problem with using the Form_AfterUpdate event to post a value to the [Result] field is that as soon as you do that, you have now dirtied that record again.  If you use a query for the recordsource of your form, and exclude the [Result] column from that query, then your example code should work, since you are not causing the active record to be dirtied.

However, I restate my original question, why would you want to store a value that concatenates over twenty fields and store that value in the same table, when you could compute that value at any time by calling your function in a query.  Or using the answer I provided.
Rey Obrero (Capricorn1)Commented:
<I thought that my first question was very clear.>

YES  your original post   WAS VERY CLEAR.

<  No one asked for clarification. >

I don't see the need.

it is your responsibility to present your problem properly.
peispudAuthor Commented:
I have a regular job that nothing do with Microsoft Access.   There are many things that I do not understand.   I do the database work for our small business after I get home from work.  I have no formal training in Microsoft Access.

So now, you have answered my question.
The problem with using the Form_AfterUpdate event to post a value to the [Result] field is that as soon as you do that, you have now dirtied that record again.

 It would seem that my request was impossible.    I will not pursue this direction any further.

Thank you both for your time.  I have  received help from you both in the past.   I respect and appreciate both your help.  

Your request was not impossible, the problem was the event you were using.  Although I also believe that you shouldn't store the calculated result, the CORRECT event to do so would be the Form's BeforeUpdate event.  The BeforeUpdate event is the last event to run before the record is saved.

When Dale mentioned that the AfterUpdate event dirties the form again, he didn't explain the implication of that which is - it puts the form into an INFINATE loop.  Save the record, run the after update event which dirties the record which causes the record to be saved and then runs the after update event which dirties the record which causes the record to be saved --- are you dizzy yet.  Earlier versions of Access would go into a hard lock up when people made this mistake.  Newer versions recognize the situation when the stack becomes full and manage to exit gracefully.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
peispudAuthor Commented:
Thank you
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.