Locking forms

[pfm721]

This is an easy concept, but it probably won't have and easy solution. I have
a form with a text box that the user enters a record number to be searched. I
would like to lock all other fields on the form at the start until a record
is found and then unlock them so they can be edited.

Any help is greatly appreciated.

 

[Arvin Meyer [MVP]]

The following will lock all the controls on a form. Note the TAG property is
used to exclude some of them. You can use that, then write another function
called UnlockIt to reverse what LockIt does.

http://www.datastrat.com/Code/LockIt.txt

 

[Ken Sheridan]

I'll assume the search text box is called txtSearch. Firstly in the form's
Current event procedure put:

Dim ctrl As Control

On Error Resume Next
If Me.NewRecord Then
For Each ctrl In Me.Controls
ctrl.Locked = False
Next ctrl
Else
For Each ctrl In Me.Controls
ctrl.Locked = (ctrl.Name <> "txtSearch")
Next ctrl
End If

This locks all controls other than txtSearch when the form moves to an
existing record, but unlocks them if it moves to a new record so that data
for a new record can be entered.

In the AfterUpdate event procedure of the search text box put the following
code:

Dim rst As Object
Dim ctrl As Control

Set rst = Me.Recordset.Clone

With rst
.FindFirst "[Record Number] = " & txtSearch
If Not .NoMatch Then
Me.Bookmark = .Bookmark
On Error Resume Next
For Each ctrl In Me.Controls
If ctrl.Name <> Me.ActiveControl.Name Then
ctrl.Locked = False
End If
Next ctrl
End If
End With

This navigates to the first record with the value entered in the txtSearch
control in the RecordNumber column, if it can be found, and unlocks the
controls. I've assumed that RecordNumber is a number data type, but if it’s
a text data type use:

