RE: Before Update Problem

Tech-Archive recommends: Fix windows errors by optimizing your registry

Hi Andy,

I followed the code that you provided. However, I'm still getting the error
message when I try to select from dropdown list "[RECEIVING INSPECTION]".
The purpose of this script is to make sure that this dropdown is selected
prior to checking the checkbox "[rma received]".
Am I doing anything else incorrectly?
Thanks for you help!

"Andy Hull" wrote:

Hi Samantha

Try the following code instead...

Private Sub RMA_RECEIVED_BeforeUpdate(Cancel As Integer)
If Me![rma received] = True Then
If Nz(Me![RECEIVING INSPECTION].Value,"") = "" Then
Cancel = True
End If
End If
End Sub


The bits changed are...
In your 1st "If" you said if rma_received is false - I guess you're saying
it has to start as false and still is as this is the BEFORE update routine.
However, the tickbox value changes as soon as it is ticked so you need to
test if it is true.

In your 2nd "If" you said (if it is null) or (if it is NOT an empty string)
- I presume you actually wanted to say (if it is null) or (if it is an empty
string) - I have used the Nz function which does both things at once.

You don't need the refresh - in fact, you mustn't use refresh! At this
point, the oldvalue will be false, the value will be true and you have told
access you want to cancel the update which it will do on exiting the sub. As
soon as you say refresh, it "saves" the current value - which is true - and
the cancel becomes meaningless.

Also, you don't need to (or shouldn't!) set the tickbox to false - the
earlier command (cancel=true) will ensure that happens automatically - you
just have to remember that it takes effect on exiting the sub.

Hope this helps

Andy Hull

Ps If you're curious try putting the following lines at various points in
your code to see how & when the tickbox values change...

MsgBox "OldValue: " & Me![rma received].OldValue
MsgBox "Value: " & Me![rma received].Value


"Samantha" wrote:

I need to check to make sure that all necessary data is entered prior to the
receiving checkbox being checked. So, I have the following code to check to
make sure that combo [RECEIVING INSPECTION] is filled-in first. If it is NOT
filled in, I want to make sure that the user can NOT check the checkbox [rma
received].

My problem is that the backend is a MySQL database. And therefore there
seems to be some sort of conflict with MsAccess and MySQL. Anyhow it is
giving me this error:
"Write Conflict
This record has been chagned by another user since you started editing it.
If you save the record, you will overwrite the changes the other user made.
Copying the changes to the clipboard will let you look a the values the
other user entered, and the paste your changes back in if you decide to make
changes."

Here is my code now:

Private Sub RMA_RECEIVED_BeforeUpdate(Cancel As Integer)
If Me![rma received] = False Then
If (IsNull(Me![RECEIVING INSPECTION].Value)) Or (Me![RECEIVING
INSPECTION].Value <> "") Then

Cancel = True
Me.Refresh
Me![rma received] = False
Exit Sub
End If
End If
End Sub

Any one has any clue on how I can solve this? Any pointers are very much
appreciated!
.

Relevant Pages

  • RE: Before Update Problem
    ... Private Sub RMA_RECEIVED_BeforeUpdate ... access you want to cancel the update which it will do on exiting the sub. ... make sure that combo [RECEIVING INSPECTION] is filled-in first. ...
    (microsoft.public.access.formscoding)
  • Re: More efficient code for If Else data validation rule?
    ... cboFavoriteColor. ... if i'm going to cancel an event, i normally do it before taking ... Private Sub cboFavoriteColor_BeforeUpdate ...
    (microsoft.public.access.formscoding)
  • Re: More efficient code for If Else data validation rule?
    ... cboFavoriteColor = "Other" evaluates to Null, then the value of the Enabled ... if i'm going to cancel an event, i normally do it before taking ... Private Sub cboFavoriteColor_BeforeUpdate ...
    (microsoft.public.access.formscoding)
  • Re: Require input in a cell before saving file
    ... Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, ... Cancel As Boolean) ... Gord Dibben MS Excel MVP ...
    (microsoft.public.excel.misc)
  • Re: Require input in a cell before saving file
    ... or 2) Digitally sign and verify the macro as safe. ... Cancel As Boolean) ... Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, ... ...and yet I still can save the file with cell E59 being empty. ...
    (microsoft.public.excel.misc)