Form validation and new record? (1 Viewer)

Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 01:18
Joined
Sep 21, 2011
Messages
14,221
Thank you Pat,
I believe I have that code in the OnCurrent event at present. I will move it to that other event.

I'll post all of the form code on Monday, as it is now, then correct everything after the responses.


Pat Hartman said:
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.
Click to expand...
 
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 01:18
Joined
Sep 21, 2011
Messages
14,221
Morning all,

I've moved the setting of the FK to the On Enter even as recommended, but left everything else as is, so I can make the changes all in one go.

TIA
Code: 
Option Compare Database
Option Explicit
Private Sub cboFeeTypeID_AfterUpdate()
Dim iCount As Integer

iCount = DCount("*", "XLTWMInvoicing", "[Client Name] LIKE '* " & Me.cboClientID.Column(2) & " *'")
If Me.cboFeeTypeID.Column(1) = "Packs Fee" And iCount > 0 Then
    MsgBox "Client Surname found in previous invoice. Please check not the same Client"
End If
Me.FeeAmount = Me.cboFeeTypeID.Column(2)
' Default the invoice date to the following Monday if empty
If IsNull(Me.InvoiceDate) Then
    If (Me.FeeTypeID = 3 Or 4) Then
        Me.InvoiceDate = DateAdd("d", 9 - Weekday(Date), Date)
    Else
        Me.InvoiceDate = Date
    End If
End If
End Sub
Private Sub cmdClose_Click()
On Error GoTo ErrProc
    If Me.Dirty Then
        Me.Dirty = False
        DoCmd.RunCommand acCmdSaveRecord
    End If
    DoCmd.Close
ExitProc:
    Exit Sub
ErrProc:
    Select Case Err.Number
        Case 2101   ' save failed due to an error 2501 if not using me.dirty=false
            MsgBox "Please fix error or close again", vbOKOnly
            Resume ExitProc
        Case 3021
            Resume ExitProc
        Case Else
            MsgBox Err.Number & "--" & Err.Description
            Resume ExitProc
    End Select
End Sub
Private Sub cmdDelete_Click()
Dim strAnswer As String

strAnswer = MsgBox("Are you sure you want to delete this record?", vbYesNo, "Delete Confirmation")
If strAnswer = vbYes Then
    DoCmd.RunCommand acCmdDeleteRecord
    MsgBox "Record deleted!", , "Delete Result"
Else
    MsgBox "Delete record cancelled!", , "Delete Result"
End If
End Sub

Private Sub Form_BeforeInsert(Cancel As Integer)
If Me.NewRecord Then
    Me.cboClientID = Me.OpenArgs
End If

End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error Resume Next
'If Not Me.NewRecord Then
    If Nz(Me.FeeAmount, 0) = 0 Then
        Cancel = True
        'Me.Undo
        'Me.Dirty = False
        MsgBox "Fee amount must be > £0"
        Me.FeeAmount.SetFocus
    End If
    
    If IsNull(Me.cboFeeTypeID) Then
        Cancel = True
        'Me.Undo
        'Me.Dirty = False
        MsgBox "Fee Type is mandatory"
        Me.cboFeeTypeID.SetFocus
    End If


'End If
End Sub
Private Sub Form_Current()
' Protect controls if paid
Me.cboFeeTypeID.Enabled = IsNull(Me.PaidDate)
Me.FeeAmount.Enabled = IsNull(Me.PaidDate)
Me.InvoiceDate.Enabled = IsNull(Me.PaidDate)
Me.PaidDate.Enabled = IsNull(Me.PaidDate)

End Sub

Private Sub Form_Open(Cancel As Integer)
'   This is the main form open event handler
    'Me.RecordSource = "tblFee"            'set the recordsource of the main form
    'Me.OrderBy = "Surname, Forenames"
    'Me.OrderByOnLoad = True
    'Me.OrderByOn = True
    Me.cfrmFee.SourceObject = "cfrmFee"      'load datasheet subform w/ blank RecordSource
    Set Me.cfrmFee.Form.Recordset = Me.Recordset 'set subform recordset to same object as main form's
End Sub
 
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 01:18
Joined
Sep 21, 2011
Messages
14,221
Bumping this thread.
 
P

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 20:18
Joined
Feb 19, 2002
Messages
43,213
I've moved the setting of the FK to the On Enter even as recommended
Click to expand...
That's not what I recommended. I recommended the BeforeInsert event which you seem to be using.

