Prevent Blank Records being written. Need Help.

From: Robert Nusz _at_ DPS (_at_)
Date: 12/28/04 

Date: Tue, 28 Dec 2004 11:45:04 -0800

I would like some assistance on this as my testing all fails.

I have two Access forms, one creates Primary (keyed) Auto Accident record
and the 2nd form (if needed, multiple car) creates additional driver record
information for matching case number.

First form works well, assigns case number to keyed record as record index
allowing user to create accident record. When there are multiple cars
involved, a secondary form at time of primary record entry is then worked.
User has command button to open secondary form to create secondary records.
Case number is forwarded to secondary form and is applied as partial key to
secondary record, with detail record count as last half of record key field.
All works well, EXCEPT. If user has Opened the Secondary form to add other
drivers, and created additional driver records successfully, when the users
clicks command button to return to primary screen, the secondary screen
writes a nearly blank record. (Secondary record contains case number and
record sequence number (Driver Number), with the rest of the record blank.
If the user Opens the secondary form, does not create any secondary records,
but uses "RETURN" command to go back to primary record, NO Semi-Null Record
is written. Secondary record format is as follows:

Field name is Case_Num:
8 character text field,
format is @@-@@@@@@
input mask is 00\-000000
Default value is blank
Required = yes.

Field Name is Driver_Numr:
number field
no format requirements
no input mask requirement
required = yes.

Field name is DRIVER_NME:
30 character text field,
format is @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@,
input mask is CCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
default value is blank
required = no
allow zero length = yes.

Field name is DRIVER_ADDR_TXT:
30 character text field,
format is @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@,
input mask is CCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
default value is blank
required = no
allow zero length = yes.

Field name is DRIVER_CITY_TXT:
20 character text field,
format is @@@@@@@@@@@@@@@@@@@@,
input mask is CCCCCCCCCCCCCCCCCCCC
default value is blank
required = no
allow zero length = yes.

Field name is DRIVER_STATE_CDE:
2 character text field,
format is @@,
input mask is CC
default value is blank
required = no
allow zero length = yes.

Field name is DRIVER_ZIP_CDE:
9 character text field,
format is 99999-9999,
input mask is 99999-9999
default value is blank
required = no
allow zero length = yes.

Field name is OWNER_NME:
30 character text field,
format is @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@,
input mask is CCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
default value is blank
required = no
allow zero length = yes.

Field name is OWNER_ADDR_TXT:
30 character text field,
format is @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@,
input mask is CCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
default value is blank
required = no
allow zero length = yes.

Field name is OWNER_CITY_TXT:
20 character text field,
format is @@@@@@@@@@@@@@@@@@@@,
input mask is CCCCCCCCCCCCCCCCCCCC
default value is blank
required = no
allow zero length = yes.

Field name is OWNER_STATE_CDE:
2 character text field,
format is @@,
input mask is CC
default value is blank
required = no
allow zero length = yes.

Field name is OWNER_ZIP_CDE:
9 character text field,
format is 99999-9999,
input mask is 99999-9999
default value is blank
required = no
allow zero length = yes.

Field name is IP_NME:
30 character text field,
format is @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@,
input mask is CCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
default value is blank
required = no
allow zero length = yes.

Field name is IP_ADDR_TXT:
30 character text field,
format is @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@,
input mask is CCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
default value is blank
required = no
allow zero length = yes.

Field name is IP_CITY_TXT
20 character text field,
format is @@@@@@@@@@@@@@@@@@@@,
input mask is CCCCCCCCCCCCCCCCCCCC
default value is blank
required = no
allow zero length = yes.

Field name is IP_STATE_CDE:
2 character text field,
format is @@,
input mask is CC
default value is blank
required = no
allow zero length = yes.

Field name is IP_ZIP_CDE:
9 character text field,
format is 99999-9999,
input mask is 99999-9999
default value is blank
required = no
allow zero length = yes.

If user has not entered DRIVER_NME, DRIVER_ADDR_TXT, DRIVER_CITY_NME,
DRIVER_STATE_CDE, DRIVER_ZIP_CDE or has not entered
   OWNER_NME, OWNER_ADDR_TXT, OWNER_STATE_CDE or OWNER_ZIP_CDE
or has not entered
   IP_NME, IP_ADDR_TXT, IP_CITY_NME, IP_STATE_CDE, IP_ZIP_CDE, then I do not
want to right a BLANK record.

With each record entered, users uses command button to "ADD RECORD" which
writes record, clears screen but calculates next driver number up by 1.
CASE_NUM field remains fixed at what was passed from form 1, DRIVER_NUM is
updated each driver counter by 1, then allows user the enter DRIVER INFO, or
OWNER INFO, or IP (Interested PARTY) Infor or all three bits of info by using
ADD RECORD BUTTON. On click of RETURN BUTTON, if no updates to screen has
been applied, all fields empty except CASE_NUM and DRIVER_NUM, update needs
to be passed, but fails and writes new record. I've attempted to set
Me!DRIVER_NUM to null, CANCEL = TRUE and this fails to pass test. I've tried
the following code as well with failure.

code below:

Private Sub Command31_Return_Click()
On Error GoTo Err_Command31_Return_Click
    
    Dim AllNull As Boolean
    AllNull = False
       
    If (IsNull(Me!DRIVER_NME) = True) Then
     If (IsNull(Me!DRIVER_ADDR_TXT) = True) Then
      If (IsNull(Me!DRIVER_CITY_NME) = True) Then
       If (IsNull(Me!DRIVER_STATE_CDE) = True) Then
        If (IsNull(Me!DRIVER_ZIP_CDE) = True) Then
         If (IsNull(Me!OWNER_NME) = True) Then
          If (IsNull(Me!OWNER_ADDR_TXT) = True) Then
           If (IsNull(Me!OWNER_CITY_NME) = True) Then
            If (IsNull(Me!OWNER_STATE_CDE) = True) Then
             If (IsNull(Me!OWNER_ZIP_CDE) = True) Then
              If (IsNull(Me!IP_NME) = True) Then
               If (IsNull(Me!IP_ADDR_TXT) = True) Then
                If (IsNull(Me!IP_CITY_NME) = True) Then
                 If (IsNull(Me!IP_STATE_CDE) = True) Then
                  If (IsNull(Me!IP_ZIP_CDE) = True) Then
                      Me!DRIVER_NUM = ""
                      Cancel = True
                      AllNull = True
           End If
                 End If
                End If
               End If
              End If
             End If
            End If
           End If
          End If
         End If
        End If
       End If
      End If
     End If
    End If
    
    If AllNull = True Then
        Cancel = True
        AllNull = False
    End If
    
        DoCmd.Close
        
Exit_Command31_Return_Click:
    Exit Sub

Code hits first If statement and falls out, writing record. Any Suggestions?

Thanks,

Robert @ DPS 

-- 
Robert Nusz @ DPS