Solved

Function that locks particular fields - Access 2003

Posted on 2004-04-28
17
582 Views
Last Modified: 2008-02-01
Hi,

I don't have any knowledge of VBA & would appreciate it if someone could help me out with some code that does the following:

Objective:  To protect test results entered & saved via a form from alteration after initial entry... but would like the ability to unlock the field if required.

An associate suggested the following approach:
Have a function that locks required fields.  When I save, I would call this function.  On the form_OnCurrent event, I would also call this function, so that as you page through each record, the fields remain locked.  Also need to have a function that unlocks the fields, if the user is required to edit.

 
0
Comment
Question by:hazzard_2000
  • 9
  • 7
17 Comments
 
LVL 35

Expert Comment

by:[ fanpages ]
Comment Utility
Hi hazzard_2000,

What type of control are you using for your "fields" on the form?

Text Box, Combo-Box,...?

BFN,

fp.
0
 

Author Comment

by:hazzard_2000
Comment Utility
Text box & a drop-down box

Basically it's a database which covers a test plan... so the drop-down would have pass or fail options & the text box would be a date field.
0
 
LVL 27

Expert Comment

by:jjafferr
Comment Utility
Hi hazzard_2000,

Private Sub Form_Current()
 AllowEdits = false
End Sub

Then make a command button, call it command1, the code behind this button should be:

Private Sub Command1_Click()
 AllowEdits = false
End Sub

Hope this helps

Jaffer
0
 
LVL 27

Expert Comment

by:jjafferr
Comment Utility
Sorry, didn't see it was for paticular fields.
your code should be like this:

Private Sub Form_Current()
 FieldName1.AllowEdits = false
 FieldName2.AllowEdits = false
 ListBoxName1.AllowEdits = false
End Sub

Then make a command button, call it command1, the code behind this button should be:

Private Sub Command1_Click()
 FieldName1.AllowEdits = true
 FieldName2.AllowEdits = true
 ListBoxName1.AllowEdits = true
End Sub

Hope this helps

Jaffer
0
 

Author Comment

by:hazzard_2000
Comment Utility
Thanks Jaffer, I'll try it & get back to you later :)
0
 
LVL 27

Expert Comment

by:jjafferr
Comment Utility
ONE LAST SORRY,

.AllowEdits applies to Forms,
.Locked applies to fields

your code should be like this:

Private Sub Form_Current()
 FieldName1.Locked = True
 FieldName2.Locked = True
 ListBoxName1.Locked = True
End Sub

Then make a command button, call it command1, the code behind this button should be:

Private Sub Command1_Click()
 FieldName1.Locked = false
 FieldName2.Locked = false
 ListBoxName1.Locked = false
End Sub

Sorry

Jaffer


0
 

Author Comment

by:hazzard_2000
Comment Utility
Ok using following:
FieldName1 = DateJoined
ComboBox1 = Result

Private Sub Form_Current()
DateJoined:
0
 

Author Comment

by:hazzard_2000
Comment Utility
Disregard above message  @ 9.35.

Ok using following:
//FieldName1 = DateJoined (date)
//ComboBox1 = Result (text)

Private Sub Form_Current()
       DateJoined:    AllowEdits = False
       Results.AllowEdits = False
End Sub

I tried the code as "FieldName1.AllowEdits = False" but had an error on compile "Method or data member not found"

So looking on the net, it was mentioned that sometimes Access has problems between full-stops and bangs? So I changed the line - using a colon instead of a full stop... that compiled without error, however it locked the record as a whole.

Any suggestions?
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:hazzard_2000
Comment Utility
Missed your say on "Locked" before responding...

Debug initiated on running the form:  Run-time error'424' object required.

Highlighted 2nd line"Results.AllowEdits = False". It shows up as a "Combo11" under the object dropdown list off the menu bar in "Design View".

I remember trying "Locked" previously to posting my question & had problems entering data also achieved another debug message:

"Run-time error '2166':
"You can't lock a control while it has unsaved
changes"

Basically this was triggered when I attempted to enter a
new record in a 'form' field. I only want to protect data from modification  after being saved, in effect I locked the field agaist any entry! Maybe this all depends on where I save the code to ie. Current etc

Thanks.

0
 
LVL 27

Accepted Solution

