Before Update Problem

[Samantha]

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!

 

[Andy Hull]

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]

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!

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

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!

 

[Andy Hull]

Hi Samantha

So you get the (same) error when trying to select from the drop down box
[Receiving Inspection]?

Your original post supplied code for the before update event of [rma
received] and I think that should be ok now.

Now we need to look at whatever code you have for [Receiving Inspection]. It
looks like you may have a before update event for that control too. Post the
code for any events related to this control.

Regards

Andy Hull

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!

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

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!

 

[Samantha]

Andy,
I might have confused you on what the problem is. The before update code is
on the checkbox [rma received]. When the user check [rma received], I need
the before update script to make sure that the dropdown list [receiving
inspection] is selected prior to the check box [rma received] actually
checked. If the dropdown list [receiving inspection] is NOT selected, the
check box [rma received] can not be checked.
The [rma received] checkbox is in a sub-form (in datasheet view) and the
dropdown [receiving inspection] is on the main form. I am seeing 2 scenerios
where the same conflicting error messge occurs:

1) Moving focus to dropdown [receivng inspection] (to select an option) when
attepmting to checkthe [rma received] checkbox when dropdown [receivng
inspection] is not selected.

2) When checkbox is clicked on multiple times, for example: clicking 1 to
check, then clicking 2 to uncheck, and then clicking again to check.

Hope this explains my problems. I'm pulling my hair out on this one. I hope
somebody can give me some pointers to fix this! Thanks again for your help.

Hi Samantha

So you get the (same) error when trying to select from the drop down box
[Receiving Inspection]?

Your original post supplied code for the before update event of [rma
received] and I think that should be ok now.

Now we need to look at whatever code you have for [Receiving Inspection]. It
looks like you may have a before update event for that control too. Post the
code for any events related to this control.

Regards

Andy Hull

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!

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


:

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!

 

[Andy Hull]

Hi again

Ok, so you have a main form and a subform. That makes things quite different.

I think I'm going to need more background info.

What are the source queries for the 2 forms ? (main & sub)

Is there any more vba code for either of the forms?

And, the code you have posted refers to...

Me![rma received] and
Me![RECEIVING INSPECTION]

but in your last post you say one control is on the main form and the other
is on the sub which means you can not refer to both using "Me". Me! refers
to the active form so if you click the checkbox on the subform then Me!
refers to the subform so Me![RECEIVING INSPECTION] would be invalid!

If you get chance, then post the SQL and all code for both the main form and
subform and say how the forms are linked.

Regards

Andy Hull

Andy,
I might have confused you on what the problem is. The before update code is
on the checkbox [rma received]. When the user check [rma received], I need
the before update script to make sure that the dropdown list [receiving
inspection] is selected prior to the check box [rma received] actually
checked. If the dropdown list [receiving inspection] is NOT selected, the
check box [rma received] can not be checked.
The [rma received] checkbox is in a sub-form (in datasheet view) and the
dropdown [receiving inspection] is on the main form. I am seeing 2 scenerios
where the same conflicting error messge occurs:

1) Moving focus to dropdown [receivng inspection] (to select an option) when
attepmting to checkthe [rma received] checkbox when dropdown [receivng
inspection] is not selected.

2) When checkbox is clicked on multiple times, for example: clicking 1 to
check, then clicking 2 to uncheck, and then clicking again to check.

Hope this explains my problems. I'm pulling my hair out on this one. I hope
somebody can give me some pointers to fix this! Thanks again for your help.

Hi Samantha

So you get the (same) error when trying to select from the drop down box
[Receiving Inspection]?

Your original post supplied code for the before update event of [rma
received] and I think that should be ok now.

Now we need to look at whatever code you have for [Receiving Inspection]. It
looks like you may have a before update event for that control too. Post the
code for any events related to this control.

Regards

Andy Hull

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!

:

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


:

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!

 

[Samantha]

Hi Andy,
Really appreciate your response, as I'm losing a lot of time trying to debug
this.

I found that the problem exist even without a form and subform relationship.
I think the problem is the way Ms Access and MySQL handles checkbox.
I created 2 new test forms: Form1 is linked to a table in Ms Access
(imported from MySQL); Form2 is linked to a MySQL table; Actually Form2 is a
copy of Form1 and I changed the datasource to the linked MySQL table.
Both forms have a checkbox [rma received]. The default for field [rma
received] is 0 (type Tiny Integer). [rma received] is placed on the Detail
section of the form. There are 2 records in the Details section. I did not
put any scripting on the [rma receiving] checkbox. I have a TIMESTAMP field
and its default value to CURRENT_TIMESTAMP.

There are 2 differences that I observed from comparing the 2 forms:
1) On Form2: If I clicked on checkbox [rma received] 2 times consecutively
and then click on another record (either on the checkbox or another field),
then the Write Conflict error message occurs. (Clicking the checkbox 2 times
actually checks and unchecks the box). The same is done on Form2, but NO
error message!

