• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 647
  • Last Modified:

Function that locks particular fields - Access 2003

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
hazzard_2000
Asked:
hazzard_2000
  • 9
  • 7
1 Solution
 
[ fanpages ]IT Services ConsultantCommented:
Hi hazzard_2000,

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

Text Box, Combo-Box,...?

BFN,

fp.
0
 
hazzard_2000Author Commented:
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
 
jjafferrCommented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
jjafferrCommented:
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
 
hazzard_2000Author Commented:
Thanks Jaffer, I'll try it & get back to you later :)
0
 
jjafferrCommented:
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
 
hazzard_2000Author Commented:
Ok using following:
FieldName1 = DateJoined
ComboBox1 = Result

Private Sub Form_Current()
DateJoined:
0
 
hazzard_2000Author Commented:
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
 
hazzard_2000Author Commented:
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
 
jjafferrCommented:
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
 
hazzard_2000Author Commented:
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
 
jjafferrCommented:
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
 
hazzard_2000Author Commented:
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
 
hazzard_2000Author Commented:
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
 
jjafferrCommented:
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
 
hazzard_2000Author Commented:
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
 
jjafferrCommented:
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 9
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now