Locked out of fields in Access 2000

[jpnyc]

I was trying to create a check box so that if checked, the fields would be
locked. Unfortunately, It also locked me out of unchecking the box to edit
prior fields.

NOW I run into a big problem. I am still locked out making corrections to
prior fields even after I deleted the macros, the check box and everything I
was tinkering with this morning.

The code I used for the check box is as follows:

Private Sub chkLock_Click()
If chkLock = True Then
Me.AllowEdits = False
Me.AllowDeletions = False
Else
Me.AllowEdits = True
Me.AllowDeletions = True
End If
End Sub

So I need help in:
1. re-establishing my editing of prior records(currently locked out)
2. creating a check box that I can click and UNCLICK to lock and edit records.

Many Thanks .....HURRY:-(

 

[Beetle]

Open your form in design view, open the properties sheet and
check that the Allow Aditions, Allow Edits, Allow Deletions
properties are all set to Yes.

Rather than locking the whole form, you could lock individual
controls in the After Update event of the check box;

Private Sub chkLock_AfterUpdate ()

With Me
!Control1.Locked = !chkLock
!Control2.Locked = !chkLock
etc.
End With

End Sub

You would want to repeat this code in the Current event of
your form.

 

[jpnyc]

Thanks Sean...

I'm not sure, but I think locking the whole form with a toggle button/check
box would work best because I have nearly 100 fields to enter.

Since I caused myself trouble with a checkbox lock, I think coding for those
100 variables would bring me a bigger headache.

So I think one simple button, one simple code would work best for me to lock
and unlock the form for each record. ANY Clues?

Thanks again.

John Patrick

 

[John Spencer MVP]

You have to lock all the controls individually. That does not mean that you
have to write 100 statements to lock or unlock the controls. The following is
UNTESTED code that should do what you want. It could be turned into generic
code fairly easily that you could call from any control on any form. The code

Private Sub LockCheck_AfterUpdate()
Dim Ctl as Control
Dim tfLock as Boolean: TfLock = Not(me.LockCheck)

For Each ctl in Me.Controls
Select Case Ctl.ControlType
'list the control types you want to lock
Case acCombobox, acListbox, acCheckbox, _
acTextbox, acSubForm, acOptionGroup
If ctl.name <> "LockCheck" then
ctl.locked = tfLock
End If
Case acCommandButton 'treat buttons differently
ctl.enabled = Not(tfLock)
End Select

Next ctl
End Sub


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[John W. Vinson]

So I think one simple button, one simple code would work best for me to lock
and unlock the form for each record.

It would be easier to program.
It would also make it impossible to unlock the form when you clicked it
because the unlock control would be... locked.

Sometimes the simplest way isn't the best; as Einstein said "A theory should
be as simple as possible - *but no simpler*."

 

[Dirk Goldgar]

Thanks Sean...

I'm not sure, but I think locking the whole form with a toggle
button/check
box would work best because I have nearly 100 fields to enter.

Since I caused myself trouble with a checkbox lock, I think coding for
those
100 variables would bring me a bigger headache.

So I think one simple button, one simple code would work best for me to
lock
and unlock the form for each record. ANY Clues?

You have 100 fields on your form? Wow, that's a lot! Does that mean you
have a table with 100 fields in it? What is your form doing?


An alternative to looping through all the controls is to use the GotFocus
event of your check box to temporarily enable edits, and then use the
LostFocus event to disable them again if the check box isn't checked.
Something like this:

'----- start of code -----
Private Sub chkLock_AfterUpdate()

Me.AllowEdits = Not Me.chkLock
Me.AllowDeletions = Me.AllowEdits

End Sub

Private Sub chkLock_GotFocus()

Me.AllowEdits = True

End Sub

Private Sub chkLock_LostFocus()

Me.AllowEdits = Not Me.chkLock

End Sub
'----- end of code -----

 

[jpnyc]

Thank you all for your replys.

Dirk: I do have nearly 100 fields, I'm a medical student and trying to put
together a research project with each participant's medical history and
various exams.

I tried pasting your code in the event procedure... coding sheet under the
Focus option, but could not get the button to work.

John Spencer: after pasting your code, I was locked out again from
unchecking the box. (my initial problem)

maybe my initial code was misleading which may be causing my current
problem. So I'll try to explain myself again.

I have my 100 fields. The last field is a check box titled 'Lock'...I would
like to lock my current info (i.e. record 'J'), return to prior data for
editing if I encounter an error (i.e in record C) just by
removing/un-checking my lock field.

I really don't know what I'm doing with access since I've only used it for a
week.
Maybe I'm clicking in the wrong places or pasting in the wrong forms.

 

[Dirk Goldgar]

Thank you all for your replys.

Dirk: I do have nearly 100 fields, I'm a medical student and trying to put
together a research project with each participant's medical history and
various exams.

Your data structure is probably not the best. However, I won't fuss about
that now.

I tried pasting your code in the event procedure... coding sheet under the
Focus option, but could not get the button to work.

I gave you three different event procedures. You couldn't paste them all
into one event procedure. (Also, do you mean that you tried to paste it
directly into event property lin on the property sheet? That won't work.)
If you don't currently have any code in the check box's AfterUpdate,
GotFocus event, or LostFocus event, you could just open the form's module,
go to the last line of code in the module, and paste the code for all three
procedures after the last line of code.

Note that if you use the code I gave you, you should remove the code you
have for the check box's Click event (the chkLock_Click procedure). I'm
using AfterUpdate instead.

Maybe I'm clicking in the wrong places or pasting in the wrong forms.

Probably. We've assumed you knew more than you do, and haven't given you
sufficiently explicit directions.

 

[jpnyc]

Dirk: THANKS!!...I just realized that I have to move to the next entry for
the lock to take effect. I did create the module initially with all three
procedures but did not see the result b/c i did not close the event.

Good thing for bathroom thinking breaks.lol anyways..have you seen the size
a medical record? each time a patient visits increase the folder by at least
30 sheets of paper. now imagine how many times one person can visit a
hospital in their lifetime.

My form may not be the best, but if you can tell me of a way to select
multiple variables from a combo box/drop down, it may shrink my fields. Say
the person is a smoker, hypertensive, diabetic and high cholesterol. Each
disease is a separate field for my study currently. No two patients are alike
so I have to classify each one differently. later when i run a query, at
times I only want the HTN, HL fields. I may want the diabetics and HTN next.
I could not think of a better way to separate the fields. compound that
with each exam performed on the patient with their 20 variables, and multiple
exams being performed. I end up having this massive table. I know I
could've created linking tables, but that was taking too much time for me.

Another topic yes, but I'll make my due.

Thanks again.
John Patrick

 

[Beetle]

I understand that you might not have time right now to
consider rethinking your table structure, but if you intend
to use this application on a regular/long term basis, you
really should keep it in mind for the near future. It is always
going to be a struggle to get usable data from your
current table design. Here are some research resources.
You should start with the Tutorial by Crystal.

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

 

[John W. Vinson]

My form may not be the best, but if you can tell me of a way to select
multiple variables from a combo box/drop down, it may shrink my fields. Say
the person is a smoker, hypertensive, diabetic and high cholesterol. Each
disease is a separate field for my study currently. No two patients are alike
so I have to classify each one differently. later when i run a query, at
times I only want the HTN, HL fields. I may want the diabetics and HTN next.
I could not think of a better way to separate the fields.

That situation is EXACTLY what Dirk's and Beetle's advice is designed to
solve.

"Fields are expensive, records are cheap". Each Patient may have zero, one, or
many conditions; each condition may affect zero, one or many patients. The
proper structure involves three tables:

Patients
PatientID
LastName
FirstName
DateOfBirth
<other personal biographical data, NOTHING about conditions>

Conditions
ConditionCode
ConditionName
<generic information about this condition>

PatientConditions
PatientID <link to Patients, who has the condition>
ConditionCode <what do they have>
DiagnosisDate
<other information about this condition in this patient, e.g. severity,
etc.>

If Joe Schmoe has diabetes, hypertension, hypercholesterolemia and narcolepsy,
he would have four records in this table. You can very easily search for all
patients with hypertension, or (with only a bit more difficulty) all
hypertensives with narcolepsy who are NOT diabetic. No table has more than a
few fields.

 

[jpnyc]

Hey John V.

When I thought of the db, I did want to separate everything into categories
you have mentioned, but did not know how to link multiple tables together. I
understand where everyone is coming from, but I didn't have much time to
fiddle with access, before I started entering data. Thanks for your reply,
hopefully, I figure out what I wanted to do initially (as everyone is already
stated) before I move to my next project.