thanks for the help. I went to Allen Brown's page you mentioned and
got that code on there. I have a question though. When I create a
button, it toggles from locked and unlocked upon click, is there away
I can change those names to like Update and Inquiry? Here is the code.
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 " & Now()
End Select
Next
'Set the visual indicators on the form.
On Error Resume Next
frm.cmdLock.Caption = IIf(bLock, "Un&lock", "&Lock")
frm!rctLock.Visible = bLock
Exit_Handler:
Set ctl = Nothing
Exit Function
Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description
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
Hi Ryan
You can set Me.AllowEdits=True (or False) to enable (or disable) the
user's
ability to modify editable controls on the form.
Unfortunately this also affects unbound controls, which you may want the
user to change - maybe to filter records or some other reason.
Allen Browne has a nifty function on his website that locks only the
*bound*
controls.
You can find it here:
http://allenbrowne.com/ser-56.html
On the other question, if a control is bound to a field in your form's
RecordSource, then that record will be updated whenever the form moves to
another record, or the form closes. There is no way to have the update
happen only if you explicitly request it.
There are two ways around this:
One is to use unbound controls and, when the user clicks the Save button,
you copy all the values from the unbound controls to the corresponding
fields in the form's recordsource.
The other way is to use the form's BeforeUpdate event to intercept the
update. You can use a MsgBox to ask whether to save the record or not.
If
the response is "No" then cancel the event by setting Cancel=True, and
undo
the changes to the record (Me.Undo).
The click event of your Save button should set a module-level boolean
variable so that BeforeUpdate does not ask the question.
So, your code will look something like this:
Dim fSaveClicked as Boolean
Private Sub cmdSave_Click()
If Me.Dirty then
fSaveClicked = True
Me.Dirty = False
End If
End Sub
Private Sub Form_BeforeUpdate(Cancel as Integer)
If fSaveClicked Then
fSaveClicked = False
Else
If MsgBox("Do you want to save changes?", vbYesNo) = vbNo Then
Cancel = True
Me.Undo
End If
End If
End Sub
--
Good Luck
Graham Mandeno [Access MVP]
Auckland, New Zealand
I have a form where a user would input data into and logs that data
into many different tables. I would like to have the form default in
read only view where that user can't make any changes but only look at
the data, I'd like to add a button called "UPDATE" where the form text
boxes come available to input data into. Also, currently I have it
where the information entered into the text box automatically goes
into the table creating a new record, I'd rather have it where they
can input the data and when they want to save it to a table they can
click a save button or if they don't want that record they can close
it and nothing will be saved. Now I know the button wizard has the
save feature, but it seems that when I put in test data it's already
saved before me pushing the save button. This way the user can proof
read the information before submitting into a table. the main form is
called frmcomponents, I imagine it would be in properties - data -
allow edits; I can click "NO" for allow edits but I don't know the VBA
code how to do that, since I'm linking to a "UPDATE" button, am I on
the right thought path? Any thoughts? Thanks.
