Lookup after update

[mtagliaferri]

I have a DB tracking payslip entries which ultimately flags discrepancy with payments.
I am looking at a look up to copy a value from the main form to a subform once the amount has been entered in the subform with possibly a after update (or any other more appropriate solution.
with reference to the picture, when entering the amount on in both 'sfrmPayslipPayments' and 'sfrmPayslipDeductions' I would like to populate the PSR and Tax Year in the subform with the information in the main form from PSR and Tax Year.

How can I achieve this?

 

[theDBguy]

Hi. Is the main form bound to a table? If so, how are the subforms related to it? Have you tried setting the Default Value properties in the subforms to look up the values from the main form?

 

[mtagliaferri]

Hi. Is the main form bound to a table? If so, how are the subforms related to it? Have you tried setting the Default Value properties in the subforms to look up the values from the main form?

Your explanation is very explanatory, however my knowledge in Access is very limited. could you give me more indication on how to achieve this.

I have attached copy of basic DB

 

[theDBguy]

Your explanation is very explanatory, however my knowledge in Access is very limited. could you give me more indication on how to achieve this.

I have attached copy of basic DB

Hi. Since your subforms are linked to the main form through the IDPayslip field and the IDPayslip dictates the TaxYear and PSR, then there's no need to store the same information in your subforms. This information is already displayed on the main form, so there's no need to display them again on the subforms.

 

[mtagliaferri]

That would actually make sense...
moving to the next issue... I need to add a text box which calculates Year To Date for specific entries in the Payment and Deduction section, also I need to run a report to sum specific entries hence why I thought having Tax Year and PSR information in the two tables would have made it easier to run queries

 

[theDBguy]

That would actually make sense...
moving to the next issue... I need to add a text box which calculates Year To Date for specific entries in the Payment and Deduction section, also I need to run a report to sum specific entries hence why I thought having Tax Year and PSR information in the two tables would have made it easier to run queries

Hi. Year to Dates could probably be calculated using a Totals query. For example, if you want to know how much payments have been done this year, it might look something like this:

SELECT LoanID, Sum(Payments) As YTD
FROM TableName
WHERE PaymentDate>=DateSerial(Year(Date()),1,1,)
GROUP BY LoanID

Hope that helps...