Locking records

[Guest]

Hello,

We are looking for a way to 'lock' records after they are entered into a
form-- either in code on close of the form or behind a Command Button. We
currently have forms in a series that you get to by hitting a 'Next' Command
Button. This button has code to go back to the 'initial' form (which is
opened at database startup and remains open as the data entry person
progresses thru the series), gather four header fields, and place those
values in the four header fields in the form you advance to. This ensures
that you are entering data for the same patient in the series (see code
below):

Private Sub Form_Current()
Call SetAutoValues(Me)
End Sub

Private Sub Next_Click()
On Error GoTo Err_Next_Click
Call OpenNextForm(Me.Name)
Exit_Next_Click:
Exit Sub
Err_Next_Click:
DoCmd.Close acForm, Me.Name
Resume Exit_Next_Click
End Sub

Sub SetAutoValues(frm As Form)
On Error GoTo SetAutoValues_err
' Set Automatic Values in each form in series
With frm
!studyday = Forms!fScrEligCriteria!studyday
!id = Forms!fScrEligCriteria!id
!ptin = Forms!fScrEligCriteria!ptin
!site = Forms!fScrEligCriteria!site
End With
SetAutoValues_err:
Resume Next
End Sub

Everything works great, BUT we have discovered a potential problem. Since
the 'SetAutoValues' is in the 'On Current:' property of all the forms in the
series (except for the initial 'fScrEligCriteria' form), if you have the
'initial' form open ('fScrEligCriteria'), and then you go back to the main
database window, and open another form (any form) and advance thru
already-existing records using the Record Navigator arrows at the bottom of
the form, the four header fields will all get over-written with the values
that match the values in the four header fields present in the open initial
form (which is set to open on database open so this code will work in the
first place). While this falls out of the normal workflow we have set up for
our data entry people, and SHOULDN'T happen, can we 'lock' records that have
been entered already, so that if they do navigate thru records using the Form
Record Navigators that these header fields cannot get overwritten by
activating the 'SetAutoValues' code? We're just trying to protect
already-existing records from user error, so any suggestions would be most
helpful.

Thank you.

 

[John Nurick]

Hi Pat,

1) I don't think there's a simple way of preventing code running behind
a form from modifying values in the form's controls. Instead, I'd think
in terms of doing one or both of the following:

a) remove, hide or disable the navigation buttons on the form(s) and
only display them when they can safely be used

b) modify the code in the form's Current event procedure so it only
calls SetAutoValues when appropriate. If I understand what you're doing,
it may be be enough to use

If Me.NewRecord Then
Call SetAutoValues(Me)
End If

2) But the process you're undertaking - copying the contents of four
controls from one form into multiple other forms - makes me feel that
the design of your database is wrong, and in particular that you may
have based the structure of your tables on existing paper forms rather
than working out a properly normalised structure.

 

[Guest]

Hi John,

You hit the nail on the head-- I have based these tables/forms on paper
forms that are filled out by the coordinators of a clinical trial, and then
entered into Access, which has forms built (by yours truly) to look exactly
like those paper forms. What are you referring to when you say, '...based
the structure of your tables on existing paper forms rather than working out
a properly normalised structure?'

In all honesty, I just want to help my data entry people stay on the same
patient ID/initials, etc. as they enter information into the series of forms
in the database. I wanted to avoid having records associated with the wrong
(or non-existing) patients because of data entry error. I'm fairly adept at
Access, but there is always room for improvement on my database structure
skills.

Thank you!

 

[John Nurick]

Hi Pat,

One of the fundamental principles of relational databases is that each
piece of information should be stored in one place in the database only.

In your case, you'd have a table for Patients, with fields for things
like PatientID, name, initials, address, etc. Everywhere else in the
database that involves a patient, you just *store* PatientID; whenever
it's necessary to *display* other information about the patient (such as
the Patient's name), you use a query or expression to retrieve it from
the Patients table.

The fact that you asked this question makes me feel that you're not yet
really "adept at Access". Ordinarily I'd encourage you to go ahead and
read up on normalisation and relational database design - but if you're
working in clinical trials there are ethical and regulatory objections
to learning on the job. Your state or country probably has stringent
regulations concerning confidentiality, data integrity and so on, and
you need to know enough about both databases and the regulations to be
certain that your system complies.