473,320 Members | 2,146 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

How to conditionally Lock data fields in Access forms using VBA

kobamfo
14
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

✓ answered by Seth Schrock

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.

16 18476
twinnyfo
3,653 Expert Mod 2GB
kobamfo,

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 Expert 2GB
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
kobamfo
14
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_Update" 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.Locked = True
Else: Me.testsub.Locked = False
End If]
Oct 19 '12 #4
twinnyfo
3,653 Expert Mod 2GB
Good catch, Seth! I was just thinking about the current record.

kobamfo,
the correct function is

Expand|Select|Wrap|Line Numbers
  1. If Not IsNull(subject_dl.Value) Then....
  2.  
Oct 19 '12 #5
kobamfo
14
Great!
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 Expert 2GB
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
kobamfo
14
twinnyfo, I have reverted to use your correction since that is the right way. I appreciate your help.
Oct 19 '12 #8
NeoPa
32,556 Expert Mod 16PB
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_AfterUpdate() 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
  4.  
  5. Private Sub subject_dl_AfterUpdate()
  6.     Call LockTestsub()
  7. End Sub
  8.  
  9. Private Sub LockTestsub()
  10.     With Me
  11.         .testsub.Locked = (Not IsNull(.subject_dl))
  12.     End With
  13. End Sub
Oct 19 '12 #9
TheSmileyCoder
2,322 Expert Mod 2GB
Note that you can also call an event from within another event. I sometimes call the Form_Current from within the Form_AfterUpdate event. The best approach will depend on the individual circumstances.
Oct 22 '12 #10
NeoPa
32,556 Expert Mod 16PB
That's certainly true Smiley, but event procedures are designed to be triggered by events. I always advise creating a separate procedure so that it's clear that the code is not only being invoked from the Access event, but also from elsewhere, which can be imprortant in some circumstances, and helps anyone maintaining the code to appreciate better what goes on where and when. As such I make it a rule never to call event procedures from my code, and advise others to do likewise.
Oct 22 '12 #11
twinnyfo
3,653 Expert Mod 2GB
Yes... one can get into endless loops by accidentally calling an event procedure which refers back to itself. Been there... Done that... Good recommendation Neo!
Oct 22 '12 #12
TheSmileyCoder
2,322 Expert Mod 2GB
@NeoPa
I do see your point.

I feel on the other hand that the code I have is appropriately placed in the OnCurrent, and having it directly there is (for me) the logical place, as opposed to creating a separate sub-rutine.

Most of my applications have a difference in functionality for new records and already existing records. I find it simpler to call the OnCurrent event (again) as the record is updated.
Oct 22 '12 #13
NeoPa
32,556 Expert Mod 16PB
As long as the reasoning has been outlined and understood (as it has been) then readers are in a position to make up their own minds. I'm quite happy that the different perspectives have been clearly expressed :-)
Oct 22 '12 #14
kobamfo
14
And my understanding has only improved just by listening in on your discussions. Actually this Post was part of an attempt to prevent storage of redundant data in several tables other than the foreign keys which make the joins happen between tables.
Oct 22 '12 #15
NeoPa
32,556 Expert Mod 16PB
Good to hear. The more you understand the better you can do the job.
Oct 23 '12 #16
TheSmileyCoder
2,322 Expert Mod 2GB
I myself am a self-taught Access developer, bar the occasional 2 day course. Freee an open discussions like this, with experienced developers like NeoPa is what has taken me to the level where I am today.
Oct 23 '12 #17

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

Similar topics

3
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...
1
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...
0
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...
1
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...
1
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...
19
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...
0
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...
1
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 ...
0
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...
2
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...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

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.