Command button to validate data

[punter]

Hi,

I have form which has a subform and a command button that enters records. What I'm looking to do is not allow the record to be updated unless the difference between two fields = zero.

I have attached a zip file of the database. The form is called BOL information. The two fields in question are Cartons (top left, 8th box down) and Carton Count Subtotal (bottom left of form). If Cartons - Carton Count Subtotal do not equal zero I do not want the record to be updated.

I'm looking around for the answer now and I'm not sure where I would put it in the command button to make this happen. Any thoughts?

Thanks

Eddie

 

[punter]

Sorry. I left off the database. It is attached below.

 

[Moniker]

That goes in the form's BeforeUpdate event.

Private Sub Form_BeforeUpdate(Cancel As Integer)

    If Me.[Cartons]-Me.[Carton Count Subtotal] <> 0 Then
        MsgBox "Check your numbers, super-nice person!"
        Cancel = True
    End If

End Sub

FYI, spaces in variable names equals not good.

 

[punter]

Thank you so much for your quick reply. I realize the spaces were not the best thing in the world. I'm getting better at this all the time thanks to help from people like you.

Where is the Beforeupdate event? Is that in the command button? I'm looking in the properities and the VBA code and I don't see it.

Thanks

Eddie

 

[missinglinq]

I don't think

Private Sub Form_BeforeUpdate(Cancel As Integer)

    If Me.[Cartons]-Me.[B][Carton Count Subtotal][/B] <> 0 Then
        MsgBox "Check your numbers, super-nice person!"
        Cancel = True
    End If

End Sub

is going to work; it looks like [Carton Count Subtotal] is a control on the subform, not the main form.

That aside, isn't all you have to do is check to see if the two fields are equal?

Isn't

IF [Cartons] <> [Carton Count Subtotal]

the same as

If [Cartons]-[Carton Count Subtotal] <> 0

 

[punter]

Yes IF [Cartons] <> [Carton Count Subtotal] should work but I'm not sure where I would stick in it my code in order to make it work. I'll kick it around now.

Below is the current code that I have for the command button. This updates the records:

Option Compare Database
Option Explicit

Private Sub Command20_Click()
On Error GoTo Err_Command20_Click


DoCmd.GoToRecord , , acNewRec

Exit_Command20_Click:
Exit Sub

Err_Command20_Click:
MsgBox Err.Description
Resume Exit_Command20_Click

End Sub

Where would the IF [Cartons] <> [Carton Count Subtotal] go?

Thanks always

Eddie.

 

[arsal]

Hello

Is there any way to use Cancel=True operation in VBA module function just the way we use it for example in before_update event procedure to perform field validation on the form in which case we cant move focus to another control until and unless correct value is entered.

Example:
On Field_Before_update event, the following works fine

if len(ME.field) > 20
then
msgbox ........

Cancel = true

end if


What if there are plenty of fields in the form and i want to utilize a Public function to avoid repitition of code.


Cancel=True doesnt seem to work in my case

 

[KeithG]

You can use
exit function

 

[punter]

Hi,

You seem to have piggy backed onto the back of an old thread of mine. Not that I mind but you might have a better result if you start a new thread.

Eddie.