Solved Check that fields contain values..

[mloucel]

This might be an old thing for all of you..

I have my form and all is working, some records on my table (fname, lname, etc, and some combo boxes generated from a query on the database) MUST have something, and by using on the table the option of (THIS RECORD MUST NOT BE EMPTY [OR REQUIRED = YES]) THAT SHOULD MAGICALLY SOLVE THE PROBLEM , well I imagine wrong, I can navigate from record to record on the form and Access takes the record or saves the record no matter what I said on REQUIRED, so I am toasted, yes is probably not a big deal since the EU can come back and just fill the empty spaces, but here it is my mind thinking a bit ahead of myself..
I figured that some code like:

If IsNull(Me.Location_ID) Then
Me.Location_ID.BackColor = vbRed
MsgBox "Cancelling update"
Me.Location_ID.SetFocus
Else
Me.Location_ID.BackColor = vbWhite
End If

this I IMAGINE could go in the form current

but what if I need to check 4 or 5, what if my end user clicks my SAVE button then do I have to repeat the code, what if it uses the navigation on the bottom..
is there a way to accomplish this, I believe there is..

This is of course not a dead or alive situation, but I would like to make sure that at least the end user fills Location and Certificate type, just to make sure I have at least those..

Thank you GURUS..

Maurice.

 

[missinglinq]

There are a number of ways to approach this, such as

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Nz(Me.Control1,"") = "" Then

MsgBox "Control1 Must Not Be Left Blank!"

Cancel = True

Control1.SetFocus

Exit Sub

End If

If Nz(Me.Control2, "") = "" Then

MsgBox "Control2 Must Not Be Left Blank!"

Cancel = True

Control2.SetFocus

Exit Sub

End If

End Sub

You could loop through some or all Controls and do the same thing. If the number of Controls makes the above too time consuming, given your situation, this will loop through all Textboxes and all Comboboxes and check that they're populated

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim ctl As Control

Dim CName As String

For Each ctl In Me.Controls

Select Case ctl.ControlType

Case acTextBox, acComboBox

If Nz(ctl, "") = "" Then

CName = ctl.Controls(0).Caption

MsgBox "Following field is required: " & vbCrLf & vbCrLf & CName

Cancel = True

ctl.SetFocus

Exit Sub

End If

End Select

Next ctl

End Sub

You could also use the Tag Property to mark certain Controls, and then loop through all Controls but only check on/address the status of these 'marked' Controls.

To set the Tag Property for multiple Controls, all at once:

1. Go into Form Design View
   
2. Holding down <Shift> and Left clicking on each Control in turn.
   
3. Go to Properties – Other and enter Marked in the Tag Property (just like that, no Quotation Marks)
   

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim ctl As Control

Dim CName As String

For Each ctl In Me.Controls

If ctl.Tag = "marked" Then

If Nz(ctl, "") = "" Then

CName = ctl.Controls(0).Caption

MsgBox "Following field is required: " & vbCrLf & vbCrLf & CName

Cancel = True

ctl.SetFocus

Exit Sub

End If

End If

Next ctl

End Sub

As a rule, Validation for a given Control, such as a Textbox, is done in the Control's BeforeUpdate event. This would include such things as making sure Numeric data was entered, if appropriate, rather than Text, or that the data had to contain a certain number of characters, and so forth. If the data fails the Validation, you simply set Cancel = True, which makes the Focus stay on the errant Control until appropriate data is entered.

Validation that involves multiple Controls has to go in the Form_BeforeUpdate event. This includes things such as insuring that a EndDate is later than a StartDate, or verifying that if ControlA is populated, ControlB must also have data, etc.

Validation that one or more Controls actually contain data (is not empty or Null) also has to be done in the Form_BeforeUpdate event. Why? Because Validating that a Control actually has data, using one of that Control's events, is useless; all the user has to do is skip the Control entirely, and none of its events will fire!

Once again, if Validation in the Form_BeforeUpdate event fails, you set Cancel = True, which aborts the Save, and tell the user where they've gone wrong, setting Focus back to the offending Control.

You could simply mark the Fields as 'Required,' either at the Table or Form level, but most experienced developers avoid this, as the error messages Access gives the users, when Required Fields are left empty can be, shall we say, less than helpful! Validating thru the Form_BeforeUpdate event allows you to pop up custom messages that will actually mean something to your users.

