Blocking Record

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello

I want to be able to block a customer from having details altered once the
customer reaches a certain status.

I have a Combo box called Status and I want to block a record once it
reaches a status of "HO".

Can any one assist??? (Please)
 
Hello

I want to be able to block a customer from having details altered once the
customer reaches a certain status.

I have a Combo box called Status and I want to block a record once it
reaches a status of "HO".

Can any one assist??? (Please)
Click to expand...

There's no *rock solid* way to do this; but if you enforce Access
security so that your Form is the only method available to the user to
update this table, you could use VBA code in the Form's BeforeUpdate
event:

Private Sub Form_BeforeUpdate(Cancel as Integer)
If Me!Status.OldValue = "HO" Then
MsgBox "This customer's record is blocked", vbOKOnly
Cancel = True ' Don't save the record
Me.Undo ' Erase the user's changes
End If
End Sub

John W. Vinson[MVP]
 
assuming that "HO" is the value in the BoundColumn of the combo box, try the
following on the form's Current event, as

Private Sub Form_Current()

With Me
.AllowEdits = Not (!Status = "HO")
.AllowDeletions = Not (!Status = "HO")
End With

End Sub

the code acts as a toggle switch. if Status does not equal HO, then
AllowEdits and AllowDeletions are set to True, otherwise both properties are
set to False - preventing the current record from being edited or deleted
from within the form.

hth
 
oops! better make that

Private Sub Form_Current()

With Me
.AllowEdits = Not Nz((!Status = "HO"), False)
.AllowDeletions = Not Nz((!Status = "HO"), False)
End With

End Sub

hth
 
Tina

I want to be able to block the whole thing, not just the Status combo box
once the status is "HO"

Can this be done?
 
well, that's what the code does - resets the FORM properties AllowEdits and
AllowDeletions. did you try the code? if so, where are you running it from,
and what error(s) occurred, or what did/didn't happen?
 
Tina

Just to expand.... There is a couple of fields that need to be updated after
the status of HO is achieved.

The fields are:-

1.Date Disbursed (manditory field)
2.Disbursement Comments. (Not maditory to be completed. Optional)

Can the code you gave be set up to block the record once the status is "HO"
and the above mentioned fields have been completed?

Thanks Tina
 
well, because one of the fields is optional, if you write the code to
include consideration of whether that field contains a value, then *any
given record could potentially NEVER be blocked from changes*.

but to answer the question - yes, you can use additional parameters. for the
sake of clarity, i would probably use an standard If statement rather than
the "toggle" code, as

Private Sub Form_Current()

With Me
If !Status = "HO" And Not IsNull(!DateDisbursed) _
And Not IsNull(!DisbursementComments) Then
.AllowEdits = False
.AllowDeletions = False
Else
.AllowEdits = True
.AllowDeletions = True
End If
End With

End Sub

hth
 
I am not thinking straight!!

you are right Tina

So How do I code it so that only the DateDisbursed field requires a date and
it doesnt matter if the comments is populated ot blank??

Thanks Tina
 
Back
Top