Validate using control tag

oxicottin

Learning by pecking away....
Local time
Today, 11:19
Joined
Jun 26, 2007
Messages
889
Hello, I have a form that has a few text boxes and combo boxes and one option group that needs to have data in it before the form can be either changed or closed. I have been reading and it seems you can use a controls tag to validate. I wanted to have an Ok/Cancel message box come up and ask if I wanted to close the form and undo the data I have entered or cancel and finish filling out the form. How can this be done?

Thanks,
Chad
 
For each control on the Form you want to check to ensure data has bee placed there, place a string in each Control's Tag property that describes what the Control is for....for example let's say the Option Group contains Radio Controls (option buttons) for a Type of Injury and the Option Group these control reside in is named Injury Type, well then enter Injury Type into the Tag property for the Option Group control (with no quotation marks).

Then in the Form's BeforeUpdate event:

Code:
Dim Ctrl As Control
Dim Msg As String

[COLOR="DarkGreen"]'If the Form hasn't been edited then why worry about it.[/COLOR]
If Me.Dirty = False Then Exit Sub

[COLOR="DarkGreen"]'Enumerate through the Form's Controls and check those that 
'contain something within the Tag property.[/COLOR]
For Each Ctrl In Me.Controls
   [COLOR="DarkGreen"]'If the control's Tag property contains something then...[/COLOR]
   If Ctrl.Tag <> "" then
      [COLOR="DarkGreen"]'If the Control is our desired Option Group then see if it has a value[/COLOR]
      If Ctrl.ControlType = acOptionGroup And Ctrl.Value = 0 then 
         [COLOR="DarkGreen"]'Nope, it doesn't so set up a Message and get outta this loop.[/COLOR]
         Msg = "You must to select a " & Ctrl.Tag & " before leaving this record."
         Exit For
      End If
      
      [COLOR="DarkGreen"]'Since our other controls are Text Bxes or Combo Boxes, we can check
      'them for Null (nothin entered)[/COLOR]
      If Nz(Ctrl, "") = "" Then
         [COLOR="DarkGreen"]'Nope, no data so set up a message and get outta this loop.[/COLOR]
         Msg = "You must provide data for the " & Ctrl.Tag & " field."
         Exit For
      End If
   End If
Next Ctrl

[COLOR="DarkGreen"]'If there is a Message then we obviously have issues.
'Inform the User, cancel the update and or Form closure.[/COLOR]
If Msg <> "" Then
   MsgBox Msg, vbExclamation, "Insufficient Data Entry"
   Cancel = True
End If

.
 
Good idea Cyberlynx, however are we talking about bound or unbound forms here? Or does it work for both?

In VB there is not a Forms BeforeUpdate Event but it does have QueryOnUnload which Access does not have. So I suppose I could check it there.

David
 
The air code above was written with a Bound Form in mind.

In VB I would attack this in a slightly a different way. As you mentioned, VB Forms do not contain the BeforeUpdate event therefore I would place the code into the Form's Unload event.

A Form Wide Boolean variable would be created so has to hold a Is Dirty flag.

Dim FormIsDirty As Boolean

In the Form's Unload event, I would enumerate through the Form Controls twice. Once to see if a Control has had its Data modified (then exit the loop once one has been found) by referencing the Control's DataChanged property and once determined then the FormIsDirty variable is set to True.

Now knowing the Form is indeed dirty I would enumerate through the Form's Controls a second time and seek out those Controls which have he Tag property set.

It's nice that most VB Controls have the DataChanged property, something Access Controls do not but, not all VB Controls have it and rightly so. For example there is no DataChanged property for Command Buttons, Option Buttons, Frame Controls, and perhaps one or two more. This property works for bound and unbound controls to a data source.

