Automatic lookup the primary field in another table

I am building a database for a music education charity which has five bands, about one hundred members and over a hundred musical instruments which we loan to members. With different tutors issuing instruments at different venues, I want to keep track of which members have what instruments, which instruments are in the store cupboard and which ones are elsewhere eg repair/service. I have various locations on a single select field so an instrument can be allocated to a location. Then, if “Existing Member” is selected, another single select field of member’s names comes into play. Each instrument on the [Instrument Stock] table has a unique ID directing to a form and profiling the Instrument ID and a time stamp. To increase compliance with logging instruments into the database, I have made some QR code labels which stick to the instruments and their cases. These open form refilled with the instrument ID and a timestamp. After about two weeks building the system, I became aware that forms cannot update existing records, only create new ones. My workaround is a table of instrument movements each event creating a new record. The [Instrument Stock] table will lookup the instrument’s latest location. I’ve managed to do the roundup MAX (values) trick, moving data between tables. However, I can’t get the linked table lookup functions to work, without having to manually select a record. I want it to do the equivalent of an excel Vlookup, taking the value in my Primary field as the argument to look up in the other table. When choosing fields in the table on the interface, the primary field doesn’t appear. I’m even struggling with putting a lookup in a formula. Any ideas of a workaround?

This topic was solved and automatically closed 15 days after the last reply. New replies are no longer allowed.