.FindFirst "[Record Number] = """ & txtSearch & """"

However, rather than using a text box for searching why not use a combo box
with a RowSource of:

SELECT [Record Number] FROM [YourTableName] ORDER BY [Record Number];

Set its AutoExpand and LimitToList properties to True (Yes). A user can
then either scroll down the list, or type in the value, in which case the
first match in the list would be selected as each character is typed in. The
above code will work equally well with a combo box.

If the Record Number column (presumably the primary key) contains arbitrary
numeric values, then you might prefer to hide this column and show a more
meaningful column or columns in the combo box. The following describes how
this is done, using an employees table as an example:

RowSource: SELECT EmployeeID, FirstName & " " & LastName AS FullName
FROM tblEmployees ORDER BY LastName, FirstName;

BoundColum: 1
ColumnCount: 2
ColumnWidths 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

Once again the code to execute the search and lock/unlock the other controls
would be the same.

Ken Sheridan
Stafford, England

 

[pfm721]

the text search function is that way becuase it can search multiple ways,
Either entire 9 digit SSN, last initial+last4 of SSN, last name only. I
orginally used a combo, but my users were entering data on the wrong records
because they were selecting them incorrectly out of the text box. In this
manner I have eliminated much of that problem.

I'll assume the search text box is called txtSearch. Firstly in the form's
Current event procedure put:

Dim ctrl As Control

On Error Resume Next
If Me.NewRecord Then
For Each ctrl In Me.Controls
ctrl.Locked = False
Next ctrl
Else
For Each ctrl In Me.Controls
ctrl.Locked = (ctrl.Name <> "txtSearch")
Next ctrl
End If

This locks all controls other than txtSearch when the form moves to an
existing record, but unlocks them if it moves to a new record so that data
for a new record can be entered.

In the AfterUpdate event procedure of the search text box put the following
code:

Dim rst As Object
Dim ctrl As Control

Set rst = Me.Recordset.Clone

With rst
.FindFirst "[Record Number] = " & txtSearch
If Not .NoMatch Then
Me.Bookmark = .Bookmark
On Error Resume Next
For Each ctrl In Me.Controls
If ctrl.Name <> Me.ActiveControl.Name Then
ctrl.Locked = False
End If
Next ctrl
End If
End With

This navigates to the first record with the value entered in the txtSearch
control in the RecordNumber column, if it can be found, and unlocks the
controls. I've assumed that RecordNumber is a number data type, but if it’s
a text data type use:

.FindFirst "[Record Number] = """ & txtSearch & """"

However, rather than using a text box for searching why not use a combo box
with a RowSource of:

SELECT [Record Number] FROM [YourTableName] ORDER BY [Record Number];

Set its AutoExpand and LimitToList properties to True (Yes). A user can
then either scroll down the list, or type in the value, in which case the
first match in the list would be selected as each character is typed in. The
above code will work equally well with a combo box.

If the Record Number column (presumably the primary key) contains arbitrary
numeric values, then you might prefer to hide this column and show a more
meaningful column or columns in the combo box. The following describes how
this is done, using an employees table as an example:

RowSource: SELECT EmployeeID, FirstName & " " & LastName AS FullName
FROM tblEmployees ORDER BY LastName, FirstName;

BoundColum: 1
ColumnCount: 2
ColumnWidths 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

Once again the code to execute the search and lock/unlock the other controls
would be the same.

Ken Sheridan
Stafford, England

This is an easy concept, but it probably won't have and easy solution. I have
a form with a text box that the user enters a record number to be searched. I
would like to lock all other fields on the form at the start until a record
is found and then unlock them so they can be edited.

Any help is greatly appreciated.

 

[Arvin Meyer [MVP]]

Unless you are using Access as an accounting system and are storing payroll
records, storing data like a SSN (which can be compromised by a malicious
employee) is not a good idea. The most secure strategy is to never store any
sensitive data that you don't absolutely need.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

the text search function is that way becuase it can search multiple ways,
Either entire 9 digit SSN, last initial+last4 of SSN, last name only. I
orginally used a combo, but my users were entering data on the wrong
records
because they were selecting them incorrectly out of the text box. In this
manner I have eliminated much of that problem.

I'll assume the search text box is called txtSearch. Firstly in the
form's
Current event procedure put:

Dim ctrl As Control

On Error Resume Next
If Me.NewRecord Then
For Each ctrl In Me.Controls
ctrl.Locked = False
Next ctrl
Else
For Each ctrl In Me.Controls
ctrl.Locked = (ctrl.Name <> "txtSearch")
Next ctrl
End If

This locks all controls other than txtSearch when the form moves to an
existing record, but unlocks them if it moves to a new record so that
data
for a new record can be entered.

In the AfterUpdate event procedure of the search text box put the
following
code:

Dim rst As Object
Dim ctrl As Control

Set rst = Me.Recordset.Clone

With rst
.FindFirst "[Record Number] = " & txtSearch
If Not .NoMatch Then
Me.Bookmark = .Bookmark
On Error Resume Next
For Each ctrl In Me.Controls
If ctrl.Name <> Me.ActiveControl.Name Then
ctrl.Locked = False
End If
Next ctrl
End If
End With

This navigates to the first record with the value entered in the
txtSearch
control in the RecordNumber column, if it can be found, and unlocks the
controls. I've assumed that RecordNumber is a number data type, but if
it's
a text data type use:

.FindFirst "[Record Number] = """ & txtSearch & """"

However, rather than using a text box for searching why not use a combo
box
with a RowSource of:

SELECT [Record Number] FROM [YourTableName] ORDER BY [Record Number];

Set its AutoExpand and LimitToList properties to True (Yes). A user can
then either scroll down the list, or type in the value, in which case the
first match in the list would be selected as each character is typed in.
The
above code will work equally well with a combo box.

If the Record Number column (presumably the primary key) contains
arbitrary
numeric values, then you might prefer to hide this column and show a more
meaningful column or columns in the combo box. The following describes
how
this is done, using an employees table as an example:

RowSource: SELECT EmployeeID, FirstName & " " & LastName AS FullName
FROM tblEmployees ORDER BY LastName, FirstName;

BoundColum: 1
ColumnCount: 2
ColumnWidths 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the
first
dimension is zero to hide the first column and that the second is at
least as
wide as the combo box.

Once again the code to execute the search and lock/unlock the other
controls
would be the same.

Ken Sheridan
Stafford, England

This is an easy concept, but it probably won't have and easy solution.
I have
a form with a text box that the user enters a record number to be
searched. I
would like to lock all other fields on the form at the start until a
record
is found and then unlock them so they can be edited.

Any help is greatly appreciated.

 

[pfm721]

I work in a hospital. Unfortunately information like SSN's are important for
us. I have made my boss fully aware of the issues of storing this information
in access.

Unless you are using Access as an accounting system and are storing payroll
records, storing data like a SSN (which can be compromised by a malicious
employee) is not a good idea. The most secure strategy is to never store any
sensitive data that you don't absolutely need.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

the text search function is that way becuase it can search multiple ways,
Either entire 9 digit SSN, last initial+last4 of SSN, last name only. I
orginally used a combo, but my users were entering data on the wrong
records
because they were selecting them incorrectly out of the text box. In this
manner I have eliminated much of that problem.

I'll assume the search text box is called txtSearch. Firstly in the
form's
Current event procedure put:

Dim ctrl As Control

On Error Resume Next
If Me.NewRecord Then
For Each ctrl In Me.Controls
ctrl.Locked = False
Next ctrl
Else
For Each ctrl In Me.Controls
ctrl.Locked = (ctrl.Name <> "txtSearch")
Next ctrl
End If

This locks all controls other than txtSearch when the form moves to an
existing record, but unlocks them if it moves to a new record so that
data
for a new record can be entered.

In the AfterUpdate event procedure of the search text box put the
following
code:

Dim rst As Object
Dim ctrl As Control

Set rst = Me.Recordset.Clone

With rst
.FindFirst "[Record Number] = " & txtSearch
If Not .NoMatch Then
Me.Bookmark = .Bookmark
On Error Resume Next
For Each ctrl In Me.Controls
If ctrl.Name <> Me.ActiveControl.Name Then
ctrl.Locked = False
End If
Next ctrl
End If
End With

This navigates to the first record with the value entered in the
txtSearch
control in the RecordNumber column, if it can be found, and unlocks the
controls. I've assumed that RecordNumber is a number data type, but if
it's
a text data type use:

.FindFirst "[Record Number] = """ & txtSearch & """"

However, rather than using a text box for searching why not use a combo
box
with a RowSource of:

SELECT [Record Number] FROM [YourTableName] ORDER BY [Record Number];

Set its AutoExpand and LimitToList properties to True (Yes). A user can
then either scroll down the list, or type in the value, in which case the
first match in the list would be selected as each character is typed in.
The
above code will work equally well with a combo box.

If the Record Number column (presumably the primary key) contains
arbitrary
numeric values, then you might prefer to hide this column and show a more
meaningful column or columns in the combo box. The following describes
how
this is done, using an employees table as an example:

RowSource: SELECT EmployeeID, FirstName & " " & LastName AS FullName
FROM tblEmployees ORDER BY LastName, FirstName;

BoundColum: 1
ColumnCount: 2
ColumnWidths 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the
first
dimension is zero to hide the first column and that the second is at
least as
wide as the combo box.

Once again the code to execute the search and lock/unlock the other
controls
would be the same.

Ken Sheridan
Stafford, England

:

This is an easy concept, but it probably won't have and easy solution.
I have
a form with a text box that the user enters a record number to be
searched. I
would like to lock all other fields on the form at the start until a
record
is found and then unlock them so they can be edited.

Any help is greatly appreciated.

 

[Arvin Meyer [MVP]]

Health insurance companies are for the most part abandoning SSN's as used
IDs. Only government Medicare and Medicaid insurance still use it, and
rumors are that they are looking for other methodology. So the only
remaining usage by a hospital is perhaps in collections. Despite some views
to the contrary, having an SSN doesn't enhance collections, it only
facilitates posting to a credit bureau.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I work in a hospital. Unfortunately information like SSN's are important
for
us. I have made my boss fully aware of the issues of storing this
information
in access.

Unless you are using Access as an accounting system and are storing
payroll
records, storing data like a SSN (which can be compromised by a malicious
employee) is not a good idea. The most secure strategy is to never store
any
sensitive data that you don't absolutely need.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

the text search function is that way becuase it can search multiple
ways,
Either entire 9 digit SSN, last initial+last4 of SSN, last name only. I
orginally used a combo, but my users were entering data on the wrong
records
because they were selecting them incorrectly out of the text box. In
this
manner I have eliminated much of that problem.

:

I'll assume the search text box is called txtSearch. Firstly in the
form's
Current event procedure put:

Dim ctrl As Control

On Error Resume Next
If Me.NewRecord Then
For Each ctrl In Me.Controls
ctrl.Locked = False
Next ctrl
Else
For Each ctrl In Me.Controls
ctrl.Locked = (ctrl.Name <> "txtSearch")
Next ctrl
End If

This locks all controls other than txtSearch when the form moves to an
existing record, but unlocks them if it moves to a new record so that
data
for a new record can be entered.

In the AfterUpdate event procedure of the search text box put the
following
code:

Dim rst As Object
Dim ctrl As Control

Set rst = Me.Recordset.Clone

With rst
.FindFirst "[Record Number] = " & txtSearch
If Not .NoMatch Then
Me.Bookmark = .Bookmark
On Error Resume Next
For Each ctrl In Me.Controls
If ctrl.Name <> Me.ActiveControl.Name Then
ctrl.Locked = False
End If
Next ctrl
End If
End With

This navigates to the first record with the value entered in the
txtSearch
control in the RecordNumber column, if it can be found, and unlocks
the
controls. I've assumed that RecordNumber is a number data type, but
if
it's
a text data type use:

.FindFirst "[Record Number] = """ & txtSearch & """"

However, rather than using a text box for searching why not use a
combo
box
with a RowSource of:

SELECT [Record Number] FROM [YourTableName] ORDER BY [Record Number];

Set its AutoExpand and LimitToList properties to True (Yes). A user
can
then either scroll down the list, or type in the value, in which case
the
first match in the list would be selected as each character is typed
in.
The
above code will work equally well with a combo box.

If the Record Number column (presumably the primary key) contains
arbitrary
numeric values, then you might prefer to hide this column and show a
more
meaningful column or columns in the combo box. The following
describes
how
this is done, using an employees table as an example:

RowSource: SELECT EmployeeID, FirstName & " " & LastName AS
FullName
FROM tblEmployees ORDER BY LastName, FirstName;

BoundColum: 1
ColumnCount: 2
ColumnWidths 0cm;8cm

If your units of measurement are imperial rather than metric Access
will
automatically convert the last one. The important thing is that the
first
dimension is zero to hide the first column and that the second is at
least as
wide as the combo box.

Once again the code to execute the search and lock/unlock the other
controls
would be the same.

Ken Sheridan
Stafford, England

:

This is an easy concept, but it probably won't have and easy
solution.
I have
a form with a text box that the user enters a record number to be
searched. I
would like to lock all other fields on the form at the start until a
record
is found and then unlock them so they can be edited.

Any help is greatly appreciated.

 

[John W. Vinson]

I work in a hospital. Unfortunately information like SSN's are important for
us. I have made my boss fully aware of the issues of storing this information
in access.

Do review the (rather stringent!!) patient privacy/security regulations in
HIPAA. As a database developer, I'd be very very reluctant to store patient
personal data in an Access .mdb file, just because it probably isn't
sufficiently robust in security. And A2007 has no security at all.

You should really consider storing the data in SQL/Server with appropriate
security, and use Access as a frontend.

 

[pfm721]

It is going to be stored in an SQL server. There was actually a post on here
talking about this very issue with regards to the medical profession. I am
apprehensive about storing that information period, even in a SQL server, but
in our setting its unavoidable. The IT security guys have assured me that the
data will be stored securely.

 

[John W. Vinson]

It is going to be stored in an SQL server. There was actually a post on here
talking about this very issue with regards to the medical profession. I am
apprehensive about storing that information period, even in a SQL server, but
in our setting its unavoidable. The IT security guys have assured me that the
data will be stored securely.

Sounds like you're on the right track then! Good luck.