How to Get the Last Value of Certain Field
In this How To, I will show how to get the last value of certain field from the last record. Sometime, we need a specific value of the last record. For this example, I need to get the value in Balance field from the last record which is $300.00 per the picture below. The last record is the ID field that has the biggest number which is 144. I will use DLookUp and DMax functions together to get the value $300.00 from Balance field of record ID 144.
Step 1. Add Textbox to Form
- Click on Textbox icon and drag it to form
- Rename text label as Current Balance:
- Open the Property Sheet of textbox
- Click Format tab
- Select Currency for Format
- Click Data tab
- Click three dots(…) at the end of Control Source to open the Expression Builder
Step 2. Add VBA to the Control Source
We will use DLookup function to get the balance where the ID value is max value.
Enter the VBA code below in the Expression Builder box below.
=DLookUp(“balance”,”[transactions Extended]”,”ID = ” & DMax(“ID”,”[transactions Extended]”,””))
- The result of DMax(“ID”,”[transactions Extended] = 144
- The result of DLookUp(“balance”,”[transactions Extended]”,”ID = 144”) = 300
- So $300.00 is the last value in Balance field
After click OK and close the Expression Builder, we will see the Control Source = the DLookup code function above.
Step 3. View the Value in Form
- Save and close the form design view
- The last value of balance ($300.00) will display on the Current Balance box as shown below