I'm working on a clinical database for a hospital. I started out with just a standard table which contains the following
Field Name Data Type MRN Text Name Text DOB Date/Time Start Date Date/Time End Date Date/Time Volume Text Location Text
I created a Query that searches this table. I then created a form with unbound text boxes for each field that the user can input data, and then created a button that would execute the Query. In my Query under the criteria I used the following expression: Like "*" & [Forms]![SearchForm]![MRN] for each searchable field. When the user opens the database, they only see the form, inputs 12345 in the MRN, and clicks search. The query executes and returns any results where the MRN in the table matches 12345.
What I would like to do is give users the option of updating only 1 field, the location. Once search results are returned the MRN, Name, DOB, Start/End, and Volume are all read only. I created another table called LocationUpdate which contains a autonumber field with 4 locations. I included that on my query. So now the user sees an additional column on the Query results called Transfer Location that has a dropdown box with 4 locations. I want them to be able to select 1 of the 4 locations and have it update the Location for the same record on the master location table. This would prevent users from free texting, or modifying any data they shouldn't.
Hopefully that all makes sense. I'm somewhat experienced with Access, but not enough that I can write VBA expressions to do what I want. I apologize if this is in the wrong form, and greatly appreciate any insite anyone may be able to lend!