allow edits = no doesn't work - continuous forms

  • Thread starter Thread starter JulieD
  • Start date Start date
J

JulieD

Hi

i'm being driven slowly mad by a continuous form ... i'm using code from
Stephan Leban's site to make the background of the current record a
different colour and most of the time it works. However, in the same form i
want to set disable edits to no - but i can't get it to work. I've set the
form properties (in the properties sheet), i've set it in code in the
form_open event BUT it still doesn't work. Any ideas?

Additionally, i want to be able to add to reference tables where an item
isn't in the drop down list - and normally i have no problem with doing
this. I basically use two methods - a direct add to the table (from Dev
Ashish's code) when the reference table consists of only one field and i
also open a from linked to the reference table and allow the addition of the
new record into the form when there is more than one field, using the
following code

Private Sub CoilInfo_DblClick(Cancel As Integer)
me.coilinfo.value = null
DoCmd.OpenForm "FRM_CoilInfo", , , , acFormAdd
me.coilinfo.requery
End sub

normally, this opens the forms, allows me to add a new record and then
updates the combo box to include the record i've just entered - however, i
just could not get it to work today ... the record is added but the combo
box is not updated. - what am i missing.

Cheers
JulieD
 
Hi

i'm being driven slowly mad by a continuous form ... i'm using code from
Stephan Leban's site to make the background of the current record a
different colour and most of the time it works. However, in the same form i
want to set disable edits to no - but i can't get it to work. I've set the
form properties (in the properties sheet), i've set it in code in the
form_open event BUT it still doesn't work. Any ideas?
Click to expand...


is the query updatable?
Additionally, i want to be able to add to reference tables where an item
isn't in the drop down list - and normally i have no problem with doing
this. I basically use two methods - a direct add to the table (from Dev
Ashish's code) when the reference table consists of only one field and i
also open a from linked to the reference table and allow the addition of the
new record into the form when there is more than one field, using the
following code

Private Sub CoilInfo_DblClick(Cancel As Integer)
me.coilinfo.value = null
DoCmd.OpenForm "FRM_CoilInfo", , , , acFormAdd
me.coilinfo.requery
End sub

normally, this opens the forms, allows me to add a new record and then
updates the combo box to include the record i've just entered - however, i
just could not get it to work today ... the record is added but the combo
box is not updated. - what am i missing.
Click to expand...

it is opening the form and the code runs further to make the update
before you can change something on the open form, so add a ,acDialog
to it that the code stops till you close the form
 
Hi Andi

Andi Mayer said:
is the query updatable?
Click to expand...

it's actually based directly on a table and yes it is updateable.

it is opening the form and the code runs further to make the update
before you can change something on the open form, so add a ,acDialog
to it that the code stops till you close the form
Click to expand...

thanks i'll give that a go at work tomorrow.
 
Hi Andi



it's actually based directly on a table and yes it is updateable.
Click to expand...

i don't know Stephans approach, but I don't think it has something to
do with your edit-problem

are the controls editable? (enable and looked properties)
 
Hi Andi

Andi Mayer said:
i don't know Stephans approach, but I don't think it has something to
do with your edit-problem

are the controls editable? (enable and looked properties)
Click to expand...

enabled = true & locked = false - as it is a continuous form i can't change
these properties as they then apply to all records in the form.

basically, what i'm trying to do is making it easy for non-computer literate
guys to use an electronic version of their paper "running sheet" - this
paper version has columns & rows .. where they enter (among other details)
the start time of a production run. Then they enter the stop time - choose
a reason for the stoppage - and then when they start again, they enter the
new start time on the next line, and then the stop time etc ..

so in my form i want to replicate this as far as possible - but to limit the
risks of them changing a record instead of adding a new one, i want the
"completed" rows (ie with a stop time) not to be editable. Then when they
press the START button - a new record is created - which has a yellow
background where they enter the current data. However, when i open the form
(with existing records for a day) with the allow edits property set to no
the records can still be edited.

would appreciate any ideas you may have.
 
basically, what i'm trying to do is making it easy for non-computer literate
guys to use an electronic version of their paper "running sheet" - this
paper version has columns & rows .. where they enter (among other details)
the start time of a production run. Then they enter the stop time - choose
a reason for the stoppage - and then when they start again, they enter the
new start time on the next line, and then the stop time etc ..

so in my form i want to replicate this as far as possible - but to limit the
risks of them changing a record instead of adding a new one, i want the
"completed" rows (ie with a stop time) not to be editable. Then when they
press the START button - a new record is created - which has a yellow
background where they enter the current data. However, when i open the form
(with existing records for a day) with the allow edits property set to no
the records can still be edited.

would appreciate any ideas you may have.
Click to expand...

i see two posibilities:
1. use a subform for input which is editable and the main form not
2. jump out of a field when the record has a stop time or use the
before update event to stop the edit
 
Hi Andi

thanks for your assistance - it seems to be working now (the acdialog fixed
that problem) and i've decided to go for form oncurrent events to "lock" the
cells on completed records.

However, i have another question ... hope you don't mind ... in the same
form i would like to provide a way for the user to edit some fields of
"completed" records. I tried putting a "edit" button on but although when i
stepped through the code to unlock these fields it seemed to run fine - none
of the fields were actually unlocked! ... i'm wondering if it has something
to do with the oncurrent code locking all fields. (Hope this makes sense) -
any ideas?

Cheers
JulieD
 
Hi Julie

If you can still edit a record when the form's AllowEdits property is set to
No, then the record is already dirty. (Presumably you are using Form_Current
to apply this stuff to the current record, so it's important that nothing in
that procedure is dirtying the record as soon as you move there.)

Microsoft (wisely) decided they had better let you finish the edit that is
in progress before they block the edits.

On a related issue, are you using Conditional Formatting here? There are
several bugs related to this. One of them allows a control to get focus even
if its Enabled property is No!

All the best.
 
Hi Allen

(nice to hear from you)
yes, i'm using Stephen Leban's code to highlight the current record - which
uses conditional formatting. However, i've managed to "work-around" the
problem by using the form OnCurrent event to lock fields in completed
records. Now, i'm having the opposite problem, i can't get an edit button
to work!

(This is a continuous form - not my favourite creature) .. the aim is to
have an edit button against a record, which "unlocks" certain fields (not
all) so that the users can correct any input errors. However, when i press
the button the code appears to run okay, but the fields still stay locked -
so i'm thinking that there's a conflict between the OnCurrent code and the
Edit button code. One option i've being toying with is that the edit button
launches another form with only that record and the fields they're allowed
to edit ... once they close that form, the record in this continuous form is
then updated to show the latest changes - do you think that this might be a
way to go?

Would appreciate any advice you can offer.

Cheers
JulieD
 
Hi Andi

thanks for your assistance - it seems to be working now (the acdialog fixed
that problem) and i've decided to go for form oncurrent events to "lock" the
cells on completed records.

However, i have another question ... hope you don't mind ... in the same
form i would like to provide a way for the user to edit some fields of
"completed" records. I tried putting a "edit" button on but although when i
stepped through the code to unlock these fields it seemed to run fine - none
of the fields were actually unlocked! ... i'm wondering if it has something
to do with the oncurrent code locking all fields. (Hope this makes sense) -
any ideas?

Cheers
JulieD
Click to expand...
I think Allen has answerd your question, before you have asked

change to field Values with the Edit-button
 
Not sure what might be causing the problem you describe. It should be
possible to unlock the controls.

As you probably know, I tend to use unbound controls on a form (e.g. for
filtering), and I find it annoying that Access locks these as well if you
set the form's AllowEdits to No. My preference is not to use AllowEdits, but
to set the Locked property of each bound control instead.

Below is the approach I now use. You can call it in Form_Current if you want
to lock the record based on the value of some control, or you can call it in
the Click event of a button if you want to toggle between locked and
unlocked. You call it with:
Call LockBoundControls(Me, True)
if you want to lock the controls, or:
Call LockBoundControls(Me, False)
if you want to unlock them.

If there are controls you *don't* want to lock, add their names as extra
arguments, e.g.:
Call LockBoundControls(Me, True, "Surname", "City")

The code forces a save, and toggles AllowDeletions also. It then visits each
control, and skips any in the exception list, any controls that don't have a
Control Source (such as check boxes in an option group), any that are
unbound or bound to an expression.

If it finds a subform that is not named in the exception list, the code
calls itself recursively, so the subform (and any further levels of
sub-subform) are also handled.

You're very welcome to it, if it achieves what you need.

----------------------code starts------------------------
Public Function LockBoundControls(frm As Form, bLock As Boolean, _
ParamArray avarExceptionList())
On Error GoTo Err_Handler
'Purpose: Lock the bound controls and prevent deletes on the form any
its subforms.
'Arguments frm = the form to be locked
' bLock = True to lock, False to unlock.
' avarExceptionList: Names of the controls NOT to lock
(variant array of strings).
'Usage: Call LockBoundControls(Me. True)
Dim ctl As Control 'Each control on the form
Dim lngI As Long 'Loop controller.
Dim bSkip As Boolean

'Save any edits.
If frm.Dirty Then
frm.Dirty = False
End If
'Block deletions.
frm.AllowDeletions = Not bLock

For Each ctl In frm.Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox, acListBox, acOptionGroup, _
acCheckBox, acOptionButton, acToggleButton
'Lock/unlock these controls if bound to fields.
bSkip = False
For lngI = LBound(avarExceptionList) To
UBound(avarExceptionList)
If avarExceptionList(lngI) = ctl.Name Then
bSkip = True
Exit For
End If
Next
If Not bSkip Then
If HasProperty(ctl, "ControlSource") Then
If Len(ctl.ControlSource) > 0& And Not ctl.ControlSource
Like "=*" Then
If ctl.Locked <> bLock Then
ctl.Locked = bLock
End If
End If
End If
End If

Case acSubform
'Recursive call to handle all subforms.
bSkip = False
For lngI = LBound(avarExceptionList) To
UBound(avarExceptionList)
If avarExceptionList(lngI) = ctl.Name Then
bSkip = True
Exit For
End If
Next
If Not bSkip Then
If Len(Nz(ctl.SourceObject, vbNullString)) > 0& Then
ctl.Form.AllowDeletions = Not bLock
ctl.Form.AllowAdditions = Not bLock
Call LockBoundControls(ctl.Form, bLock)
End If
End If

Case acLabel, acLine, acRectangle, acCommandButton, _
acTabCtl, acPage, acPageBreak, acImage, acObjectFrame
'Do nothing

Case Else
'Includes acBoundObjectFrame, acCustomControl
Debug.Print ctl.Name & " not handled on " & frm.Name & " at " &
Now()
End Select
Next

Exit_Handler:
Set ctl = Nothing
Exit Function

Err_Handler:
Call LogError(Err.Number, Err.Description, "LockBoundControls()")
Resume Exit_Handler
End Function

Public Function HasProperty(obj As Object, strPropName As String) As Boolean
'Purpose: Return true if the object has the property.
Dim vardummy As Variant

On Error Resume Next
vardummy = obj.Properties(strPropName)
HasProperty = (Err.Number = 0)
End Function
----------------------code ends------------------------
 
Hi Allen

thanks for this - i'll wade through it with the db in front of me tomorrow
and see if i can sort it out. -

Cheers
JulieD
 
Back
Top