Results 1 to 10 of 10
  1. #1
    fainterm is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2013
    Posts
    17

    modifying only certain fields in query results

    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!

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    If the other fields should never be edited by users, then set the textbox properties as Locked Yes, TabStop No.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    fainterm is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2013
    Posts
    17
    I tried locking down the text box properties, but that prevents the user from inputing data in the textbox to execute the Query on doesn't it? I want the form to be wide open so they can type into each text box and call the query, but have table fields locked so they are read only. Again, it wouldn't be all but 1 table field that's locked down, and the last field (location) they can only choose from 1 of 4 locations to change to.
    Last edited by fainterm; 12-11-2013 at 02:55 PM. Reason: additional info

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Don't lock the unbound controls, only the ones that are bound to fields where you don't want them to change data.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    1) If the text boxes are unbound, then inputting anything into them will not affect the records on the database. If only the location button is enabled and bound, then no accidental change can occur. So you could just ignore the entries.

    2) If you have a "search" button, you could use VBA in the onclick event of that button to lock/disable all the fields to prevent the users from THINKING that they can update anything. You could also change the legend on the button to "refresh", so the same button, when clicked, would unlock the fields and clear them for another entry.

    3) You could move the search fields to the header and have the actual fields in the bound detail or in a bound subform.

    4) You could pop up a small form in dialog mode, over a fixed location on the form, that only allows entry of the one item. Entering that selection would update the record, and then proceed to clear anything you wanted cleared to set up for the next data entry.

  6. #6
    fainterm is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2013
    Posts
    17
    Quote Originally Posted by June7 View Post
    Don't lock the unbound controls, only the ones that are bound to fields where you don't want them to change data.
    Sorry...I think I'm missing something. If I lock the fields no matter if they are bound or unbound, that prevents the user from typing in that field to execute the Query. They need full control over the form so that they can run the Query, it's just when the SearchResults from the Query display they should not be able to modify the data there. Is there a way to lock down the fields at the table level?

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The only controls that should be typed in to input filter criteria are the UNBOUND controls.

    Setting any single control as locked should not affect any other control.

    There is no way to lock the table or query against editing unless the query is entirely not editable due to its design (aggregate and UNION and CROSSTAB queries are not editable). This is why users should not see tables and queries and interact only through forms and reports.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    fainterm is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2013
    Posts
    17
    Sorry for the delayed response. I think I've got it about 85% there. I've got all of the fields I don't want edited locked down on the form. So the 2nd part of my question would be, on the 1 field that I want the users to be able to update, I'd like to create a list box with 4 available locations for the user to choose from. Based on the value in the list box selected I'd like to have a "save" button, that would overwrite the previous value in the table for that record.

    Example:
    User searches for John Smith. result finds that there is 1 entry in the table with the name John Smith and the location of his chart is in Medical Records. The user then selects "Physician Records" in the list box and clicks a save button. The next time John Smith is the search option the location displays as "Physician Records".

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    If form is bound to table and listbox is bound to field, then the entry/edit will save directly to record. Record is committed to table when form closes, move to another record, or run code.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    fainterm is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2013
    Posts
    17
    Quote Originally Posted by June7 View Post
    If form is bound to table and listbox is bound to field, then the entry/edit will save directly to record. Record is committed to table when form closes, move to another record, or run code.
    That worked! I was able to use the values from my tblUpdate in the listbox, and then set it to be bound to the location field on the table. Thanks! marking this as solved.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. help! need help modifying a query
    By BigDan in forum Access
    Replies: 16
    Last Post: 05-28-2013, 03:58 PM
  2. Need help modifying query
    By redwagontoy in forum Queries
    Replies: 13
    Last Post: 04-30-2013, 03:25 PM
  3. Replies: 11
    Last Post: 04-22-2013, 04:21 PM
  4. Need help Modifying a query expression
    By leslina76 in forum Queries
    Replies: 3
    Last Post: 04-02-2011, 08:45 AM
  5. Modifying Update Query
    By James Elvin in forum Queries
    Replies: 0
    Last Post: 10-14-2008, 09:07 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums