Disable fields on Submit

  • Thread starter Thread starter Kayleen Huggart via AccessMonster.com
  • Start date Start date
K

Kayleen Huggart via AccessMonster.com

I would like to be able to disable or lock fields when clicking a submit
button. Any suggestions on how I can do this. I'm fairly new at access
and have been able to manipulate through use of conditional formatting and
wizards, etc., and don't know much VB. Any help I can get would be
appreciated. Thank you.
 
you can loop thru all the controls on your form & disable &/or lock them.
You can also specify certain controls you don't want to effect such as the
close or new buttons by referencing their name. You can use the ControlType
to reference a type of control such as command buttons which doesn't have a
Locked property. So this would disable all the controls except 2 buttons
named cmdNew & cmdClose & would lock all controls unless it's a command
button. You could put this in the OnClick of the submit button.

Dim ctl As Control

For Each ctl In Me
If ctl.Name <> "cmdNew" And ctl.Name <> "cmdClose" Then
ctl.Enabled = False
If ctl.ControlType <> acCommandButton Then
ctl.Locked = True
End If
End If
Next

HTH
Connie
 
Connie, this is great information. Now ... how do I lock only fields of a
specific record? In other words, I don't want to lock all the fields in
the table, only when I submit a specific record. For example... When
the user clicks "Submit" that should mean all of this record's fields have
been complete and the user cannot allow changes to this record from this
point on, but should be able to change other records in the table which
have not been submitted.

Thanks again...
 
Hopefully your users are accessing the data via forms, so you can control
this by your form. You might need to add a new field in the table that
indicates this record has been submitted (for this example I'm using a
yes/no field). You'll also need to add this field to the form & make it's
visible property to No. When a user clicks the submit button you'll need to
write code that will check the yes/no field (see below for example).

Now what I generally do is create 2 subroutines ... 1 called DisableCtls
(the code I previously sent) & 1 called EnableCtls .. copy same code as
DisableCtls only change the False to True & the True to False. Then in the
submit button's OnClick you would call the disable/lock code.

If you have already put the previous code in the OnClick of the submit
button, get rid of it & create the 2 subroutines above. Then put the
following in the OnClick of the submit button.

Note: if you named the control (of the new field) something else, just
replace chkSubmit with the name you're using
********
Me!chkSubmit=True (if you are using a yes/no field)
Call DisableCtls
********

Then if a user selects a record on this form that has already been submitted
you'll need to run the enable/unlock code. If you have a combo box that a
user selects existing records .. put this code in the AfterUpdate event
(after the code that changes the form to the record they just selected).

********
If Me!chkSubmit=True then
Call DisableCtls
Else
Call EnableCtls
End If
********

HTH
Connie
 
You're amazing...thank you for taking so much time out to help.. Appreciate
it!

Kayleen
 
Excellent ideas, Connie.

If I may add some additional thoughts, use the form's
Current event to check if a record has been submitted.

Instead of checking for the names of specific controls to
lock/enable, set those control's Tag property to an
indicator string such as LOCK. Then the code loop would
look like:
For Each ctl In Me
If ctl.Tag = "LOCK" Then ctl.Locked = True
Next ctl

Another thought, if the Submitted field were a Date type
field, then the test for submitted records would only be
slightly differentL
If Not IsNull(Me.Submitted) Then
. . .
but you would gain the additional benefit of recording the
date that the record was submitted, which could also be used
for further analysis.
 
Thank you, Marsh.

If you don't mind can I bounce a couple of questions off you?
1) If you use the form's Current event to check if record has been submitted
.... aren't you possibly checking it more than needed .. for ex if they are
using requery in the form ... the OnCurrent would trigger each time for the
same record, correct?

2) The loop with the Tag property is a great idea, however I use the Tag for
other things in my code & you can't use it for multiple things, right? Or
maybe you could using the Instr / Mid functions ... but I would think that
could get very confusing?


Marshall Barton said:
Excellent ideas, Connie.

If I may add some additional thoughts, use the form's
Current event to check if a record has been submitted.