Your validation in cboFeeTypeID_AfterUpdate() isn't doing anything meaningful. The user may get an error message but so what.

The cmdClose is attempting to save the record TWICE. Pick a method and stick with it. It makes no sense to run both of them.

Similar to consolidating validation code in the Form's BeforeUpdate event, you need to put your delete confirmation code in the correct form level event. Your delete validation code will not fire if the user simply uses the delete option from the ribbon. Your Delete button should simply run the Delete command just the way your save button runs the save command. Then the real form events, trap the errors. Your error traps must be in form level events that can actually STOP the action from happening so if the event you put validation code into does not have a Cancel argument, it will offer no protection at all.

Your Form's BeforeUpdate event is still doing all validation. That simply confuses the user when there are multiple errors. Just give him one and exit the event.

Not sure why you are using the same recordset for both the main form and subform. I'm guessing that this will lead to unexpected errors. It's bad enough to have to worry about multiple users attempting to update the same record at the same time, you don't need to conflict with yourself.
 
moke123

moke123

AWF VIP
Local time
Yesterday, 20:18
Joined
Jan 11, 2013
Messages
3,910
Code: 
iCount = DCount("*", "XLTWMInvoicing", "[Client Name] LIKE '* " & Me.cboClientID.Column(2) & " *'")
This line also looks like it will return unreliable results, using like with wildcards. Cant you use a clientID? What happens when 2 clients have the same or similar names? Using a unique ClientID resolves the question definitively.
 
Solo712

Solo712

Registered User.
Local time
Yesterday, 20:18
Joined
Oct 19, 2012
Messages
828
Gasman said:
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
Click to expand...

Withdrawn...noticed that Pat H. made the points I wanted to make.

J.
 
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 01:18
Joined
Sep 21, 2011
Messages
14,221
Pat Hartman said:
That's not what I recommended. I recommended the BeforeInsert event which you seem to be using.
Click to expand...
Oops, sorry my mistake.:banghead:
Pat Hartman said:
Your validation in cboFeeTypeID_AfterUpdate() isn't doing anything meaningful. The user may get an error message but so what.
Click to expand...

There I am attempting to see if a client was processed previously in the Excel sheet we were using. Just trying to cover the changeover period?

The rest is setting defaults for ease of data entry.

Pat Hartman said:
The cmdClose is attempting to save the record TWICE. Pick a method and stick with it. It makes no sense to run both of them.
Click to expand...
Which would be the best Pat? I am keen to learn better ways of carrying out a task
Pat Hartman said:
Similar to consolidating validation code in the Form's BeforeUpdate event, you need to put your delete confirmation code in the correct form level event. Your delete validation code will not fire if the user simply uses the delete option from the ribbon. Your Delete button should simply run the Delete command just the way your save button runs the save command. Then the real form events, trap the errors. Your error traps must be in form level events that can actually STOP the action from happening so if the event you put validation code into does not have a Cancel argument, it will offer no protection at all.
Click to expand...
Whicch event would that be Pat? On Delete?
Pat Hartman said:
Your Form's BeforeUpdate event is still doing all validation. That simply confuses the user when there are multiple errors. Just give him one and exit the event.
Click to expand...

Do you mean present all the errors at once?, well in this case both?
Pat Hartman said:
Not sure why you are using the same recordset for both the main form and subform. I'm guessing that this will lead to unexpected errors. It's bad enough to have to worry about multiple users attempting to update the same record at the same time, you don't need to conflict with yourself.
Click to expand...

That is because I am using the subform, just to select the relevant record to edit . What has been named as the Emulated Split Form. I use it purely as a record locater. I do not use the subform for editing purposes., that is done on the main form.
 
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 01:18
Joined
Sep 21, 2011
Messages
14,221
Hi Moke,

There I am attempting to see if a client was processed previously in the Excel sheet we were using. Just trying to cover the changeover period?

If there are two Smiths, we would need to check if they are the same person. The Excel system only went by names, and then not even full names.

It is only to cover the changeover period, eventually they could not possible exist in the Excel workbook and I could probably remove that piece of code.

moke123 said:
Code: 
iCount = DCount("*", "XLTWMInvoicing", "[Client Name] LIKE '* " & Me.cboClientID.Column(2) & " *'")
This line also looks like it will return unreliable results, using like with wildcards. Cant you use a clientID? What happens when 2 clients have the same or similar names? Using a unique ClientID resolves the question definitively.
Click to expand...
 
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 01:18
Joined
Sep 21, 2011
Messages
14,221
Ok,
I've amended the code as below, and it seems to work a lot better. No error message when adding a record and can close the form OK if no data has been entered.
Delete code seems to work as well. Thank you for those events Pat.

