Dont Move to Next/Save form if any Null field

  • Thread starter Thread starter Irshad Alam
  • Start date Start date
I

Irshad Alam

Please advice a code for the following situation:

Only to applicable for the new records entry:

Dont move/save the record if any of the fields are null (the fields contain
numeric, text, date etc). And move to the cursor to the null fields after a
message prompt, so that user can just type the information.

Regards

Irshad
 
Put a validation rule of Not Null on those fields in your table. Then the
record
cannot be saved unless all fields are complete.
 
I understood, but I have to do the same changes for 25 tables in which more
than 250 fields are there.

Code may have helped me if I may have used on each form opening.

Please advice if any alternate by code

Irshad
 
Here is a function you can put in a standard module and call from any form.
If any control is Null or an empty string, it will return the name of the
first such control it finds. Notice you will need to complete the code for
the types of controls you want check.

Function CheckFormValues(frmCheck As Form) As String
'Pass the name of the OPEN form to check
'Returns vbNullString if no errors found
'Returns the name of the control that fails
Dim ctl As Control
Dim lngCtlCount As Long
Dim lngX As Long

CheckFormValues = vbNullString
lngCtlCount = frmCheck.Controls.Count - 1
For lngX = 0 To lngCtlCount
Set ctl = frmCheck.Controls(lngX)
Select Case frmCheck.Controls(lngX).ControlType
Case Is = acTextBox
If Len(ctl & vbNullString) = 0 Then
CheckFormValues = ctl.Name
Exit For
End If
Case Is = acComboBox
'etc, etc, etc
End Select
Next lngX

End Function
 
Thanks. Will check it out

Regards

Irshad


Klatuu said:
Here is a function you can put in a standard module and call from any form.
If any control is Null or an empty string, it will return the name of the
first such control it finds. Notice you will need to complete the code for
the types of controls you want check.

Function CheckFormValues(frmCheck As Form) As String
'Pass the name of the OPEN form to check
'Returns vbNullString if no errors found
'Returns the name of the control that fails
Dim ctl As Control
Dim lngCtlCount As Long
Dim lngX As Long

CheckFormValues = vbNullString
lngCtlCount = frmCheck.Controls.Count - 1
For lngX = 0 To lngCtlCount
Set ctl = frmCheck.Controls(lngX)
Select Case frmCheck.Controls(lngX).ControlType
Case Is = acTextBox
If Len(ctl & vbNullString) = 0 Then
CheckFormValues = ctl.Name
Exit For
End If
Case Is = acComboBox
'etc, etc, etc
End Select
Next lngX

End Function
Click to expand...
 
Hi Sir,

I checked out the code as below it is producing error, it goes to vba code
and highlight in yellow (the after insert event). the code I used as below :

Private Sub Form_AfterInsert()
CheckFormValues
End Sub

Function CheckFormValues(frmCheck As Form) As String
'Pass the name of the OPEN form to check
'Returns vbNullString if no errors found
'Returns the name of the control that fails
Dim ctl As Control
Dim lngCtlCount As Long
Dim lngX As Long

CheckFormValues = vbNullString
lngCtlCount = frmCheck.Controls.Count - 1
For lngX = 0 To lngCtlCount
Set ctl = frmCheck.Controls(lngX)
Select Case frmCheck.Controls(lngX).ControlType

Case Is = acTextBox
If Len(ctl & vbNullString) = 0 Then
CheckFormValues = ctl.Name
Exit For
End If

End Select
Next lngX

End Function


Please advise.

Regards

Irshad
 
Hi, you never tell me if last code i've sent to you helps you. Code to send a
email message from OE6???

Did work?
 
Back
Top