Form_AfterUpdate event to programmatically enter data in [result] field
Hi
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
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.
TheDatabase.accdb
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 FunctionThis 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.
TheDatabase.accdb
ASKER
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.
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.
< there are 20 fields. >
then, you should have uploaded a sample db with the exact copy of the form and table
then, you should have uploaded a sample db with the exact copy of the form and table
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:
= 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.
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= 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.
ASKER
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.
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.
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.
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.
<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.
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.
ASKER
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.
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.
Aubrey
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.
Aubrey
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you
TheDatabaseRev.accdb