On click event & If Statements

[Jonny45wakey]

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

 

[Minty]

Have a read of this thread

Solved - Validate required and close form

Hello experts I manage to write something by myself... :rolleyes: now I need your opinion if what I make is correct or not?:unsure: I have some validation funcion in module... What i Want is that. 1. user opens and close the form...nothing happen..no saving records 2. users open the form and...

www.access-programmers.co.uk

It contains a number of methods to do what you require. Some simple for starters and some more complicated and elegant.

 

[XPS35]

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.

 

[MajP]

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.

Save Button

Hi Guys, I am new to using access. I have finally after 5 hours created a database and a form. All my combo Box and other necessary buttons are all good. But I want the user form to have a save button. Please anyone can provide me the basic step. The user form feeds the database...

www.access-programmers.co.uk

 

[Gasman]

Give your controls meaningful names as well.
Command133 is not going to mean squat, 6 months down the line.

 

[Pat Hartman]

@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.

 

[Minty]

@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.

 

[arnelgp]

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