Lock All Records Except New Record

D

Debra Ann

MS Access 2003 - Form contains:

four fields:
DocTitle
DocRev
DocDate
DocNumber
and one command button:
Create New

I want all fields to be locked on the form except for the "Create New"
button. If the "Create New" button is clicked, then I want all fields to be
unlocked.

How would I go about doing this?
 
J

John W. Vinson

MS Access 2003 - Form contains:

four fields:
DocTitle
DocRev
DocDate
DocNumber
and one command button:
Create New

I want all fields to be locked on the form except for the "Create New"
button. If the "Create New" button is clicked, then I want all fields to be
unlocked.

How would I go about doing this?
Click to expand...
Set the Enabled property of all four controls on the form to No in the form's
Current event, and to Yes in the Create New button. I presume that the Create
New should also go to the form's blank "new record":

DoCmd.GoToRecord acForm, Me.Name, acNewRecord
 
G

Gina Whipp

Debra,

This might give you what you need...

Put this On-Current of the form
fncLockUnlockControls Me, True

Put this On_Click of your button
fncLockUnlockControls Me, False


***** Copy in a module window but do not name module the same as the
Function...

Public Function fncLockUnlockControls(frm As Form, LockIt As Boolean)
'Lock or unlock all data-bound controls on form,
'depending on the value of <LockIt>: True = lock; False = unlock.
'Dirk Goldgar 2005

On Error GoTo Err_fncLockUnlockControls
Const conERR_NO_PROPERTY = 438

Dim ctl As Control

For Each ctl In frm.Controls
With ctl
If Left(.ControlSource & "=", 1) <> "=" Then
..Locked = LockIt
..Enabled = True
End If
End With
Skip_Control: 'come here from error if no .ControlSource property
Next ctl

Exit_fncLockUnlockControls:
Exit Function

Err_fncLockUnlockControls:
If Err.Number = conERR_NO_PROPERTY Then
Resume Skip_Control
Else
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume Exit_fncLockUnlockControls
End If

End Function


--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm
 
J

John W. Vinson

MS Access 2003 - Form contains:

four fields:
DocTitle
DocRev
DocDate
DocNumber
and one command button:
Create New

I want all fields to be locked on the form except for the "Create New"
button. If the "Create New" button is clicked, then I want all fields to be
unlocked.

How would I go about doing this?
Click to expand...

duh!

Sorry for the brainfade. Simply set the Allow Additions property of the form
to Yes, Allow Edits to No, and have the Create New button move to the new
record.

It's interesting that you are so sublimely confident in the perfection of your
users that you want to prevent them from correcting any errors once a record
has been entered. <g>
 
B

Banana

John said:
It's interesting that you are so sublimely confident in the perfection of your
users that you want to prevent them from correcting any errors once a record
has been entered. <g>
Click to expand...

Indeed. I'm sure there are lot of good data entry operator but it's
going to be much more painful if one makes a mistake out of 1,000
thousands and had no facility to fix it except to pester the
administrator of the application. <g>

If it were up to me, I'd just set the form's Data Entry to 'Yes', with
the effect that they won't see any records at open but they can enter
new records and if needed, go back and edit only those they entered in
their session (e.g. they only see their own records and not others).
 
D

Debra Ann

I have simplied my request and form fields for the question ... Here is the
reason they cannot make any edits. The numbers are handed out according to
the four unique fields they choose. Once they select the four fields of
information fromthe dropdown menus, they then click a Get Number button which
queries the table behind the scenes and assigns 50001 is the four-field
combination is unique or the maximum number + 1 if the four-field combination
is not unique. Once the number is assigned, they cannot play with the other
fields as they may choose a combination with the number already existing
(hence duplicates).

Therefore, they can create (all fields are open) or view (all fields are
locked) or void a record and get a new number.

The problem is that once they create
 
D

Debra Ann

I tried this. It does not allow edits so it ignores the click on the Add New
button.
 
D

Debra Ann

Yes, I was going to try it after I let John know his ideas would not work but
got side tracked. I have a meeting but will be trying it after that.

Thanks so much Gina.
 
D

Debra Ann

Gina,

I could not get this to work. It kept freezing my form. They needed the
database in a rush so I had to do a "not pretty but doable" solution. I
created two forms that looked identical. One of them had all the fields I
wanted locked except for the create new and close button. The other one
opened as "Add New" so they did not see the already existing numbers where
they could manipulate the information. It surved the purpose but wasn't as
nice as yours was. When I do my next database, I saved what you gave me and
I will figure out why it was not working.

thanks again for all your help.

Debra Ann
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Access Automatically Send a notification email once a record is added 0
Word Lock specific drop downs on a Word Document 0
New record not updating when the form is closed 3
Allen Browne's code to duplicate records 4
Creating a new record in only one table of underlying query 4
Use unbound Check Box on Continuous Form for User Selection 3
end of record 1
Pulling Sequence Number Depending on Other Fields 6

Top