Prevent subform from updating if main form data is invalid

[DataMiner]

Hi,
Mainform and Subform are linked on field "barcode".

Barcode is the primary key for the table that Mainform is based on.

When user is entering a new record, I have the BeforeUpdate event of Mainform.barcode set to check to see if the barcode already exists in the table, and if so, to give the user some meaningful error message.

Works fine, except that the subform still updates to match the invalid, previously-used barcode. I want the subform to stay blank until the user has entered valid data in MainForm.

How do I accomplish this?

Thanks for any ideas.

 

[DataMiner]

PS -- I know I can do this by using me.undo, but this undoes all the other info the user has entered in other fields. Not very friendly, when the only problem is the barcode field. But using barcode.undo on a new record doesn't seem to work.

 

[jaydwest]

The Form_BeforeUpdate Event has a cancel parameter. If the BarCode already exists, set Cancel = True. This will prevent the Main Form from completing the update. This should work. Be aware that this also throws off an error, I think it's 3021 but not sure. You'll need to trap the error and bypass it.

Good Luck!

 

[RuralGuy]

Use the BeforeUpdate event of the form and Cancel = True to keep from saving the record. You can give a meaningful message and do a Me.BarcodeControl.SetFocus to put the user back in the BarcodeControl. (use your control name) You could also verify the Barcode in the BeforeUpdate event of the BarcodeControl and Cancel = True to keep the user in that control until it is correct.

 

[DataMiner]

I have the following in the main form's form_beforeupdate event:
If IsNull(DLookup("failurebarcode", "failurebarcodes", "Failurebarcode='" & Me.FailureBarCode & "'")) Then Exit Sub
MsgBox "Failure Barcode " & Me.FailureBarCode & " has already been used. Please try again."
Cancel = True
Me.FailureBarCode.Undo

When the barcode is found, the msgbox fires,and the record is prevented from being entered, but the subform still updates to match the invalid, previously used barcode. This is what I want to prevent.

 

[RuralGuy]

Why not put this in the BeforeUpdate event of Me.FailureBarCode:

If DCount("*", "failurebarcodes", "[Failurebarcode] ='" & Me.FailureBarCode & "'") > 0 Then
   MsgBox "Failure Barcode " & Me.FailureBarCode & " has already been used. Please try again."
   Cancel = True
   Me.FailureBarCode.Undo
End If

Just for the record, Access *will* save a record if it is Dirty and you move the focus to a SubForm or the SubForm record is Dirty and you move the focus back to the MainForm.

 

[DataMiner]

I tried putting the code in the beforeupdate event of me.failurebarcode. The results were the same.

 

[RuralGuy]

Let's try something a little different:

If DCount("*", "failurebarcodes", "[Failurebarcode] ='" & Me.FailureBarCode & "'") > 0 Then
   MsgBox "Failure Barcode " & Me.FailureBarCode & " has already been used. Please try again."
   Cancel = True
   Me.FailureBarCode.SelStart = 0
   Me.FailureBarCode.SelLength = Len(Me.FailureBarCode)
End If

This should leave the offending FailureBarCode selected and ready to be replaced with another scan. I don't know at this point why your subform is saving but this should hold the cursor here until you get a good scan or the user hits ESC once or twice.

 

[DataMiner]

Ok, I'm finally getting back to working on this. Rural Guy, many thanks for your input. However, I still can't get it to work. I tried the method above, but now I'm getting
1. My custom error message ("barcode has already been used")

followed by, on exiting the Before_Update event:
2. "The expression Before Update produced the following error: Problem referencing property or method of the object."

followed by:

3. "Validation rule violation"
followed by:

4. The dang subform STILL updates to match the erroneous barcode!

 

[RuralGuy]

All of that? We're moving backwards. Is the code I posted on 11/8 the *only* code you have in the BeforeUpdate event?

 

[DataMiner]

Yup, that's it.

At any rate, I've found another approach that I think will work for me. If I use basically my original code but use Me.Undo instead of FailureBarCode.Undo, then the subform fails to update (which is what I want). I was reluctant to do this because FailureBarCode was the last of a number of fields that the user enters, and it seemed rather unfriendly of me to erase all of them just because the last one they entered was wrong. So, I've simply changed FailureBarCode to be the FIRST thing they enter.

You're probably thinking "Well, DUH!", and I can't say that I blame you!

At any rate, I'm pressing on....

Thanks again.

 

[RuralGuy]

Any solution that you can walk away from is a good solution. No wait, that was landings. Oh well. Onward and upward. I would love to get a gander at that db and see what was causing the problem but I'll save that for another life. Enjoy.