Form validation and new record? (1 Viewer)

[Gasman]

Hi all,

How does one handle form validation and a new record?
I have the following code for validation on a form.

As you can see I am trying to handle a new record and validate if not a new record, (commented out for now) however I still need to validate if it is new record?
So how does one handle this situation?

At present as soon as I add the new record, I get the error messages below. Ok'ing through them eventually allows me to enter data, but I do not want to see them when adding a new record unless I have missed a control, but this is happening before I have the chance to enter any data.?

TIA

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error Resume Next
'If Not Me.NewRecord Then
    If IsNull(Me.cboFeeTypeID) Then
        Cancel = True
        'Me.Undo
        Me.Dirty = False
        MsgBox "Fee Type is mandatory"
    End If

    If Nz(Me.FeeAmount, 0) = 0 Then
        Cancel = True
        'Me.Undo
        Me.Dirty = False
        MsgBox "Fee amount must be > £0"
    End If
'End If
End Sub

 

[moke123]

try removing Me.Dirty = False

 

[missinglinq]

As moke123 suggested, you can't use

Me.Dirty = False

in the Form_BeforeUpdate event, as this interferes with what the Access Gnomes are already in the middle of doing, which is saving the Record...and will always pop an error!

Linq ;0)>

 

[Gasman]

Thank you both,

I'll give it a go when I next have to create a record.
I've already commented out the lines with that statement.

 

[Gasman]

OK,
I've commented out the Me.dirty code but as soon as I add a new record using the new record icon at the bottom of the form I get the messages twice.
I do not want to show the messages until I get to close the form?

TIA

 

[moke123]

you get 2 messages because your calling it twice with 2 If/thens.

i often write mine differently along the lines of

Private Sub Form_BeforeUpdate(Cancel As Integer)

     dim IntFlg as integer
     dim strMsg as string

     intFlg = 0

    If IsNull(Me.cboFeeTypeID) Then
          intflg = 1
          strMsg = strMsg & "Fee Type is mandatory" & vbnewline
    end if
        
    If Nz(Me.FeeAmount, 0) = 0 Then
          intflg = 1
          strMsg = strMsg &  "Fee amount must be > £0" & vbnewline
     End If

     If intFlg = 1 then
          Cancel = True
          MsgBox  strMsg
     End If

End Sub

 

[Gasman]

Ah I see now.
Thank you Moke, I'll give that a go on Monday.

 

[Gasman]

Moke,
Just to clarify both messages get displayed twice.?
The event is being called twice for some reason?

 

[isladogs]

I'm guessing that as each If clause causes an update the code loops/runs twice.
Try disabling one of the If sections.
Am I correct in saying there will only be one message ONCE?

If so try putting this in each If clause instead of what you have now

 If intflg=0 Then intflg=1

Does that fix it?

 

[Gasman]

Hi Colin,

Yes, I only want to see each message once.

It's in work, so cannot try until Monday.
I would also want both messages to be displayed, but I can do that easily enough using Mokes' logic.

I'm guessing that as each If clause causes an update the code loops/runs twice.
Try disabling one of the If sections.
Am I correct in saying there will only be one message ONCE?

If so try putting this in each If clause instead of what you have now

 If intflg=0 Then intflg=1

Does that fix it?

 

[isladogs]

What I'm suggesting won't stop each message being displayed
However, if I'm correct, it will stop the repeat message

 

[moke123]

Just to clarify both messages get displayed twice.?

No. The first 2 if/then statements just check for null values and if they are,
concatenates a message string and sets the flag to 1.

the last if/then checks the flag and if it = 1 displays a single concatenated message and cancels the update.

Colin: I think he's talking about his code repeating, not mine. I just tested his as i thought it me be the On Error Resume Next but it wasnt. I think he may have some other code on the form causing his problem.

 

[isladogs]

Hi moke
Sorry - I got the wrong end of the stick....

 

[Pat Hartman]

Your original question is about new record validation. Generally new and existing records should be validated the same way. If not, put the New Record specific validation inside the If Me.NewRecord = True statement.

Unless you have some need to report all errors at once rather than one at a time, it is far easier for people to deal with fixing each error as it is encountered so I would use the following code. It sets focus to the control with the problem and exits immediately after canceling the update. I also change the null checks to include ZLS. If a user backspaces a control to "clear" it, you end up with a ZLS and those shouldn't be saved. In fact, in most cases, I set the Allow ZLS property to No so Access will prevent them from being saved.

