Evaluating Controls with If IsNull

[Talismanic]

I have a form that tracks conversations between employees and superintendants at our company. I am trying to find a way to make sure that all three fields in the form are filled in before a new record is added. I have tried this a few different ways but can not get it to work. This is the last thing I tried:

Private Sub cmdNewRecord_Click()

If IsNull(Me.Conversation) Then
Beep
MsgBox "You can not leave the conversation box blank"
DoCmd.GoToControl "Conversation"
End If

If IsNull(Me.EmployeeName) Then
Beep
MsgBox "You can not leave the employee name box blank"

DoCmd.GoToControl "EmployeeName"
End If

If IsNull(Me.OfficeName) Then
Beep
MsgBox "You can not leave the office name box blank"

DoCmd.GoToControl "OfficeName"

End If

DoCmd.GoToRecord acForm, "EmpContactLog", acNewRec ' Starts new record.

End Sub

Any ideas?

 

[Former]

Could you not set the table fields that you are saving to 'required'. You can do this in design view of the table. The form will then not be able to move on until something is put in each field.

[This message has been edited by Former (edited 10-16-2000).]

 

[Talismanic]

I had thought about doing it that way but there is another form attached to the table that does not require all fields to be filled in.

 

[Former]

Hi Talismanic,

Try restucturing you code like this
...

Private Sub cmdNewRecord_Click()

dim bProcOK as boolean

set bProcOK = true

If IsNull(Me.Conversation) Then
Beep
MsgBox "You can not leave the conversation box blank"
DoCmd.GoToControl "Conversation"
bProcOK = False
Else
If IsNull(Me.EmployeeName) Then
Beep
MsgBox "You can not leave the employee name box blank"
DoCmd.GoToControl "EmployeeName"
bProcOK = False
Else
If IsNull(Me.OfficeName) Then
Beep
MsgBox "You can not leave the office name box blank"
DoCmd.GoToControl "OfficeName"
bProcOK = False
End If
End If
End If

if bProcOK
DoCmd.GoToRecord acForm, "EmpContactLog", acNewRec ' Starts new record.
end if

End Sub

This will only fire the new record if all fields are full.

Hope this helps

 

[Talismanic]

I get an error that says object required and then it highlights this:

Set bProcOk = True

I am using Access 97 instead of 2000, is this something that will not work with A97?

The only thing I altered was the line that says

If bProcOK
DoCmd.GotoRecord acForm, "Emp....
End If

I put a Then on the end of the If line.


Thanks


[This message has been edited by Talismanic (edited 10-16-2000).]

 

[chrismcbride]

If you put your form validation in the Before_Update event of your form, you will have the Cancel Property to help you. Once Cancel is set to true, the normal results of the Update Method practised by Access are prevented.
For Example you can

If IsNull(field_in_question) = true then
MsgBox ("What ever text")
Cancel = True
Me.field_in_question.SetFocus
Exit Sub
End if

I hope this is helpful
Good Luck
Chris

 

[Former]

Hi Talismanic,

Apologies about the dodgy code - you were right to put a 'then' on the end of the statement.

Also get rid of the 'set' in the line that is causing you problems.

However, chrismcbride's answer looks very succinct.

 

[Talismanic]

Thanks, chrismcbride and Former it works now. I ended up removing the Set and that did the trick.

Just out of curiousity, if I were to set it in the before update event of the form. Would the fields have to be filled in a certain order (Tab order)? If not, how would I prevent the user from just filling the first field and then hitting the Add New Record button?