On click event & If Statements

Jonny45wakey

Member
Local time
Today, 20:37
Joined
May 4, 2020
Messages
48
Hi

I have a form with a cmd button "Command133" and when the "on-click" event fires for "Command133" i would like codes executed but struggling to nest each statement.

Below is the current code for the "PaymentTerms" field, if this is blank the msgbox appears and then set focus to field, this should be done for each of the following fields in sequence until they are not blank.

1. [me.paymentterms]
2.[me.margin]
3.[me.contactname]
4.[me.contactno]
5.[me.quotedprice]
6.[me.totaljob]


Private Sub Command 133_Click()
If me.paymentterms = "" then
msgbox "Payment Terms is required!",vbCritical,"Mandatory Field"
cancel = true
me.paymentterms.setfocus
End if


Hope this makes sense?

Any help much appreciated :)

Thanks

Jonny
 
Have a read of this thread

It contains a number of methods to do what you require. Some simple for starters and some more complicated and elegant.
 
That the user has to press a button to check whether he has filled in a field does not sound very logical. If you had to create a button for all mandatory fields, you would end up with a very strange situation.

And what if the user doesn't click the button?

You're better off making the field required in the table or using the before update event at the form level. Then the check is automatically enforced.
 
See demo. Validations multi fields on the forms before update ( That is where it should be done not in a button click). But added a click button as well for demonstration purposes.
 

Attachments

Give your controls meaningful names as well. :(
Command133 is not going to mean squat, 6 months down the line. :)
 
@Minty, I would not suggest that code you linked to. It does not use the correct event to validate the data. @MajP 's suggestion is sound. I tend to not use the simple loop to ensure presence because my validation normally goes far beyond that. Just ensuring a date isn't null is never sufficient. 1/23/223 is a valid date as far as Access is concerned but it is a common typo. You need sanity checks on date fields at a minimum. So, if I have more than a couple of fields that only require "presence" checks, I use the loop code. Otherwise, I just check each field for presence as part of the rest of its validation.
 
@Pat Hartman It was only an example, and you could easily add further checks within it and more descriptive messages (e.g check data type and validate dates as per your example), it was more a principle to avoid continually pestering the end user individually per field, that they missed something or made an error.

If you have 25 required fields I wouldn't want five or six separate message boxes popping up because they were missing or incorrect, especially if I wanted to come back to the record to complete it later, which is an option on that particular form, and hence the choice of event used. The record already exists, it just requires completion.
 
Code:
Dim tfCancel As Boolean

Private Sub Command 133_Click()
Dim arrCtl, arrMsg
Dim i As Integer
arrCtl = Array("paymentterms", "margin", "contactname", "contactno", "quotedprice", "totaljob")
arrMsg = Array("Payment Terms is required!", "Margin is required!", "Contact Name is required!", _
            "Contact Number is required!", "Quoted Price is required!", "Total job is required!")
tfCancel = False
For i = 0 To UBound(arrCtl)
    If Nz(Me(arrCtl(i)), 0) = 0 Then
        tfCancel = False
        MsgBox arrMsg(i), vbCritical, "Mandatory Field"
        Me(arrCtl(i)).SetFocus
    End If
Next

Private Sub Form_BeforeUpdate()
Cancel = tfCancel
End Sub
 

Users who are viewing this thread

Back
Top Bottom