by:
jjafferr earned 500 total points
Comment Utility
hazzard_2000

You are mixing between .Locked and .allowEdits
please use ONLY .Locked

Private Sub Form_Current()
 DateJoined.Locked = True
 Results.Locked = True
 Combo11.Locked = True
End Sub

Command1 button code should be:

Private Sub Command1_Click()
 DateJoined.Locked = false
 Results.Locked = false
 Combo11.Locked = false
End Sub

Add New Record button code should be:

 DoCmd.GoToRecord , , acNewRec
 DateJoined.Locked = false
 Results.Locked = false
 Combo11.Locked = false


What we are doing:
We are locking the fields in all Records, so everytime you move to another Record, On Current Locks the fields,
When you press the button, you are Unlocking the fields,
When you create a New Record, the Records are locked, so we add the Unlock code right after adding a new Record.

I hope it is clear now

jaffer
0
 

Author Comment

by:hazzard_2000
Comment Utility
Jaffer,

I didn't mix .Locked and .allowEdits, I was refering to results using .Locked prior to enlisting your aid... ie "previous to posting"

Incorporated your suggestion with following results:

All fields in saved records are locked, not just the desired "DateJoined" & "Results".
"Unlock" does not unlock the fields.
"Add New Record" does as expected.

Now I'm probably making basic errors, so I've attached the code as is for you to scrutinise & up'ed the anti a 100pts due to this being on-going.

I'm learning as I go, so I appreciate your comments.

hazzard


Option Compare Database

Private Sub Add_New_Record_Click()

On Error GoTo Err_Add_New_Record_Click


    Screen.PreviousControl.SetFocus
    DoCmd.GoToRecord , , acNewRec
    DateJoined.Locked = False
    Results.Locked = False
       

Exit_Add_New_Record_Click:
    Exit Sub

Err_Add_New_Record_Click:
    MsgBox Err.Description
    Resume Exit_Add_New_Record_Click
   
End Sub

Private Sub comSaveRecord_Click()
On Error GoTo Err_comSaveRecord_Click


    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_comSaveRecord_Click:
    Exit Sub

Err_comSaveRecord_Click:
    MsgBox Err.Description
    Resume Exit_comSaveRecord_Click
   
End Sub
Private Sub comGoPrevRecord_Click()
On Error GoTo Err_comGoPrevRecord_Click


    DoCmd.GoToRecord , , acPrevious

Exit_comGoPrevRecord_Click:
    Exit Sub

Err_comGoPrevRecord_Click:
    MsgBox Err.Description
    Resume Exit_comGoPrevRecord_Click
   
End Sub
Private Sub comGoNextRecord_Click()
On Error GoTo Err_comGoNextRecord_Click


    DoCmd.GoToRecord , , acNext

Exit_comGoNextRecord_Click:
    Exit Sub

Err_comGoNextRecord_Click:
    MsgBox Err.Description
    Resume Exit_comGoNextRecord_Click
   
End Sub

Private Sub Form_Current()
    DateJoined.Locked = True
    Results.Locked = True
End Sub




Private Sub Command23_Click()
On Error GoTo Err_Command23_Click


    Screen.PreviousControl.SetFocus
    DoCmd.FindNext

Exit_Command23_Click:
    Exit Sub

Err_Command23_Click:
    MsgBox Err.Description
    Resume Exit_Command23_Click
   
End Sub

Private Sub Unlock_Click()
    DateJoined.Locked = False
    Results.Locked = False
End Sub
0
 
LVL 27

Expert Comment

by:jjafferr
Comment Utility
Hi hazzard
1-
It all looks OK, but I expect your Unlock button:
Lable is Unlock
Name is something else,
So please check the Name of the command button on the Form and fix it in Code,


Option Compare Database

'Add New Record
Private Sub Add_New_Record_Click()
On Error GoTo Err_Add_New_Record_Click

    Screen.PreviousControl.SetFocus
    DoCmd.GoToRecord , , acNewRec
    DateJoined.Locked = False
    Results.Locked = False      

Exit_Add_New_Record_Click:
    Exit Sub
Err_Add_New_Record_Click:
    MsgBox Err.Description
    Resume Exit_Add_New_Record_Click
End Sub

'Save Record
Private Sub comSaveRecord_Click()
On Error GoTo Err_comSaveRecord_Click

    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_comSaveRecord_Click:
    Exit Sub
