473,327 Members | 2,112 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Saving unbound text box in Table for current record

Hi All,

I have an audit form which has a calculating unbound text box which displays a result at the end of the audit. I understand it's not best practice to save these in the table however I need to secure what the result was at the time of the audit.

I have managed to compile the code to place within the event used when I click the save button. The command button does a number of functions so this is just one of them. It saves in the table just fine except it always saves as a new entry. I want it to save in the field of the current entry. Is there any way to do this?

Expand|Select|Wrap|Line Numbers
  1. Dim sql As String
  2. Dim QAResult As String
  3. Dim db as DAO.Database
  4.  
  5. set db = CurrentDb
  6.  
  7. QAResult = Me.txtQAResult.Value
  8.  
  9.  
  10. If Not IsNull(QAResult) Then
  11. sql = "INSERT INTO tblQAAuditRecords2014 ([QAResult]) VALUES ('" & QAResult & "')"
  12.  
  13. CurrentDb.Execute (sql)
  14.  
  15. End If
Nov 13 '14 #1

✓ answered by TheSmileyCoder

I just want to point out that there are certainly cases where storing a calculation IS considered best practice. For example the final result of a invoice. You wouldn't want the invoice total to change if the tax percentage changed one day.

I think the easiest way is to store the value when the record is saved. If you have the field in the same table as your record what could do is simply copy the value as the record is saved, using the forms BeforeUpdate event. Example posted below (Aircode)

Add a textbox, e.g. txtNameOfBoundField, and sets its visibility to False
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel as Integer)
  2.   Me.txtNameOfBoundField=Me.txtQAResult
  3. End Sub

5 10596
TheSmileyCoder
2,322 Expert Mod 2GB
I just want to point out that there are certainly cases where storing a calculation IS considered best practice. For example the final result of a invoice. You wouldn't want the invoice total to change if the tax percentage changed one day.

I think the easiest way is to store the value when the record is saved. If you have the field in the same table as your record what could do is simply copy the value as the record is saved, using the forms BeforeUpdate event. Example posted below (Aircode)

Add a textbox, e.g. txtNameOfBoundField, and sets its visibility to False
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel as Integer)
  2.   Me.txtNameOfBoundField=Me.txtQAResult
  3. End Sub
Nov 13 '14 #2
@TheSmileyCoder
Thanks for the code. I tried exactly what you said however I got an error : "Object doesn't support this property or method."

Is there something else I need to do to make this work?

Appreciate your assistance.
Nov 13 '14 #3
Scrap that - there is no error with the code (there was an error with my typing).

The only thing is that now, although the action runs smoothly, there is nothing saved in that field of the table.
Nov 13 '14 #4
Tried again and seems to be all saving now.

Thanks a million for your help!
Nov 13 '14 #5
TheSmileyCoder
2,322 Expert Mod 2GB
Your welcome :)
Nov 14 '14 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: DBQueen | last post by:
I have a subform which is in Continuous Forms view. I have added a button to the bottom of the page to move to the next record using the button wizard (result: DoCmd.GoToRecord , , acNext). I...
7
by: Aravind | last post by:
Hi folks. I have 2 forms, frmBorrow and frmHistory frmBorrow has an unbound multi-column combo box (cboMember) and 7 unbound text boxes (MemNo, MemName, MemIC, MemType, CourseFaculty, Borrow,...
2
by: Rosy | last post by:
I am attempting to use the following code to print a report based on the current record in the form. Users bring up the record with a parameter box and then can make changes to the sub-form on the...
8
by: Zlatko Matiæ | last post by:
There is a form (single form) and a combobox. I want that current record of the form is adjusted according to selected value in the combobox. Cuurrent record should be the same as the value in the...
2
by: Zlatko Matiæ | last post by:
Hello. I have the following problem with MS Access/PostgreSQL combination: There is a form in Access that has an unbound text box, used for entering a commentary of a batch of records. There is a...
0
by: Pourya99 | last post by:
Hello, How do a extract the value of one field from the currently selected record in a pivot table? I am currently using the following event: <SCRIPT language=vbscript event=SelectionChange...
11
by: jwessner | last post by:
I have a form (Form1) which contains basic Project data and a subform listing the personnel assigned to the Project as a continuous form. Selecting a person on that project and clicking on a command...
4
by: bkberg05 | last post by:
Hi I have a form called Customer whose record source is a table called Customer. The primary key is Customer_ID. On that form, there's a subform called sub_Customer_Contact_Log. It's record...
2
topher23
by: topher23 | last post by:
Disclaimer: I've already come up with a solution for this issue, but I thought I'd post it in the hope that it could help someone else and on the off chance that someone has another solution. I...
3
AccessIdiot
by: AccessIdiot | last post by:
Please see attached screen shot. As you can see I have a main form (DPR_Main) with a subform (DPR_Survey). Within the subform is another subform (DPR_Resources). I am using the following code I...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.