I have this scenerio that I am trying to work through and find a proper solution:

Main table stores a part # and an index (Primary Key) along with a bunch of other data. I want to store with the part # the current active version of firmware (aka software) associated with that particular part# / index. Part # may be a duplicate, so for this reason the PK is the index, so that each index / part # will have which firmware has been used with it.

I would like the ability to retain a history of firmware versions used for a particular part # / index so that if we need to go back to a previous version of software we will know which ones have been used.

I have been able to create the relationship to show all previous versions of the firmware used for a particular part # / index, but cannot figure out how to only have one 'current' and update the others as 'archived'. I would like to either use a boolean or selectable lookup (ie Current vs. Archived). What I want is that when I select one firmware to be ‘current’ all others related to the particular part # / index are updated to ‘archived’ leaving the other records not related to that part # / index alone. Also note that I am trying to make the update of the firmware status in the table view without having to go through a form.

Main Table
ID (PK)
Part No
Product

Firmware History
FirmwareVerIndex (PK)
PartNo
Firmware
ChangeDate
ChangeName
FirmwareStatus

Firmware Table (lookup table)
FirmwareIndex (PK)
Firmware
FirmwareFileName

FirmwareStatus (lookup table)
ID (PK)
FirmwareStatus

Firmware Status table is redundant when compared against Firmware History table. Both appear to contain the same fields. One of them should "go away."

I'm thinking that you are taking perhaps a too-complex approach. Try this on for size.

In your Firmware History table, the "current" version of ANYTHING is that version with the most recent Change Date (i.e. DMax([ChangeDate],"Firmware History", {put something here to specify part number}) - and you can look up DMax in Access Help to see how that works.

If the above statement is NOT true, then you have to explain your versioning system a bit better before we can offer any better suggestions.

To reset the statuses on older version records, just set the version on the new record and write a query to update the status on all records for that part number and a date less than the maximum date of all records for that part number.

I am trying to make the update of the firmware status in the table view without having to go through a form.

In Access, this is considered a poor design practice. Forms are for data control. Tables are for holding data. Their functions should not be mixed.