Err_comSaveRecord_Click:
    MsgBox Err.Description
    Resume Exit_comSaveRecord_Click
End Sub

'Previous Record
Private Sub comGoPrevRecord_Click()
On Error GoTo Err_comGoPrevRecord_Click

    DoCmd.GoToRecord , , acPrevious

Exit_comGoPrevRecord_Click:
    Exit Sub
Err_comGoPrevRecord_Click:
    MsgBox Err.Description
    Resume Exit_comGoPrevRecord_Click
End Sub

'Next Record
Private Sub comGoNextRecord_Click()
On Error GoTo Err_comGoNextRecord_Click

    DoCmd.GoToRecord , , acNext

Exit_comGoNextRecord_Click:
    Exit Sub
Err_comGoNextRecord_Click:
    MsgBox Err.Description
    Resume Exit_comGoNextRecord_Click
End Sub

'On Current
Private Sub Form_Current()
    DateJoined.Locked = True
    Results.Locked = True
End Sub

'Find
Private Sub Command23_Click()
On Error GoTo Err_Command23_Click

    Screen.PreviousControl.SetFocus
    DoCmd.FindNext

Exit_Command23_Click:
    Exit Sub
Err_Command23_Click:
    MsgBox Err.Description
    Resume Exit_Command23_Click
End Sub

'Unlock
Private Sub Unlock_Click()
    DateJoined.Locked = False
    Results.Locked = False
End Sub


2-
>All fields in saved records are locked, not just the desired "DateJoined" & "Results".
if you want to lock all fields, then this means locking the Form, thus .allowEdits can be used.
Please see an example I made for this.
It Allows Edits/modifications, but for New Records, it compares values with the Table.
Look at the syntax, then we can take it further,
http://www.almosawe.ae/jaffer/Inventory.zip

jaffer
0
 

Author Comment

by:hazzard_2000
Comment Utility
Jaffer,

1. Checked command button name = Unlock
Selected ->Properties->Other (Name = Unlock)

Answer - Form ->Properties->Allow Edits - Flagged 'No' - changed to 'Yes'... runs well!

2. The desired effect is to only lock 2 fields, the other fields should be open to modification after saving via the form.


The Problem
1. It appears that although were coding to lock 2 fields... the form is somehow locked after saving or paging through previously saved records.

I'm considering it's something simple that I've done or failed to do (ie. allowing edits under form properties), is there any chance I can send a 47kb zip file containing the db to you?

Thanks
 
0
 

Author Comment

by:hazzard_2000
Comment Utility
Jaffer,

Disregard "The Problem" above, I wrote that while I was playing with the db  & stumbled across the answer without properly editing above.

Thanks for all your help. Is there a way to password protect the Unlock command button or some user level protection?
0
 
LVL 27

Expert Comment

by:jjafferr
Comment Utility
Hey  hazzard
Thank you for the points and the grade.
I assume your problem is solved, Right?
You are welcome to email me your zipped mdb to look into the problem, if still not solved.

How about hiding the button instead of protecting it,
Here you have a code which will hide the Unlock button from all users except Asha and Jaffer
please check and let me know the result.

Private Sub Form_Load()
'++ Show OR Hide a Command button based on CurrentUser()
'Please change as approperiate
    If CurrentUser() = "Asha" Or CurrentUser() = "Jaffer" Then
        Forms![YourFormName]![Unlock].Visible = -1  'Visible
    Else
        Forms![YourFormName]![Unlock].Visible = 0  'Hide
    End If
End Sub

Good luck in your project.

jaffer
0
 

Author Comment

by:hazzard_2000
Comment Utility
Yep all works ;)

That's great, thanks again. If u can recommend a good novice to imtermediary level book on vba, I'd like to know about it.

hazzard_2000
0
 
LVL 27

Expert Comment

by:jjafferr
Comment Utility
Will you believe me if I told you that I never read any books about VB OR Access,
I learned Basic in the mid 80s, then using F1 help in Access, I learned my way through,
Then I came accoss this mighty site, and you know the rest of the story ;o)

place a 20 points question in programming > Languages > VB and ask the same question, I am sure they all will help.

Hey I am glad I was of help.
Keep up the good work.

jaffer
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now