Private Sub Form_BeforeUpdate(Cancel As Integer)

    Dim strMsg as string

    If Me.cboFeeTypeID & "" = "" Then
          strMsg = strMsg & "Fee Type is mandatory" & vbnewline
          msgbox strMsg, vbOKOnly
          Cancel = True
          Me.cboFeeTypeID.SetFocus
          Exit Sub
    end if
        
    If Nz(Me.FeeAmount, 0) = 0  OR Me.FeeAmount & "" = "" Then
          strMsg = strMsg &  "Fee amount must be > £0" & vbnewline
          msgbox strMsg, vbOKOnly
          Cancel = True
          Me.FeeAmount.SetFocus
          Exit Sub
    End If

End Sub

 

[Gasman]

Moke,

I have not had a chance to implement your code, as the DB is in work.

Yes my code currently shows each message twice before I can get to put any data in a control after I click the New record icon.

I will try both suggestions on Monday.

No. The first 2 if/then statements just check for null values and if they are,
concatenates a message string and sets the flag to 1.

the last if/then checks the flag and if it = 1 displays a single concatenated message and cancels the update.

Colin: I think he's talking about his code repeating, not mine. I just tested his as i thought it me be the On Error Resume Next but it wasnt. I think he may have some other code on the form causing his problem.

 

[Gasman]

Hi Pat,

I want to validate the data, whether a new record or not. What I was trying to say, was that my code did not even give me the chance to enter data when adding a new record, before it was complaining that the data was missing.

Your original question is about new record validation. Generally new and existing records should be validated the same way. If not, put the New Record specific validation inside the If Me.NewRecord = True statement.

Unless you have some need to report all errors at once rather than one at a time, it is far easier for people to deal with fixing each error as it is encountered so I would use the following code. It sets focus to the control with the problem and exits immediately after canceling the update. I also change the null checks to include ZLS. If a user backspaces a control to "clear" it, you end up with a ZLS and those shouldn't be saved. In fact, in most cases, I set the Allow ZLS property to No so Access will prevent them from being saved.

Private Sub Form_BeforeUpdate(Cancel As Integer)

    Dim strMsg as string

    If Me.cboFeeTypeID & "" = "" Then
          strMsg = strMsg & "Fee Type is mandatory" & vbnewline
          msgbox strMsg, vbOKOnly
          Cancel = True
          Me.cboFeeTypeID.SetFocus
          Exit Sub
    end if
        
    If Nz(Me.FeeAmount, 0) = 0  OR Me.FeeAmount & "" = "" Then
          strMsg = strMsg &  "Fee amount must be > £0" & vbnewline
          msgbox strMsg, vbOKOnly
          Cancel = True
          Me.FeeAmount.SetFocus
          Exit Sub
    End If

End Sub

 

[moke123]

Do you have any code that runs that maybe sets default values or something?

 

[Gasman]

Do you have any code that runs that maybe sets default values or something?

I only have some code to set the FK for the record, as the form is opened from another form and I pass in the ID of the parent.

Something along the lines of

If me.newrecord then
    Me.cboClientID = Me.openargs
end if

Best leave it until Monday and then I can answer with certainty.

TIA

 

[Pat Hartman]

I want to validate the data, whether a new record or not. What I was trying to say, was that my code did not even give me the chance to enter data when adding a new record, before it was complaining that the data was missing.

You have code that is causing the record to be saved. Please post the entire code module.

Also, I prefer to NOT dirty a record before the user does. This leads to confusion for the user. He thinks he just opened the form but didn't type anything and doesn't understand why he is getting a complaint about missing data. Or, even worse, you have no validation code and the empty (except for the FK) record just gets saved.

Use the BeforeInsert event to populate the foreign key. That event runs as soon as someone types the first character into ANY control. Once that happens, the user should recognize that HE dirtied the record and will not be confused by any missing data messages.

 

[moke123]

I only have some code to set the FK for the record,

That's what I suspected. As Pat points out your dirtying the record.
Another option would be to set the default value along the lines of

If me.newrecord then
    Me.cboClientID.DefaultValue = Me.openargs
end if