2) On Form1: There are only 2 records with data, but there's a third line
for new records. The third line does not appear on Form2. I don't know if
this affects the performance, but an observation that I saw.

What do you think about this? Any pointers would help! Thanks again.

Hi again

Ok, so you have a main form and a subform. That makes things quite different.

I think I'm going to need more background info.

What are the source queries for the 2 forms ? (main & sub)

Is there any more vba code for either of the forms?

And, the code you have posted refers to...

Me![rma received] and
Me![RECEIVING INSPECTION]

but in your last post you say one control is on the main form and the other
is on the sub which means you can not refer to both using "Me". Me! refers
to the active form so if you click the checkbox on the subform then Me!
refers to the subform so Me![RECEIVING INSPECTION] would be invalid!

If you get chance, then post the SQL and all code for both the main form and
subform and say how the forms are linked.

Regards

Andy Hull

Andy,
I might have confused you on what the problem is. The before update code is
on the checkbox [rma received]. When the user check [rma received], I need
the before update script to make sure that the dropdown list [receiving
inspection] is selected prior to the check box [rma received] actually
checked. If the dropdown list [receiving inspection] is NOT selected, the
check box [rma received] can not be checked.
The [rma received] checkbox is in a sub-form (in datasheet view) and the
dropdown [receiving inspection] is on the main form. I am seeing 2 scenerios
where the same conflicting error messge occurs:

1) Moving focus to dropdown [receivng inspection] (to select an option) when
attepmting to checkthe [rma received] checkbox when dropdown [receivng
inspection] is not selected.

2) When checkbox is clicked on multiple times, for example: clicking 1 to
check, then clicking 2 to uncheck, and then clicking again to check.

Hope this explains my problems. I'm pulling my hair out on this one. I hope
somebody can give me some pointers to fix this! Thanks again for your help.

Hi Samantha

So you get the (same) error when trying to select from the drop down box
[Receiving Inspection]?

Your original post supplied code for the before update event of [rma
received] and I think that should be ok now.

Now we need to look at whatever code you have for [Receiving Inspection]. It
looks like you may have a before update event for that control too. Post the
code for any events related to this control.

Regards

Andy Hull


:

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!

:

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


:

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!

 

[Samantha]

Hi,
I think I solved the problem. It turns out that I need to have a key field
in the form's datasource. In this case, I added the RMAID field (which is
auto increment) to the form's datasource and the write conflict error message
does not pop up. And I can check and uncheck the [rma receiving] field as
many times as I want! Thank you for your time!

Hi Andy,
Really appreciate your response, as I'm losing a lot of time trying to debug
this.

I found that the problem exist even without a form and subform relationship.
I think the problem is the way Ms Access and MySQL handles checkbox.
I created 2 new test forms: Form1 is linked to a table in Ms Access
(imported from MySQL); Form2 is linked to a MySQL table; Actually Form2 is a
copy of Form1 and I changed the datasource to the linked MySQL table.
Both forms have a checkbox [rma received]. The default for field [rma
received] is 0 (type Tiny Integer). [rma received] is placed on the Detail
section of the form. There are 2 records in the Details section. I did not
put any scripting on the [rma receiving] checkbox. I have a TIMESTAMP field
and its default value to CURRENT_TIMESTAMP.

There are 2 differences that I observed from comparing the 2 forms:
1) On Form2: If I clicked on checkbox [rma received] 2 times consecutively
and then click on another record (either on the checkbox or another field),
then the Write Conflict error message occurs. (Clicking the checkbox 2 times
actually checks and unchecks the box). The same is done on Form2, but NO
error message!

2) On Form1: There are only 2 records with data, but there's a third line
for new records. The third line does not appear on Form2. I don't know if
this affects the performance, but an observation that I saw.

What do you think about this? Any pointers would help! Thanks again.

Hi again

Ok, so you have a main form and a subform. That makes things quite different.

I think I'm going to need more background info.

What are the source queries for the 2 forms ? (main & sub)

Is there any more vba code for either of the forms?

And, the code you have posted refers to...

Me![rma received] and
Me![RECEIVING INSPECTION]

but in your last post you say one control is on the main form and the other
is on the sub which means you can not refer to both using "Me". Me! refers
to the active form so if you click the checkbox on the subform then Me!
refers to the subform so Me![RECEIVING INSPECTION] would be invalid!

If you get chance, then post the SQL and all code for both the main form and
subform and say how the forms are linked.

Regards

Andy Hull

