Newbie Query Criteria: When ID = Current Record ID

[mactheknife]

Hello Everyone,

I'm just venturing into Access and VBA and have found this forum really helpful on my journey, but the time has come when I actually have to ask you guru's for some help .

I am creating a vehicle maintenance DB, The 2 important forms are:

frm Vehicles: Where I have some basic information about the vehicles and a double list box that lets you navigate records based on vehicle category.

frmVehicleCheck: An Input form for entering service detail records about the vehicles.

Now for my question. On the VehicleCheck form I have it setup so that the current date for the service is entered automatically into a field and recorded.

I have created a Totals query that will find the last service date for each vehicle ID, but how do I then limit this to just the Last service date for the current vehicleID that's selected on my frmVehicles and display it in a control there.

I hope I've explained myself well enough, but please let me know if I've not given enough information, or I could always upload the DB.

Thanks

 

[pr2-eugin]

Hello mactheknife, Welcome to AWF

I am not able to understand the problem completely. Could you probably give an example and explain again?

 

[mactheknife]

Thanks for the response pr2-eugin.

Sure, It's not a brilliant description. Bascially I have a list of records for each vehicle:

vehicleID (PK)
VehicleName
CategoryID
LastServiced
ServiceDue
StatusID

and a list of records for maintenance done on that vehicle:

ExamID (PK)
VehicleID
ExamDate
Examiner...

Each vehicle will have many maintenance/service records, with each record storing the date that the service was done.

Now I have created a totals query that finds the last service date for each vehicle and my output just now is a list

VehicleID LastServiceDate
1 03/01/14
2 02/01/14
3 34/02/13 etc

But I want to be able to have a field on my Vehicles form, that will display the last service date for the current vehicle record thats showing.

I.e. an expression I can put into a textbox that will display only the last service date for the current record.

I've uploaded my DB for clarity.

Thanks for taking a look.

 

[pr2-eugin]

Go to your frmVehicle, and go to the Unbound control, Last Serviced and set this as the Control Source..

=Nz(DMax("[ExamDate]","[tblVehicleCheck Main]","[VehicleID] = " & [VehicleID]),"")

 

[mactheknife]

That's hit the spot perfectly, thanks! I'm not familiar with this fuction so I shall go and have a read up. Is there a way I can store this information in my vehicles table?

Thanks again!

 

[pr2-eugin]

Here is a comprehensive list of functions available in MS Access : http://www.techonthenet.com/access/functions/index.php

Regarding the question about storage, do not. You need this only for display or probably in a Reports, this can be easily calculated when and where required absolutely no need to store this information. Here is an explanation of why it should not be stored ! http://allenbrowne.com/casu-14.html

Happy to be of help ! Good Luck !