473,902 Members | 5,149 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

How to conditionally Lock data fields in Access forms using VBA

14 New Member
Hi Guys,
I am developing a solution for Document Management in my Organization; Now I want a particular field named "testsub" to be Locked once another field named "subject_dl " is not null, otherwise, the field testsub should be unlocked for data entry.
So I had tried a code in the "On_Current " Event of the Form Named "outfrm" Example:

Expand|Select|Wrap|Line Numbers
  1. If Is Not Null(subject_dl.Value) Then
  2.    Me.testsub.Locked = True
  3.        Else: Me.testsub.Locked = False
  4.        End If
But this has'nt helped me at all. Any help please :-(
Oct 19 '12 #1
16 18621
3,653 Recognized Expert Moderator Specialist

You should put that code in the AfterUpdate event of the subject_dl field. This way, every time that field is updated, it checks the value and locks/unlocks the testsub field.
Oct 19 '12 #2
Seth Schrock
2,965 Recognized Expert Specialist
Actually, you need it in both the form's OnCurrent and subject_dl's AfterUpdate events. This will make it so that when you move between records it will check to see if subject_dl is null and act accordingly and when you change subject_dl it will check it. What I often do is make a function in the form's VBA code that has the code you want. I then call the function from both events. This makes it so that if I ever want to make a change to what is being checked, I only have to make the change one place.
Oct 19 '12 #3
14 New Member
Guys, thank you for your quick responses but Seth, could you help me with your function code? It seems to me the function approach is a "neat job". But I have tried your "On_Current " and "After_Upda te" suggestions with problems: Compiler error "Sub or function not defined" with "NotNull" highlighted.
I must admit that my VBA is below average :-)
Here is the Code i used:
[If NotNull(subject _dl.Value) Then
Me.testsub.Lock ed = True
Else: Me.testsub.Lock ed = False
End If]
Oct 19 '12 #4
3,653 Recognized Expert Moderator Specialist
Good catch, Seth! I was just thinking about the current record.

the correct function is

Expand|Select|Wrap|Line Numbers
  1. If Not IsNull(subject_dl.Value) Then....
Oct 19 '12 #5
14 New Member
The Code below did the magic on the After_Update Event of the "subject_dl "
Expand|Select|Wrap|Line Numbers
  1. If IsNull(subject_dl.Value) Then
  2.    Me.testsub.Locked = False
  3.        Else: Me.testsub.Locked = True
  4.        End If
Very Glad this is working just as I want it. Thank you Guys
Oct 19 '12 #6
Seth Schrock
2,965 Recognized Expert Specialist
Please remember to use the <CODE/> button when posting code.

I missed that error in your code. Replace "NotNull" with "Not IsNull"
Oct 19 '12 #7
14 New Member
twinnyfo, I have reverted to use your correction since that is the right way. I appreciate your help.
Oct 19 '12 #8
32,584 Recognized Expert Moderator MVP
As Seth so cleverly indicated, what you really need is the code encapsulated in a separate procedure which is called by both the subject_dl_Afte rUpdate() and the Form_Current() event procedures.

To illustrate this, as you say your coding skills are at an early stage, try :
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.     Call LockTestsub()
  3. End Sub
  5. Private Sub subject_dl_AfterUpdate()
  6.     Call LockTestsub()
  7. End Sub
  9. Private Sub LockTestsub()
  10.     With Me
  11.         .testsub.Locked = (Not IsNull(.subject_dl))
  12.     End With
  13. End Sub
Oct 19 '12 #9
2,322 Recognized Expert Moderator Top Contributor
Note that you can also call an event from within another event. I sometimes call the Form_Current from within the Form_AfterUpdat e event. The best approach will depend on the individual circumstances.
Oct 22 '12 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

by: polytimi8 | last post by:
Hello, I would like to know if it is possible to create a form in Access2000, which would function like a calendar for 8 operating rooms in hospital, showing which hours are those closed for a specific date. For this intersection I would like to be appeared the name of the doctor, the name of the patient and the kind of operation. Furthemore I would like this intersection to be marked in the calendar with a colour, showing that this...
by: Sam Berry | last post by:
I am in need of some guidance on asscessing the same data from multiple forms. I tried using a collection, but everytime that I try to access the collection, I have to use the new word. colStuff ns = new colStuff When I try to access the items in the collection, they are not there (ie the values from a different form). Could somebody point me in the right direction to get this working? Thanks,
by: Qajussi | last post by:
Hi! I am trying to create data entry forms like MS access forms using asp.net. I am talking about a form like MS access forms which let you enter a new record, edit, navigate to previous and next record and display one record at a time. I am accessing the database. Right now it is MS access but will move it to the SQL sever. I am not too sure how to design the web form to accomplish this task. I can do insert, update(edit) and display...
by: qasimkhans | last post by:
hi, i m new in VB 6. i want to print all data which retrieved from database.i.e i have 3 required records now i want to print these records. i am using DataGrid. is there any option in DataGrid for Printing?. if yes so plz tell me with easy example. i will very thankful to you. plz reply me as soon as possible.
by: shwetagupta | last post by:
Hi I am Trying to insert data in M S Access through ASP, but it is neither showing any error nor updating the database. Please tell me ,where i am making the mistake code: <html> <% set conn=Server.CreateObject("ADODB.Connection") conn.open"gupta" set rs=server.createobject("ADODB.recordset")
by: jalmar | last post by:
My question is related to Access forms. I have set up an Access form using 4 different tables~I am pulling 2 different numbers and the name of Trusts out of one table, I am pulling custodian name and acct number from 2nd table, 2 different numbers from Investment manager table and then 3 empty fields (at this time-needs to be populated by form) out of the 4th table. Can you tell me why I am not able to enter data into the 3 empty fields while...
by: paulquinlan100 | last post by:
Hi Could someone point me in the right direction. Im trying to update an Access DB using the code below, it seems to all run correctly and I dont get any error messages, however it doesnt actually add the data to the table. Any ideas where im going wrong. private void button1_Click(object sender, EventArgs e) {
by: dipalipatel | last post by:
Hi, I need help to show Image on MS Access form using image control. i am using Memo field to store my image bytes, Data E.g. order# Number 1001 Sign Memo 45 63 46 61*46 61 46 58*46 58 46 55*46 55 45 52*45 52 45 50*45 50 44 47*44 47 43 44*43 44 43 49*43 49 44 54*44 54 44 59*44 59 45 64*45 64 46 70*46 70 47 74*47 74 49 83*49 83 50 81*50 81 51 77*51 77 51
by: nilanjangm | last post by:
I am using MS Access 2007. I have one of my tables where I store files as "Attachment" datatype against each employee record. Till this point everything is okay. Now, what I want is to display in the form is on selecting an employee (I'm using a combo box to populate all the employees), I want to display all corresponding records from the table on the form. All other details are displayed fine except the "Attachment" record. What I have...
by: astrid702 | last post by:
In MS ACCESS version 2003 I have a form that is based on a table. I have indicated that I want the form to display the table content in the order of the tracking id number (InvoiceNumber)which is an autonumber field. In detail view of my form, I select the upper left corner black box properties to get to: Form - Properties Record Source - Table1 Order By - Table1.InvoiceNumber As the field is an autonum the form opens, nothing is...
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.