Code to check for null field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form that contains 44 fields"; Location 1 (number field), Location 1
RorM (text field combo box value R, M, limit to list field), Location 2
(number field), Location 2 RorM (text field combo box value R, M, limit to
list field), Location3 (number field), Location 3 RorM (text field combo box
value R, M, limit to list field), etc... up to Location 22.

A form cannot be saved if a Location * field is not null or 0 (zero) AND a
cooresponding Location * RorM field is null.

My first thought is to put code in BeforeUpdate to test for the above, but
the following code is already in there. Is there someone else I can perform
this test or can I incorporate code that will test for this in the
BeforeUpdate event along with what is already in there? Thanks.

Current Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Nz(Me!Welded_By_Robot, 0) = 0 Then
Cancel = True 'Don't let the record be saved
Dim strMsg As String 'To assign the message's text
strMsg = "You must choose Yes or No for 1E211 Welds by Robot? " _
& "in order to continue. Click on 'Ok' if you want " _
& "to provide this information and continue, or
click " _
& "'Cancel' to abandon the entire record."
If MsgBox(strMsg, vbOKCancel + vbExclamation, _
"Incomplete Data") = vbCancel Then 'If user response is
"Cancel"
Me.Undo

End If
End If
End Sub
 
I have a form that contains 44 fields"; Location 1 (number field), Location 1
RorM (text field combo box value R, M, limit to list field), Location 2
(number field), Location 2 RorM (text field combo box value R, M, limit to
list field), Location3 (number field), Location 3 RorM (text field combo box
value R, M, limit to list field), etc... up to Location 22.
Click to expand...

Then your table is incorrectly designed. If you have these 44 fields
in your table definition, you would be MUCH MUCH better off splitting
the table into two tables in a one to many relationship. The second
table would have fields for the LoctionNumber, RorM, and Location.
A form cannot be saved if a Location * field is not null or 0 (zero) AND a
cooresponding Location * RorM field is null.

My first thought is to put code in BeforeUpdate to test for the above, but
the following code is already in there. Is there someone else I can perform
this test or can I incorporate code that will test for this in the
BeforeUpdate event along with what is already in there? Thanks.
Click to expand...

If you really want to do this - and I DON'T advise it, you'll have no
end of trouble with this "spreadsheet" table design - you can add code
in the BeforeUpdate to check the controls. you don't say what the
controls are named, but let's say that they're named cboLocation1,
cboLocation2, ... , cboLocation22 and txtRorMLocation1,
txtRorMLocation2 and so on.
Current Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Click to expand...
Dim iLoc As Integer
Dim strCbo As String
Dim strTxt As String
If Nz(Me!Welded_By_Robot, 0) = 0 Then
Cancel = True 'Don't let the record be saved
Dim strMsg As String 'To assign the message's text
strMsg = "You must choose Yes or No for 1E211 Welds by Robot? " _
& "in order to continue. Click on 'Ok' if you want " _
& "to provide this information and continue, or
click " _
& "'Cancel' to abandon the entire record."
If MsgBox(strMsg, vbOKCancel + vbExclamation, _
"Incomplete Data") = vbCancel Then 'If user response is
"Cancel"
Me.Undo

End If
End If
Click to expand...
For iLoc = 1 to 22
strCBO = "cboLocation" & i
strTxt = "txtRorMLocation" & i
If NZ(Me.Controls(strCBO)) = 0 And IsNull(Me.Controls(strTxt)) Then
MsgBox "You must fill in RorM for each location", vbOKOnly
<do whatever you want with the error>
End Sub
Click to expand...


John W. Vinson[MVP]
 
Back
Top