Also to determine the Type of Control VB Forms do not have a ControlType property (at least not that I'm aware of) and the VBA ac Constants don't work. You would need to use the TypeName(Ctrl) function to retrieve the Control Type string.

.
 
Cyberlynx, How could I give the person the option by either undo what has been entered and not even saveing the record or go back an fill the required areas?

I had tried the code and it works to a point. If I miss a required entry it will show the pop up but it still closes the form? I select the X button to close the form and then it trigers the beforeUpdate gives the msg and then closes the form. Should I try the code in the button im using to close the form? Here is the code there...

Code:
Private Sub cmdClose_Click()

'//Closes form
DoCmd.Close acForm, "AccidentEntry"

'/Shows frmSwitchboard again
Forms![frmSwitchboard].Visible = True

'Clears frmSwitchboard search controls

'Clears option group's
Forms!frmSwitchboard!optClassicficationGroup = Null
Forms!frmSwitchboard!optClassicficationSearchGroup = Null

'Clears Controls
Forms!frmSwitchboard!cboFindByEmployeeName = ""
Forms!frmSwitchboard!txtFindDate = ""

End Sub

Thanks,
Chad
 
Last edited:
You would do this in the section where the Message Box is displayed, like this:

Code:
[COLOR="DarkGreen"]'If there is a Message then we obviously have issues.
'Inform the User, cancel the update and or Form closure.[/COLOR]
If Msg <> "" Then
   Msg = Msg & vbCr & vbCr & "Would you like to fill in the above mentioned Field?"    
   If MsgBox(Msg, vbExclamation + vbYesNo, "Insufficient Data Entry") = vbYes Then
      Cancel = True
      Ctrl.SetFocus
   End If
End If

Set Ctrl = Nothing

.
 
It also is closing the form and showing a gray screen and then I have to close the DB and if a option is selected and if an option isnt select it shows the tag name I gave it and then closes the form?
 
Now when I run the code with the last code given I get an error and it taks me to:
Code:
If Ctrl.ControlType = acOptionGroup And Ctrl.Value = 0 then

Here is what I have since you have saw the DB last but I have removed the reports due to the DB size...

Thanks,
Chad
 
Last edited:
It also is closing the form and showing a gray screen and then I have to close the DB and if a option is selected and if an option isnt select it shows the tag name I gave it and then closes the form?

I don't have a clue what you mean by this.

Now when I run the code with the last code given I get an error and it taks me to:

If you tell me what the Error actually is then I could most likely determine real quick what the problem is rather than having to download, decompress, and run the Database. :)

I will look at the Database now and get back to you.

.
 
Ok, if you run the AccidentEntry form with a new record and select a "Classification from the option group you get an error: 438 Object doesnt support this property and if you debug it taks you to.
Note this is only after I have added the code with the yes/no msg in post #6
Code:
If Ctrl.ControlType = acOptionGroup And Ctrl.Value = 0 Then

Here is another thing it does. If you dont select a classification and lets say you select an employee and try to close the form then you will be prompted with the msgbox as expected because it has somthing in its tag and, If you select yes to go back an finish filling out the form then the form still closes and if you select no the form still closes.

Thanks,
Chad
 
Because you have no record navigation within your specific form, the best location for the Code is within the OnUnload event for that Form.

I have placed the Code into a Function named CheckControls which is private to the Form. This function returns Boolean True if the User would like to complete the Record by filling in fields determined as empty and False if not.

The Call to the Function is from within the Form's OnUnload event since the only way to get out of his Form is to close it.

I removed most of the code from the Close button and also placed it into the Form's OnUnload event since this is a better suited spot for it.

Here is your DB back to you.

On another note: When posting a Database for review, please ensure that all development capabilities are available (Tool Bars, menus, etc) so as to work with that Database. Enable the securities when the Database is complete.

.
 

Attachments

Last edited:
Here's something for you...Validation always seems to be a common topic here. :)
Code:
Public Function FieldValidate()
'Place an asterisk (*) in the Tag Property of the text boxes you wish to validate.
'Then in the BeforeUpdate Event of the form, copy/paste the following:
'modified to add Label text to message box instead of field name
'ctl.Controls(0).Caption instead of ctl.Name
'This will make much more sense to a user

Dim msg As String, Style As Integer, Title As String
Dim nl As String, ctl As Control, Source As String, Cancel As Integer, Answer As Variant

nl = vbNewLine & vbNewLine

For Each ctl In Me.Controls