Andy,
I might have confused you on what the problem is. The before update code is
on the checkbox [rma received]. When the user check [rma received], I need
the before update script to make sure that the dropdown list [receiving
inspection] is selected prior to the check box [rma received] actually
checked. If the dropdown list [receiving inspection] is NOT selected, the
check box [rma received] can not be checked.
The [rma received] checkbox is in a sub-form (in datasheet view) and the
dropdown [receiving inspection] is on the main form. I am seeing 2 scenerios
where the same conflicting error messge occurs:

1) Moving focus to dropdown [receivng inspection] (to select an option) when
attepmting to checkthe [rma received] checkbox when dropdown [receivng
inspection] is not selected.

2) When checkbox is clicked on multiple times, for example: clicking 1 to
check, then clicking 2 to uncheck, and then clicking again to check.

Hope this explains my problems. I'm pulling my hair out on this one. I hope
somebody can give me some pointers to fix this! Thanks again for your help.


:

Hi Samantha

So you get the (same) error when trying to select from the drop down box
[Receiving Inspection]?

Your original post supplied code for the before update event of [rma
received] and I think that should be ok now.

Now we need to look at whatever code you have for [Receiving Inspection]. It
looks like you may have a before update event for that control too. Post the
code for any events related to this control.

Regards

Andy Hull


:

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!

:

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


:

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!

 

[Andy Hull]

Glad you solved it - and thanks for posting back what you did.

Hi,
I think I solved the problem. It turns out that I need to have a key field
in the form's datasource. In this case, I added the RMAID field (which is
auto increment) to the form's datasource and the write conflict error message
does not pop up. And I can check and uncheck the [rma receiving] field as
many times as I want! Thank you for your time!

Hi Andy,
Really appreciate your response, as I'm losing a lot of time trying to debug
this.

I found that the problem exist even without a form and subform relationship.
I think the problem is the way Ms Access and MySQL handles checkbox.
I created 2 new test forms: Form1 is linked to a table in Ms Access
(imported from MySQL); Form2 is linked to a MySQL table; Actually Form2 is a
copy of Form1 and I changed the datasource to the linked MySQL table.
Both forms have a checkbox [rma received]. The default for field [rma
received] is 0 (type Tiny Integer). [rma received] is placed on the Detail
section of the form. There are 2 records in the Details section. I did not
put any scripting on the [rma receiving] checkbox. I have a TIMESTAMP field
and its default value to CURRENT_TIMESTAMP.

There are 2 differences that I observed from comparing the 2 forms:
1) On Form2: If I clicked on checkbox [rma received] 2 times consecutively
and then click on another record (either on the checkbox or another field),
then the Write Conflict error message occurs. (Clicking the checkbox 2 times
actually checks and unchecks the box). The same is done on Form2, but NO
error message!

2) On Form1: There are only 2 records with data, but there's a third line
for new records. The third line does not appear on Form2. I don't know if
this affects the performance, but an observation that I saw.

What do you think about this? Any pointers would help! Thanks again.

Hi again

Ok, so you have a main form and a subform. That makes things quite different.

I think I'm going to need more background info.

What are the source queries for the 2 forms ? (main & sub)

Is there any more vba code for either of the forms?

And, the code you have posted refers to...

Me![rma received] and
Me![RECEIVING INSPECTION]

but in your last post you say one control is on the main form and the other
is on the sub which means you can not refer to both using "Me". Me! refers
to the active form so if you click the checkbox on the subform then Me!
refers to the subform so Me![RECEIVING INSPECTION] would be invalid!

If you get chance, then post the SQL and all code for both the main form and
subform and say how the forms are linked.

Regards

Andy Hull


:

Andy,
I might have confused you on what the problem is. The before update code is
on the checkbox [rma received]. When the user check [rma received], I need
the before update script to make sure that the dropdown list [receiving
inspection] is selected prior to the check box [rma received] actually
checked. If the dropdown list [receiving inspection] is NOT selected, the
check box [rma received] can not be checked.
The [rma received] checkbox is in a sub-form (in datasheet view) and the
dropdown [receiving inspection] is on the main form. I am seeing 2 scenerios
where the same conflicting error messge occurs:

1) Moving focus to dropdown [receivng inspection] (to select an option) when
attepmting to checkthe [rma received] checkbox when dropdown [receivng
inspection] is not selected.

2) When checkbox is clicked on multiple times, for example: clicking 1 to
check, then clicking 2 to uncheck, and then clicking again to check.

Hope this explains my problems. I'm pulling my hair out on this one. I hope
somebody can give me some pointers to fix this! Thanks again for your help.


:

Hi Samantha

So you get the (same) error when trying to select from the drop down box
[Receiving Inspection]?

Your original post supplied code for the before update event of [rma
received] and I think that should be ok now.

Now we need to look at whatever code you have for [Receiving Inspection]. It
looks like you may have a before update event for that control too. Post the
code for any events related to this control.

Regards

Andy Hull


:

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!

:

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


:

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!