Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

I would like to display the calculated field result from a query in a text box on the form

Posted on 2016-07-16
11
Medium Priority
?
32 Views
Last Modified: 2016-07-18
I have a Training details form which has a control, Training_Date text box and then a combo box Frequency which has a number of set days.  This is calculated in the query and I would like to have the result displayed in the form control Due_Date once the choice from the combo box is chosen.
This will then allow the value to be recorded in the table allowing for reports and queries on training due.  
I have tried a number of methods without any luck as yet.

Cheers
0
Comment
Question by:Michael
  • 5
  • 4
11 Comments
 
LVL 40

Accepted Solution

by:
als315 earned 2000 total points
ID: 41715017
You can do it in after update event for your combo box. Something like:
Me.[Due_Date] = Dateadd("d", [Frequency], Datevalue([Training_Date]))
1
 

Author Comment

by:Michael
ID: 41715064
Many thanks for the reply
I placed your code in the update event for cboDue_Date and it still didn't work, I then put the Control Source for txtDue_Date as =[qryTraining Query]![Due_Date].  I am now displaying #Name? in the text box.

This is the code in the form module.

'Code to display calculated field from Training_Query in form

Private Sub cboFrequency_AfterUpdate()

 Me.[txtDue_Date] = Dateadd("d", [cboFrequency], Datevalue([txtTraining_Date]))
 
End Sub

Cheers
0
 
LVL 40

Expert Comment

by:als315
ID: 41715707
May be you can prepare sample DB with this form and some dummy data in table?
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

Author Comment

by:Michael
ID: 41715709
Hi als315
I have attached the database with some info though please excuse the errors as I am trying to fix them with another expert from this forum as we speak.

When you open the database if you close the error message and double click frmPPS_Training_Register the tabbed form will open and then click on the Training Details tab to see the Due Date text box.

Many thanks for your help.

Cheers
PPS-Employee-Training.accdb
0
 
LVL 40

Expert Comment

by:als315
ID: 41715737
Look at sample. txtDue_Date field should be bounded if you like to store it in table. Change cboFrequency and you will see changes
PPS-Employee-Training-1.accdb
1
 

Author Comment

by:Michael
ID: 41715742
Many thanks als315
The changes work well and update immediately allowing the user to view before saving the record.

Cheers
0
 
LVL 40

Expert Comment

by:als315
ID: 41715779
Michael! You didn't select any my comment as an answer, so it will not be marked as a solution and no points will be assigned to it.
0
 
LVL 40

Expert Comment

by:als315
ID: 41715788
I propose to select comment #a41715737 as an answer
1
 

Author Comment

by:Michael
ID: 41716989
Sorry for the mix up.
I accept #a41715737  as the solution.

Cheers
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question