Can anyone see any more potential problems with what I have at present please.?

TIA
Code: 
Option Compare Database
Option Explicit
Private Sub cboFeeTypeID_AfterUpdate()
Dim iCount As Integer

iCount = DCount("*", "XLTWMInvoicing", "[Client Name] LIKE '* " & Me.cboClientID.Column(2) & " *'")
If Me.cboFeeTypeID.Column(1) = "Packs Fee" And iCount > 0 Then
    MsgBox "Client Surname found in previous invoice. Please check not the same Client"
End If
Me.FeeAmount = Me.cboFeeTypeID.Column(2)
' Default the invoice date to the following Monday if empty
If IsNull(Me.InvoiceDate) Then
    If (Me.FeeTypeID = 3 Or 4) Then
        Me.InvoiceDate = DateAdd("d", 9 - Weekday(Date), Date)
    Else
        Me.InvoiceDate = Date
    End If
End If
End Sub
Private Sub cmdClose_Click()
On Error GoTo ErrProc
    If Me.Dirty Then
        Me.Dirty = False
    End If
    DoCmd.Close
ExitProc:
    Exit Sub
ErrProc:
    Select Case Err.Number
        Case 2101   ' save failed due to an error 2501 if not using me.dirty=false
            MsgBox "Please fix error or close again", vbOKOnly
            Resume ExitProc
        Case 3021
            Resume ExitProc
        Case Else
            MsgBox Err.Number & "--" & Err.Description
            Resume ExitProc
    End Select
End Sub
Private Sub cmdDelete_Click()
On Error GoTo ErrProc
    DoCmd.RunCommand acCmdDeleteRecord
ExitProc:
    Exit Sub
ErrProc:
    Select Case Err.Number
        Case 2501   'Delete cancelled message save failed due to an error 2501 if not using me.dirty=false
            Resume ExitProc
        Case Else
            MsgBox Err.Number & "--" & Err.Description
            Resume ExitProc
    End Select
End Sub

Private Sub Form_AfterDelConfirm(Status As Integer)
 Select Case Status
    Case acDeleteOK
        MsgBox "Record deleted!", , "Delete Result"
    Case acDeleteCancel
        MsgBox "Delete record cancelled!", , "Delete Result"
    Case acDeleteUserCancel
        MsgBox "Delete record cancelled!", , "Delete Result"
 End Select
End Sub

Private Sub Form_BeforeDelConfirm(Cancel As Integer, Response As Integer)
Response = acDataErrContinue
 ' Display custom dialog box.
If MsgBox("Are you sure you want to delete this record?", vbYesNo, "Delete Confirmation") = vbNo Then
    Cancel = True
End If
End Sub

Private Sub Form_BeforeInsert(Cancel As Integer)
If Me.NewRecord Then
    Me.cboClientID = Me.OpenArgs
End If

End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim blnError As Boolean
Dim strError As String, strResponse As String
On Error Resume Next

If Nz(Me.FeeAmount, 0) = 0 Then
    blnError = True
    strError = "Fee amount must be > £0" & vbCrLf
    Me.FeeAmount.SetFocus
End If
    
If IsNull(Me.cboFeeTypeID) Then
    blnError = True
    strError = strError & "Fee Type is mandatory"
    Me.cboFeeTypeID.SetFocus
End If
If blnError Then
    Cancel = True
    strResponse = MsgBox(strError, , "Data Validation")
End If
End Sub
Private Sub Form_Current()
' Protect controls if paid
Me.cboFeeTypeID.Enabled = IsNull(Me.PaidDate)
Me.FeeAmount.Enabled = IsNull(Me.PaidDate)
Me.InvoiceDate.Enabled = IsNull(Me.PaidDate)
Me.PaidDate.Enabled = IsNull(Me.PaidDate)

End Sub


Private Sub Form_Open(Cancel As Integer)
'   This is the main form open event handler
    'Me.RecordSource = "tblFee"            'set the recordsource of the main form
    'Me.OrderBy = "Surname, Forenames"
    'Me.OrderByOnLoad = True
    'Me.OrderByOn = True
    Me.cfrmFee.SourceObject = "cfrmFee"      'load datasheet subform w/ blank RecordSource
    Set Me.cfrmFee.Form.Recordset = Me.Recordset 'set subform recordset to same object as main form's
End Sub
 