Instead of checking for the names of specific controls to
lock/enable, set those control's Tag property to an
indicator string such as LOCK. Then the code loop would
look like:
For Each ctl In Me
If ctl.Tag = "LOCK" Then ctl.Locked = True
Next ctl

Another thought, if the Submitted field were a Date type
field, then the test for submitted records would only be
slightly differentL
If Not IsNull(Me.Submitted) Then
. . .
but you would gain the additional benefit of recording the
date that the record was submitted, which could also be used
for further analysis.
--
Marsh
MVP [MS Access]

Hopefully your users are accessing the data via forms, so you can control
this by your form. You might need to add a new field in the table that
indicates this record has been submitted (for this example I'm using a
yes/no field). You'll also need to add this field to the form & make it's
visible property to No. When a user clicks the submit button you'll need to
write code that will check the yes/no field (see below for example).

Now what I generally do is create 2 subroutines ... 1 called DisableCtls
(the code I previously sent) & 1 called EnableCtls .. copy same code as
DisableCtls only change the False to True & the True to False. Then in the
submit button's OnClick you would call the disable/lock code.

If you have already put the previous code in the OnClick of the submit
button, get rid of it & create the 2 subroutines above. Then put the
following in the OnClick of the submit button.

Note: if you named the control (of the new field) something else, just
replace chkSubmit with the name you're using
********
Me!chkSubmit=True (if you are using a yes/no field)
Call DisableCtls
********

Then if a user selects a record on this form that has already been submitted
you'll need to run the enable/unlock code. If you have a combo box that a
user selects existing records .. put this code in the AfterUpdate event
(after the code that changes the form to the record they just selected).

********
If Me!chkSubmit=True then
Call DisableCtls
Else
Call EnableCtls
End If
********
Click to expand...
Click to expand...
 
Sure, bounce away ;-)

Well, yes, a Requery will run the Current event (on the
first record), but, unless the first record was already the
current record, Requery will make the first record current
and the locked procedure should be executed. I don't think
it's worth worrying about, but if it is an unacceptble
performance hit, you could check to see if one of the
controls is already locked and skip the loop.

As for the Tag property, I would rather use InStr than hard
code control names.
For Each ctl In Me
If InStr(ctl.Tag, "LOCK") > 0 Then ctl.Locked = True
Next ctl

Another technique that I have used occasionally is to add a
prefix to the control names (e.g. LOCKcboProduct) so the
loop would look like:

For Each ctl In Me
If Left(ctl.Name, 4) = "LOCK" Then ctl.Locked = True
Next ctl
 
Thanks for letting me bounce away ! ;)

Very informative!!!

Thanks again Marsh !!


Marshall Barton said:
Sure, bounce away ;-)

Well, yes, a Requery will run the Current event (on the
first record), but, unless the first record was already the
current record, Requery will make the first record current
and the locked procedure should be executed. I don't think
it's worth worrying about, but if it is an unacceptble
performance hit, you could check to see if one of the
controls is already locked and skip the loop.

As for the Tag property, I would rather use InStr than hard
code control names.
For Each ctl In Me
If InStr(ctl.Tag, "LOCK") > 0 Then ctl.Locked = True
Next ctl

Another technique that I have used occasionally is to add a
prefix to the control names (e.g. LOCKcboProduct) so the
loop would look like:

For Each ctl In Me
If Left(ctl.Name, 4) = "LOCK" Then ctl.Locked = True
Next ctl
--
Marsh
MVP [MS Access]

If you don't mind can I bounce a couple of questions off you?
1) If you use the form's Current event to check if record has been submitted
... aren't you possibly checking it more than needed .. for ex if they are
using requery in the form ... the OnCurrent would trigger each time for the
same record, correct?

2) The loop with the Tag property is a great idea, however I use the Tag for
other things in my code & you can't use it for multiple things, right? Or
maybe you could using the Instr / Mid functions ... but I would think that
could get very confusing?


"Marshall Barton" wrote need
to in
the that
a
Click to expand...
Click to expand...
 
Back
Top