RE: Prevent Blank Records being written. Need Help.

From: Bruce (Bruce_at_discussions.microsoft.com)
Date: 12/28/04 

Date: Tue, 28 Dec 2004 12:59:04 -0800

Could you create a table for second vehicles with the PK from the main table
as its FK? It could have its own PK. Your main form, based on the main
table, could have a command button to make the subform (based on the second
table) visible in cases where it is necessary to log a second vehicle. You
could add as many additional records as you like (for third car, etc.). I
amy be missing something, but it seems as if it could do what you need.

"Robert Nusz @ DPS" wrote:

> 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