Linq ;0)>

 

[mloucel]

Dear;
missinglinq

That's it, I have decided just to check for 3 places, that are absolutely necessary the rest I am sure people will eventually realize they are wrong at printing time and will have to correct it.

Thank you so much, I know I can go thru the simplest one but I like the idea of TAG and I can learn something new that I can reuse in my next project.

Very truly yours..

Maurice.

 

[MajP]

I have been using this from @arnelgp and @oxicottin

Solved - Form Validation using .Tag with highlighted controls on close

Hello, I am trying to validate controls on y form that if their .tag is "Required" and they are null or =0 then highlight the border red and if there is data stay the same. I found a piece of code years ago and am trying to implement it but cant figure out what to change for the below revision...

www.access-programmers.co.uk

It works really well. The code goes in a standard module and it can be used on all forms.

 

[mloucel]

I have been using this from @arnelgp and @oxicottin

Solved - Form Validation using .Tag with highlighted controls on close

Hello, I am trying to validate controls on y form that if their .tag is "Required" and they are null or =0 then highlight the border red and if there is data stay the same. I found a piece of code years ago and am trying to implement it but cant figure out what to change for the below revision...

www.access-programmers.co.uk

It works really well. The code goes in a standard module and it can be used on all forms.

Excellent but it will go way too complicated for what I need, I have saved the page for future reference thou.

Thanks..

 

[missinglinq]

Glad we could help!

Linq ;0)>

 

[mloucel]

There are a number of ways to approach this, such as

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Nz(Me.Control1,"") = "" Then
MsgBox "Control1 Must Not Be Left Blank!"
Cancel = True
Control1.SetFocus
Exit Sub
End If

If Nz(Me.Control2, "") = "" Then
MsgBox "Control2 Must Not Be Left Blank!"
Cancel = True
Control2.SetFocus
Exit Sub
End If
End Sub

Linq ;0)>

Hello again missinglinq, and everyone else, I am very interested in this part of the code, it helps me for a routine I have but now I tried to use it in a different way:

I have a form that the only purpose is to ADD a record (singular) so I added missinglinq's code to LostFocus() Sub
thinking that if the EU leaves the field empty if will trigger the message immediately [what i want] and place the cursor back to the field it came from.
well it works partially, I click, then hit tab (or enter or the arrow) and the message shows up no problem but the pointer never returns to the field; it simply goes to the the next field.

I have no idea why or what to do....
here is my code for reference.

Patient chart is Short Text and the Key to the table
it only contains 3 fields
CHART
FName
LName
that's it..
The form when opens goes directly to NEW RECORD and has no controls whatsoever, so that the EU is forced to either ENTER DATA or CLOSE

Private Sub Patient_Chart_LostFocus()
    If Nz(Patient_Chart, "") = "" Then
        MsgBox "Chart cannot be empty, please correct"
        Me.Patient_Chart.SetFocus
        Exit Sub
    End If
End Sub

Any Help will be appreciated.

 

[missinglinq]

The Form's purpose, whether to Edit a Record or to add a new Record, makes no difference...your code still needs to be in the Form_BeforeUpdate event, just like before!

Linq ;0)>

 

[mloucel]

I did as you advise.. but now I don't even get the message when I move the focus from 1 field to the other, as a matter of fact I get no message at all.
I think I am too perfectionist, BAD HABIT,
so I LEFT it alone as it was, it works sort of, so I will have to leave with it, the only issue I have is that I have a CLOSE button that simply does a docmd.close
but of course as you imagine runs the routine and checks if chart is empty (first field) then displays the message, but after I press OK, the form is closed, nothing is saved and life continues.
I wish I knew how to simply close the form and close but this will defy the purpose of checking if 1 of the fields is empty, there are only 3 fields, really, and is making me crazy, I guess learning is not easy.

I am so sorry for giving you guys so much trouble.

 

[arnelgp]

the code that majp suggest works very well.
it only requires that you put "Required" (wihout qoute) to the Tag
Property of the textbox control on the Form that you
want to validate (not blank).

only one code you need to add, that is in the Unload event of
the Form:

Private Sub Form_Unload(Cancel As Integer)
    Cancel = VerifyAccidentEntryForm(Me)
End Sub

 

[missinglinq]

