How to lock a specific field in a row. Any help will be appreciated. Thanks (1 Viewer)

[bmaccess]

I am trying to lock a specific field in a row. When I do manage to locked the field it locks the whole entire column. Below is a screenshot and some code attached to some of the check boxes so that you can see the variable names I am using.

--------------------------------------------------------------------------------------------------------------------------
Private Sub Returned_Click() '***********************************'

If IssuedLearner.Value = False Then 'This code will set the checkbox of return to empty if no book issued with a message'
MsgBox "No book issued"
Returned.Value = False
Exit Sub
End If

If Returned.Value = True Then
Total_in_Store = Total_in_Store + 1 'When stock increase Issued decrease and vice versa
Issued = Issued - 1
DateReturned.Value = Date ' Setting return date
Else
Total_in_Store = Total_in_Store - 1 'When stock decrease Issued increase and vice versa
Issued = Issued + 1
DateReturned.Value = Null ' Setting return date to empty

End If




End Sub

Private Sub StudentBookIssue_ID_AfterUpdate()

If (StudentBookIssue_ID.Value = True) And (IssuedLearner.Value = False) Then

IssuedLearner.Value = True

Total_in_Store = Total_in_Store - 1 'When stock decrease Issued increase and vice versa
Issued = Issued + 1
DateIssued.Value = Date

End If


If IsNull(StudentBookIssue_ID) Then
IssuedLearner.Value = False
DateIssued.Value = Null
Total_in_Store = Total_in_Store + 1 'When stock decrease Issued increase and vice versa
Issued = Issued - 1

End If

 

[Jon]

Welcome to Access World! We're so happy to have you join us as a member of our community. As the most active Microsoft Access discussion forum on the internet, with posts dating back more than 20 years, we have a wealth of knowledge and experience to share with you.

We're a friendly and helpful community, so don't hesitate to ask any questions you have or share your own experiences with Access. We're here to support you and help you get the most out of this powerful database program.

To get started, we recommend reading the post linked below. It contains important information for all new users of the forum:

https://www.access-programmers.co.uk/forums/threads/new-member-read-me-first.223250/

We hope you have a great time participating in the discussion and learning from other Access enthusiasts. We look forward to having you around!

 

[The_Doc_Man]

You have another post in the Introductions topic so I'm going to move this to a Forms thread with a relay link. That link will expire in 7 days.

 

[isladogs]

Hi Bienyamin (Ben)
You provided some additional info in the email you sent me so I'll add that below to help others

I need to lock or prevent the user from removing or editing the data from the StudentBookIssue_ID field when the is IssuedLearner.Value = True AND Returned.Value = True.
I can set StudentBookIssue_ID.Locked = True but then I locked the whole column and then I cannnot issue a new book again. I need only the one field in a specific row to be locked at when both the check boxes are checked.

In future, please use code tags (</> button on the toolbar) to retain indentation and improve readability

You can do this using conditional formatting (CF) to disable the StudentBookIssue_ID field when those conditions are met.
Unfortunately the CF wizard doesn't allow you to lock controls

In the screenshot below the Student_BookIssue_ID is disabled for the first & third records which has a similar effect for the end user.

 

[Pat Hartman]

@isladogs provided a good solution to your question.

I looked at the code and it doesn't make sense. Each record has a count field which is incremented but the book can only be issued to one student at a time. So, the actual count can go up or down by 1 but what happens for the other 7 copies of abc?

 

[bmaccess]

Hi there. Let me explain a bit how it works. We have a total of a specific book text book in the store. As the same book is issued the number in the store decrease. When a learner returns that specific book the total in the store increase again. I just used letters and not actual books. I will give a detail description how system works for better understanding sometime again. Thanks I will try conditional formatting and see how that works.

 

[bmaccess]

@isladogs provided a good solution to your question.

I looked at the code and it doesn't make sense. Each record has a count field which is incremented but the book can only be issued to one student at a time. So, the actual count can go up or down by 1 but what happens for the other 7 copies of abc?

Hi all. Just tried that conditional formatting out and it works like a charm. I never knew about conditional formatting or had the need for conditional formatting in Access. Thanks again.

 

[isladogs]

You're welcome.
It has limitations but is perfect for this type of situation.

 

[Pat Hartman]

Let me explain a bit how it works. We have a total of a specific book text book in the store. As the same book is issued the number in the store decrease. When a learner returns that specific book the total in the store increase again. I just used letters and not actual books

I could determine that to be your intent. And I could tell that the book names were junk. HOWEVER, If one row is tied to ONE physical copy of a book as it would be when it connects one book to one student, the count would never logically be more than one. This record cannot be used to both tie to a student and reflect the count of books by that title.

You don't need to go into details. If there is some hidden aspect of the application and this is somehow actually working. That's fine. I was just pointing out what looks to be a logic flaw if you are still in the design phase. Just Ignore my comment if it isn't relevant.