validate text boxes in a form 1

[infoscion]

Hi:
I am working on a data enrty from. The form includes text boxes and command buttons. After entering the data when the user clicks the "add records" command button the records are added to the underlying table. This is achived through the the execution of the "command" statement.
I was seeking help on how to have message boxes popping up when a text field is left blank. I have done some initial work where the box box pops up but the execution is not halted. As a result there is a error message that pops up associated with the 'sql' statement which says type mismatch because the text field happens to be null.

Any ideas, suggestions or tips on achieving the functionality where the execution halts until a user enters a value. One the text box or text boxes is not null the of the execution of the 'sql' statement commences. Any inputs would be greatly appreciated.
Thanks and Regards,
Info

 

[PHV]

The classical way to enforce validation rules is to play with the Cancel parameter of the BeforeUpdate procedure of the form.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[infoscion]

hi All:
I am trying to validate a set of boxes after the click of a button. Here is the code that I am using to enter the data into an underlying table and trying to validate the boxes after that. Please let me have some ideas. After the user clicks the add button the exceution needs to be halted such that text validation can happen.
Regards,
Info

Private Sub Command11_Click()
'On Error GoTo HandleError
'Call Checkforfillingofideas

Dim strmessage As String

Dim Cn As ADODB.Connection
Dim CmdCommand As New ADODB.Command
'Dim strCon As String
Dim I As Integer
Dim RA As Long
Dim r As CurrentData
Dim db As Object
Dim rs As ADODB.Recordset
Dim rt As ADODB.Recordset
Set Cn = Nothing

Dim strSQL As String

Dim Con As String
'Set strCon = Application.CurrentProject.Connection
'open recordset
Set Cn = New ADODB.Connection


Set rt = New ADODB.Recordset
rt.CursorType = adOpenKeyset
rt.LockType = adLockOptimistic
rt.CursorLocation = adUseClient

Set rs = New ADODB.Recordset
rs.CursorType = adOpenKeyset
rs.LockType = adLockOptimistic
rs.CursorLocation = adUseClient
Set CmdCommand = New ADODB.Command
'open databaseconnection to schedule database

strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\\Desktop\dbform.mdb"


Cn.Open strCon



'SQL to insert a new record into the databse containg the values in the from




If (Not IsNull(Me.Ideas)) And (Not IsNull(Me.timing)) Then

strSQL = "INSERT INTO Table1 ([Idea],[names],[namee],[timing])VALUES('" & Str(Me!Ideas) + "', '" & (Me!names) & "', "

strSQL = strSQL & "'" + (Me!namee) & "', '" + Str(Me!timing) + "');"

MsgBox (strSQL)

End If

msg = "You are about to add the case to the database"
ans = MsgBox(msg, vbYesNo)
If ans = vbNo Then
Me.Ideas.SetFocus
Else
CmdCommand.ActiveConnection = Cn
'set the insert or update sql statement to the command text
CmdCommand.CommandText = strSQL
'execute the command
CmdCommand.CommandType = adCmdText
CmdCommand.Execute
Set CmdCommand = Nothing
Cn.Close
Set Cmn = Nothing
Me.Ideas = ""
Me.names = ""
Me.namee = ""
Me!timing = ""
End If






End Sub







Private Sub Command11_GotFocus()

End Sub




Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.Ideas) And Not IsNull(Me.names) Then
MsgBox " the ideas box is emptuy and you needd to enter a avlue in there"
Me.Ideas.SetFocus
Me.namee.Enabled = False
Me.names.Enabled = False
Me.timing.enabaled = False
Cancel = True
End If
If IsNull(Me.names) And (Not IsNull(Me.Ideas)) Then
MsgBox "you need to enter the right value for the names field"
Me.names.SetFocus
Me.timing.Enabled.false
Me.namee.enabaled.false
Cancel = True

End If
End Sub

 

[willir]

Here is the code that I am using to enter the data into an underlying table and trying to validate the boxes after that[/quote]

Enter data into the table? ...and then validate?

Do you mean they enter the data info a form?

You have to check / validate the data first. Then commit the record or abort.

If you are do this with code...
- you need to know what fields / controls on the form are to be checked
- you need to know what is acceptable data

In the following, I am just going to check to make sure there is data in the control.

Dim booOK as Boolean, strErrMsg as String

booOK = True
strErrMsg = ""

If Len(Nz(Me.FirstTextBox, "")) = 0 Then
    booOK = False
    strErrMsg = strErrMsg & "No data in Me.FirstTextBox" & vbCRLF   
End If

If Len(Nz(Me.FirstCombTextBox, "")) = 0 Then
    booOK = False
    strErrMsg = strErrMsg & "No data in Me.FirstCombTextBox" & vbCRLF 
End If

If Nz(Me.FirstNumericTextBox, 0) = 0 Then
    booOK = False
    strErrMsg = strErrMsg & "No data in Me.FirstNumericTextBox" & vbCRLF 
End If

If Not IsDate(Me.ADateField) Then
    booOK = False
    strErrMsg = strErrMsg & "No data in Me.ADateField" & vbCRLF 
End If

'Add more checks as required

If booOK Then
   'Data is okay, Update your record
Else
   'Data is missing, display error message
   MsgBox strErrMsg

   'If using the BeforeUpdate event procedure
   Cancel = Ture
End If

Richard