First off...you're not going to get a message when you 'move the focus from 1 field to the other...' You'll get a message, if appropriate, when you go to Save the Record. You could only get a message when moving from Control to Control, by calling a Procedure of the Control...such as the Control's OnExit Procedure.

The problem with that...is that if the end user simply ignores the Control...i.e. doesn't enter the Control...it won't fire! And EUs are fickle...you can't guarantee that they'll even enter a given Control!

So the Message Box, and other validation code, has to come at the last minute, when a Record is being Saved.

I have a CLOSE button that simply does a docmd.close...runs the routine and checks if chart is empty (first field) then displays the message, but after I press OK, the form is closed, nothing is saved...

You've encountered a long recognized bug that the Boys of Redmond refuse to address! If

DoCmd.Close

is used...and there are 'Required Fields,' or Fields that have Validation code attached...those will be ignored if you Close a Form using this Command! You have to first force a Save...then Close the Form!

Instead of simply using

DoCmd.Close

use

DoCmd.RunCommand acCmdSaveRecord
DoCmd.Close

or

If Me.Dirty Then Me.Dirty = False
DoCmd.Close

This Forces the Save...which causes the code in the Form_BeforeUpdate event to execute...then Closes the Form.

Linq ;0)>

 

[mloucel]

the code that majp suggest works very well.
it only requires that you put "Required" (wihout qoute) to the Tag
Property of the textbox control on the Form that you
want to validate (not blank).

only one code you need to add, that is in the Unload event of
the Form:

Private Sub Form_Unload(Cancel As Integer)
    Cancel = VerifyAccidentEntryForm(Me)
End Sub

Like always you are a master..
I see your code and it works, but sorry left me a bit in the moon..

your code requires my end user to right click and use CLOSE, I have 3 problems to solve..

1) I have a button that says CLOSE so how do I enter that code into that button..
2) What if the EU decides oops I do not want to enter any new patient I rather exit without saving anything..
3) The first field is also a field that the EU has to enter (I sort of figure how to enter that into the code)
That is the #1 field CHART NUMBER ( The end user will enter that MUST)
First name and last name
all those fields entered by the EU.

and I am googling what is dirty since is another brand new command I have no idea existed, (foxbase never had it)

 

[mloucel]

First off...you're not going to get a message when you 'move the focus from 1 field to the other...' You'll get a message, if appropriate, when you go to Save the Record. You could only get a message when moving from Control to Control, by calling a Procedure of the Control...such as the Control's OnExit Procedure.
Linq ;0)>

I see, well that kind of solves part of the problem, so my idea is (i hope I am not wrong)
1 Check that all the fields are not empty (all 3 fields are EU input)
if all fields are empty then exit
if 1 or 2 are in the form then display a message accordingly (I love arnelgp idea) but what if the EU decides naahhh I will just close it and do it later..
do I have to have 2 buttons SAVE and EXIT ?

Maurice.

 

[mloucel]

Hello all:
After a bit of youtube and going back and forth trying to understand each piece of code, I was able to do what I want by using the following code:
Basically checks that there is data
if there is data gives the EU the choice to save it or not
checks that there are no fields left empty and displays a message, Yes is not the message I would love, like You missed the First Name, but it does the Job.
So if you have any suggestion on how can I improve this code it will be much appreciated.

Maurice:

Option Compare Database

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Me.Dirty = True Then
        If MsgBox("do you want to save the changes", _
            vbYesNo + vbQuestion, "Save Changes?") = vbNo Then
            Me.Undo
        End If
        'DoCmd.Close
    End If
End Sub

Private Sub Form_Load()
    DoCmd.GoToRecord , , acNewRec
End Sub

Private Sub tglClose_Click()
    Me.Refresh
    On Error GoTo err_tglClose_click
    DoCmd.RunCommand acCmdSaveRecord
    If Me.Dirty = True Then
        If MsgBox("Do you want to save the changes", _
            vbYesNo + vbQuestion, "Saving a Record...") = vbNo Then
            Me.Undo
        End If
    End If
    DoCmd.Close
exit_tglClose_click:
    Exit Sub
err_tglClose_click:
MsgBox Err.Description
Resume exit_tglClose_click
    
End Sub

 

[jdraw]

Always use Option Explicit as second line at top of module

 

[mloucel]

Always use Option Explicit as second line at top of module

Thanks I had no idea, I'm learning..