If ctl.ControlType = acTextBox Or ctl.ControlType = acOptionGroup Or ctl.ControlType = acComboBox Then
If ctl.Tag = "*" And Trim(ctl & "") = "" Then
msg = "Data Required for '" & ctl.Controls(0).Caption & "' field" & nl & _
"You can't save this record until this data is provided" & nl & _
"Enter the data and try again . . . "
Style = vbCritical + vbOKOnly
Title = "Required Data..."
MsgBox msg, Style, Title
ctl.SetFocus
Answer = "False"
Me.txtValid = Answer
Cancel = True
  Exit For
    Else: Answer = "True"
    Me.txtValid = Answer
  
        End If
            End If
                Next
                Set ctl = Nothing
End Function
I use an unbound textbox to hold the value... "txtValid"
I call this code from the Before Update event of the form. This doesn't give you the option to "undo" and cancel...but you could easily add that to the message box.
 
Sorry about that I hurried up and deleted personal info ect to get the DB uploaded so it could viewed and I forgot to allow menus ect. I tested the DB and it still isnt working correctly? the only validation that is working is the option group. If I leave everything else empty and tick a radio in the option group then the msg box never comes up and there is text in thier tags on most of the other controls on the form. If I leave the option group empty then yes it does work but again only for the option group. Any Ideas?

Here is a copy with all menus working.

Thanks!
 

Attachments

My apologies :o

I placed A Exit For statement in the wrong location where it checks Option Groups. I moved it up one line into the If/Then procedure.

This was preventing the Function from checking all the other controls once it reached the first Option Group.

A problem still persisted. The Combo Boxes were being ignored. They always contained a ID value of 1. The reason for his was that you had a Default Value (0) placed upon the bound Table Fields for these Check Boxes. In your particular case, there should be no defaults for the Combo Boxes. You want an actual selection to take place. I removed the Default Value from all Table Fields bound to Form Combo Box Controls.

.
 

Attachments

Thanks CyberLynx for resolving the issue I have been trying to figure it out but couldnt. CyberLynx, if I missed a field and the msgbox comes up and asks "Would you like to fill in the above mentioned fieds" and I select no then it goes back to the switchboard. How would I be able to make it so it me.undo the record so it is like it wasnt even entered? Thanks!
 
In the Form's OnUnload event change the line:

Code:
If CheckControls = True Then Cancel = True: Exit Sub

To this:

Code:
If CheckControls = True Then
   Cancel = True
   Exit Sub
Else
   DoCmd.SetWarnings False
   DoCmd.RunCommand acCmdUndo
   DoCmd.SetWarnings True
End If

.
 
Thanks CyberLynx! I cant try it untill monday due to the mdb is at work.... :(
 
Ok I got a chance to insert the code and everything is working correctly except for the undo if the "No" button is selected. It works only if I select a classification and try to close and select no then it doesn’t create the record BUT if I select any one of the combo boxes or type in one of the text fields then it creates the record and the undo don’t work. If I use DoCmd.Delete then it just delets the record. I have also noticed that if I select no and create a new report and look at the record ID its counting in two's. Example, I created a record and its ID is 300 and then I closed that record using the no option then created a new record, now this new record ID would be 302. Thanks!

Code:
Private Sub Form_Unload(Cancel As Integer)

   If CheckControls = True Then
    Cancel = True
     Exit Sub
    Else
    DoCmd.SetWarnings False
    DoCmd.RunCommand acCmdUndo
    DoCmd.SetWarnings True
    End If

   '/Shows frmSwitchboard again
   Forms![frmSwitchboard].Visible = True

   'Clears frmSwitchboard search controls

   'Clears option group's
   Forms!frmSwitchboard!optClassicficationGroup = Null
   Forms!frmSwitchboard!optClassicficationSearchGroup = Null

   'Clears Controls
   Forms!frmSwitchboard!cboFindByEmployeeName = ""
   Forms!frmSwitchboard!txtFindDate = ""

' Turn the MouseWheel On
  Dim blRet As Boolean
  blRet = MouseWheelON
  
End Sub
 
Last edited:

Users who are viewing this thread

Back
Top Bottom