Page 1 of 2 12 LastLast
Results 1 to 15 of 30
  1. #1
    billgyrotech1 is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    May 2019
    Posts
    179

    Make Record Read Only By Value of Field

    Hi,



    Hold down Shift key when opening database.

    When the field 'Status' on my AFRs Form is "Closed" how can the record (AFR Number and parts associated) be "Read Only" (cannot edit fields).

    Thank you for your help in advance. I have not come up with a solution to this yet so please forgive me I am trying to learn.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    in the ON CURRENT event,

    Code:
    sub form_oncurrent()
    me.locked = me.txtStatus = "Closed"
    end sub


    or lock all the fields:
    txtName.locked = me.txtStatus = "Closed"
    txtFines.Locked = me.txtStatus = "Closed"
    etc...

  3. #3
    billgyrotech1 is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    May 2019
    Posts
    179
    Thanks for the reply but I get compile error with your first code.

  4. #4
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    See the attached - I have altered your combo AFN finder slightly as it wasn't playing nice with the form Current event for some reason.

    I have used a loop and the control tag property to set the lock and as a visual aid to the user, slightly change the background colour of the locked fields, I find this assists when someone can't edit something.
    I used two tags as check boxes can't have their background colour set, and used the same tag for the sub form for simplicity.

    I also disabled the unlock button , but you can easily re-enable that if you want to.

    AFR System 11.5.1.zip

    Things I would do - add a serial number finder as per your other thread, and have some record selectors with a record count.
    This would enable you to cycle through a serial numbers history, without needing to remember all the AFR numbers.

    I would also add a filter so the same form could show all open AFRS, again the record count and selectors would make this a useful visual aid.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  5. #5
    billgyrotech1 is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    May 2019
    Posts
    179
    Thank you Minty for the improvement. I agree with changing the back color to show gray when the fields are locked.

    There will be an initial locking requirement for when the Clerk enters the initial data which will be the fields:

    AFR Number
    SO Number
    Received Date
    Clerk
    Customer
    Description
    Part Number
    Serial Number
    Warranty
    Proper ID
    Hidden Damage
    Checked ADs
    Checked SBs
    Old Part Number
    Old Serial Number
    Customer Complaint
    Preliminary Inspection
    and also the Status field which should have the value "Open"

    After this initial entry of data the Clerk will print out the Tear Down Report and those above fields will lock.

    Then a technician will enter the other data as needed and when the repair is completed the Status field should change to "Closed" and all field should at that time be locked.

    I hope this makes sense.

  6. #6
    billgyrotech1 is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    May 2019
    Posts
    179
    Couldn't it be an 'If Status = "Closed" then lock all form fields'?

    I don't know the syntax in Access but just wondering if there is a simpler method.

    Thanks everyone

  7. #7
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Not trying to teach Grandma to suck eggs here - All of these things are possible, however rather than continually adjusting the end goals, take a step back and get your requirements down on paper.
    Move through your process steps (using a pen and paper, not database terms) and then breakdown each stage into what you need.
    It looks like you have done this to a degree, but maybe need to revisit before going any further and having to do a major redesign.

    Somewhat contradictory to what you might think, this will normally mean working out what you need Out of the system, to determine what you need to put In.
    Once you have those requirement clear, the rest becomes the "pretties" , reports (which you have some already) and the then clever forms to get your inputs.

    Back to your question - again this is process driven;

    What determines that the Clerk has completed their task ? Do you pre book AFR's ? Could we use the received date?
    These are business rules, and you know your business much better the me
    We should use that lock the initial fields, then add a secondary lock based on status (Which is already there) and lock all the other fields.

    I'm happy to provide an update or an example for you to work with based on your response.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  8. #8
    billgyrotech1 is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    May 2019
    Posts
    179
    Minty,

    The process will always be having the Clerk enter the initial items that I mentioned in Post#5 then when they are entered the Clerk will print out the "Tear Down Report". Those fields should be locked at that time.

    Then the widget is estimated by a Technician and if needed parts are ordered and the cost of the repair is determined including the parts and labor needed.
    The widget will remain in a holding area awaiting approval.

    When approved the Technician will complete the repair and fill out all of the other necessary fields.
    Final reports are printed out that will go to the customer and the record at that time should be locked including all parts and data fields.

    Thank you and I really do appreciate everything.

    PS The Old Part Number and Old Serial Number fields are for when the widget gets modified so these numbers will have the previous numbers from the adjacent fields. This is for traceability purposes. If you have any other questions please ask me.

  9. #9
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    I should be able to give you a decent steer on this - repair / servicing is what I have been involved in for 25 + years.

    I think in your instance I would have 2 forms, a receiving form that the clark uses and only has the print/print preview for the teardown report.
    You can remove the fields they don't need , this will simplify their form, and allow you to make it more specific to the booking in process.
    I would default it to a new record on opening, but with the ability to bring up a existing record if required.

    I would then have a technical update form, where those initial fields are locked but viewable, and there is the option to replace the part number and serial number if required, this can auto populate the Old Part No and Old Serial number fields.
    This would probably allow you to have the parts required / used on the same form as a sub form, without the need for the second tab, switching tabs is a pain for the user if you don't need to, and also allows instant visibility of what's been quoted for.

    This form by default would only display Open AFR's , again with the ability to search for closed records, but locked if status closed.

    If you need help or want me to maybe knock up a sample I'm, happy to do so, as I have some time on my hands at the moment.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  10. #10
    billgyrotech1 is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    May 2019
    Posts
    179
    That all sounds good but the only thing is there are predetermined AFR Numbers sometimes for new repairs. I personally don't agree with this method and it should be auto generated but this is the way this company has been doing their business for quite some time so I have to deal with not being able to teach an old dog new tricks.

    The reason for this new database is because I built one in Access about 5 years ago and it is having issues. I have to reset it constantly and most likely due to the initial relationships design.

    So by advice from experts on other forums I decided to start over and hopefully will be better this time.

    The reports have been in place so I recreated them similar to the old ones.

    My previous database was split with different front ends for the users. One was read only, one was for the clerk to edit any fields, and the other version had the clerk fields locked for the technicians.

    I would close the records at the end of each business day by a true false field status as long as there was a finished date.

    Thank you very much for your valuable help

  11. #11
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Out of interest, as the AFR is simply a unique number, or at least that's what it looks like, how does a predetermined one get created, and how is it guaranteed to be unique if it's manually generated.

    We have the same situation, sometimes a customer wants a return number up front.
    There is nothing to stop you creating those in the normal way, just leave out the received date, that way you know it was pre-issued, and can be excluded from your day to day processes etc until it's received.

    You can easily add a query and form to list pre-issued AFR's by customer.
    Booking them in would simply need the received date and other missing information completing.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  12. #12
    billgyrotech1 is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    May 2019
    Posts
    179
    They used to have carbon copy forms that have all been used up that have the AFR Numbers. I tried to convince them to let the numbers be auto generated but to no avail. So I have to make sure during the repair and before it's closed out to make sure the AFR Number has not been used yet. The Clerk is mostly careful but duplicates do happen. It actually would be good if the numbers could be manually entered but if the number was used before maybe a popup message stating so.

  13. #13
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    That is quite simple, your current AFR list doesn't have any duplicates. Add a unique index to it.
    If someone adds a duplicate they get an error saying the record can't be added, you could customise that to be more user friendly.

    Once they get used to that, simply silently manoeuvrethe AFR number over into the autonumber field.
    It will take a bit of behind the scenes adjustment. No one will notice, and you have you easily generated unique AFR number. I won't tell anyone...

    The benefits would be quite large and simplify your process as well.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  14. #14
    billgyrotech1 is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    May 2019
    Posts
    179
    I will be back online in an hour thanks Minty.

  15. #15
    billgyrotech1 is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    May 2019
    Posts
    179
    Would it help to attach one of my old front ends?

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Make Record Read-Only After Update
    By didiomm in forum Programming
    Replies: 5
    Last Post: 09-01-2016, 10:19 AM
  2. Macro to make record read-only
    By Sketchin in forum Forms
    Replies: 1
    Last Post: 02-10-2012, 05:57 PM
  3. Button on a Form to make record read only
    By billgyrotech in forum Forms
    Replies: 8
    Last Post: 08-08-2011, 03:28 PM
  4. Best way to make a query read only?
    By Remster in forum Queries
    Replies: 4
    Last Post: 12-17-2010, 04:36 AM
  5. Replies: 2
    Last Post: 10-19-2006, 04:37 PM

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