Versions of Records

[VinArt]

I have two tables, Product and Inspection. Each product record has a list of inspection points for that product. I have versions, so that if the inspection changes, I can create a new checklist. The product number and version comprise the key.

The Inspection records record actual inspections of particular products. When an inspection form is brought up, the user can select a product (via a combo box), and text boxes are populated with the inspection point text from that product record. When the inspection is stored, it stores the product number and version so that I know which version was used during the inspection.

My question: is there an easy way to have the form automatically select the latest version (largest version number, integer, for a given product number) when the user selects the product number? I tried to put a select statement into the version field on the form, but Access didn't like that.

Thank you in advance for your thoughts.

[nico5038]

You can use a DMAX() function to etract the max value of a field in a table.
As you need the max version of an inspection, the InspectionID needs to be "known" and within this the max version needs to be determined.
So we need in the query a WHERE for the Version like:

select * from tblInspection WHERE version = Dmax("Version","tblInspection","InspectionID=" & [InspectionID])
 

Getting the idea?

Nic;o)

[VinArt]

Getting the idea?

Nic;o)

Yes, thank you! I'm off to the races now.