Form validation and new record?

[Gasman]

Bumping this thread.

 

[moke123]

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]

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

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

J.

 

[Gasman]

That's not what I recommended. I recommended the BeforeInsert event which you seem to be using.

Oops, sorry my mistake.:banghead:

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

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.

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.

Which would be the best Pat? I am keen to learn better ways of carrying out a task

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.

Whicch event would that be Pat? On Delete?

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.

Do you mean present all the errors at once?, well in this case both?

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.

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]

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.

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.

 

[Gasman]

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

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

 

[Solo712]

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

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.

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.

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

 

[Gasman]

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.