Required Field in a Table

[mohammadagul]

I have a field called description In a table. It is set as required, when a user leave this field blanc in a form, a message stating that is field cannot be null.

What I did was that I placed “Validate” in the tag progerty of the control(s) . Crate a txtbox named backcolor (and set its default value to 16777214[white]) and insert the following into the save button event procedure.

Private Sub cmdSave_Click()
Dim currctl As Integer, numctls As Integer

Dim ctl As Control
numctls = Screen.ActiveForm.Count



For currctl = 0 To numctls - 1
Set ctl = Me(currctl)
If ctl.Tag = "Validate" Then
If IsNull(ctl) Then
MsgBox "Please Enter", vbOKOnly, "Enter"
ctl.BackColor = 255
ctl.SetFocus
Exit For
Else
If Not IsNull(ctl) Then
ctl.BackColor = Me!BackColor.BackColor
End If
End If

Next currctl
End If

End Sub

Docmd.doMenuItem acFormBar, acRecordsMenu, acSaveRecord,,acManuVer70

What I was thinking that this will look at all controls and trun an empty control/field red if it is not filled in and abordt save until it is, but unfortunately it is giving a compiliation error. Evertime the comes as follows

Next without For

Where as there is For and there Exit For and There is also next statement. What am I doing wrong here.
Please Help

 

[WayneRyan]

m,

Private Sub cmdSave_Click()
Dim currctl As Integer, numctls As Integer

Dim ctl As Control
numctls = Screen.ActiveForm.Count

For currctl = 0 To numctls - 1
   Set ctl = Me(currctl)
   If ctl.Tag = "Validate" Then
      If IsNull(ctl) Then
         MsgBox "Please Enter", vbOKOnly, "Enter"
         ctl.BackColor = 255
         ctl.SetFocus
         Exit For
      Else
         If Not IsNull(ctl) Then
            ctl.BackColor = Me!BackColor.BackColor
         End If
      End If

Next currctl <-- Swap this line and the next one
End If

End Sub

Wayne

 

[mohammadagul]

Tried your Suggestion but same thing. it is giving the same error

 

[mohammadagul]

Any Suggestion friends.. I really need this code to work

 

[Pat Hartman]

There are a number of things wrong with the procedure. For starters, all editing code should be placed in the FORM's BeforeUpdate event not your save button's click event. The reason being, you want the edit code to execute no matter how the user exits the record. Your code will only run if the user saves the record. What happens if he uses the navigation buttons to scroll to a different record? What happens if he closes the form? etc. The edit code MUST cancel the update if an error is found. Otherwise there is no point in editing. To cancel the update from the BeforeUpdate event, just use - Cancel = True

It is not necessary to use two if's to test IsNull() and Not IsNull(). If the first if is false the second if is true so it is redundant.

Don't use the A95 menu commands to save records. Use the recommended RunCommand method:
DoCmd.RunCommand acCmdSaveRecord

 

[mohammadagul]

thankyou

Any more suggestions for the code.