P

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 20:18
Joined
Feb 19, 2002
Messages
43,213
The "before" events run BEFORE a control value or record is saved and the "after" events run AFTER the control value or record is saved. It is necessary to use the AFTERUpdate events for controls if you want to modify an entered value. For example, if you want to ensure codes are entered as all caps, you would do that in the control's AfterUpdate event. But notice that the "after" events do not have a Cancel argument. That means that you cannot cancel them so they should never be used for validation. That's what I was trying to tell you about cboFeeTypeID_AfterUpdate(). Validation does NOT belong in that event. Moving ALL validation to the Form's BeforeUpdate event is safe. Some validation can be done in control events but once you understand that if a control isn't entered, NO control level events run so things like null checks can NEVER be performed in control level events.

Regarding how to save a record. Always be explicit. I use
DoCmd.RunCommand acCmdSaveRecord
because anyone looking at it will KNOW that the command is saving the record. There is some error which I have never encountered so I don't know what causes it that has prompted certain experts to recommend using
Me.Dirty = False

When I first saw that expression, I thought the save was being CANCELLED rather than forced so if you use it, put a comment in the code to help people like me wo think the statement is not clear. You will also see code that recommends using Me.Refresh or Me.Requery. That is simply bad advice since both those commands do something specific which is to either refresh the recordset or rerun the query entirely. Saving a record is simply a SIDE EFFECT. The problem with these is the unintended consequences of what the Refresh and Requery do that always trips people up when they think that these are "save" commands.

Regarding raising errors in validation. In my BeforeUpdate event, I exit sub after raising each error so the code stops. It is really rare that people make multiple mistakes when entering data or even any errors at all so you are not saving anything by deviating from my suggestion except causing confusion in the rare case where there is more than one error.
 
Solo712

Solo712

Registered User.
Local time
Yesterday, 20:18
Joined
Oct 19, 2012
Messages
828
Pat Hartman said:
Regarding how to save a record. Always be explicit. I use DoCmd.RunCommand acCmdSaveRecord
because anyone looking at it will KNOW that the command is saving the record. There is some error which I have never encountered so I don't know what causes it that has prompted certain experts to recommend using
Me.Dirty = False
Click to expand...

I am not sure. It is true that the RunCommand is more explicit and I use it. Both commands can generate errors, if the event level gets mixed up. The advantage of Me.Dirty=False is that you can substitute the Forms designation "Me" for actual name and have it fire from anywhere. If you use it, you will have to protect against error 2101, which happens when you cancel the Before_Update event because of a failed validation.

You may want to adopt a "controlled form exit" protocol and prevent the automatic update of the record (which happens e.g. if you click on a subform). In effect you can control when the Before_Update event will engage by inserting this as the first statement.

Code: 
If StopFlagOn Then          'this assures that new record is not 
      Cancel = True                       'updatable automatically
      Exit Sub
End If

The flag variable has module scope and is set to True in the Current event. The flag is released by the Save button click.

Code: 
Private Sub SaveRec_Click()
     If Me.Dirty Then
       StopFlagOn = False
       On Error Resume Next
       DoCmd.RunCommand acCmdSaveRecord
     End If
End Sub

In this manner you achieve full control over edited form and prevent all sorts of situations that cause errors. So basically with this gizmo, there are only two ways out of a form (assume single record) that your user began editing. You either save the edit with a Save button or Undo the work. You need to work in a subform ? Finish what you are doing in the Parent form! Don't mess around!
If you have navigation buttons with macros (GoPrev, Next, etc) make sure they are disabled when Me.Dirty = True. Do the same for the Exit form button. (Fire a message telling the user that to leave the form the edited record must be saved or cleared).

Best,
Jiri
 
Last edited:
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 01:18
Joined
Sep 21, 2011
Messages
14,221
Thank you Jiri.
As the form is bound to a table I do not have a Save button, but try and use Access default methods.
Not heard of that flag before either.
 
You must log in or register to reply here.

Similar threads

B
Update main form text boxes as subform is filled in? Also, subforms not linking?
Replies
4
Views
410
gemma-the-husky
gemma-the-husky
C
Adding a new record and getting ID
Replies
14
Views
1,096
Pat Hartman
P
H
Solved Problem with Rowsource in Combobox of Continuous Form.
Replies
15
Views
320
HillTJ
H
A
Solved checkbox placed in frame by a query, not responding
Replies
2
Views
200
Pat Hartman
P
D
Run-time error
Replies
4
Views
470
Danny
D

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom