A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Prevent Blank Records being written. Need Help.



 
 
Thread Tools Display Modes
  #1  
Old December 28th, 2004, 08:45 PM
Robert Nusz @ DPS
external usenet poster
 
Posts: n/a
Default Prevent Blank Records being written. Need Help.

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
  #2  
Old December 28th, 2004, 09:59 PM
Bruce
external usenet poster
 
Posts: n/a
Default

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

  #3  
Old December 28th, 2004, 10:27 PM
Robert Nusz @ DPS
external usenet poster
 
Posts: n/a
Default

Bruce, Thanks for responding but there are two tables. Table A, Main Auto,
CASE_NUM is primary key, has driver of primary car, and other information.
Table B, is 2nd table, uses CASE_NUM from Table A as foreign key, in addition
to driver number for secondary vehicles. So you have 1 Table A record, then
you can have multiple Table B records with same CASE_NUM as part of the key,
always starting with driver 2, to next record 3, to next record 4, etc. so
table A key of 04-000001 has table B 04-00000102, 04-00000103, 04-00000104,
etc. records get written correctly except when exiting or returning to
primary screen, writes empty record 04-00000105, because 05 is being applied
to record key number, sets update flag. You can have up to three people with
address, city, state, zip code per table B record type, but you may not have
a driver, just an owner, or maybe a driver and an interested party. Record
is being built to keep track of people who were involved in accidents, that
want to have departmental review of what happened, so blame can be resolved,
some people want to attend meeting others dont. I just need to know how to
test for no driver data or no owner data or no interested party data in the
record so to prevent blank record being written.

Hope this helps rather than confuses the issue.

Robert

"Bruce" wrote:

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

  #4  
Old December 28th, 2004, 11:22 PM
tina
external usenet poster
 
Posts: n/a
Default

your nested If statements say (in sum) "If ALL the named fields are null, I
don't want to save this record." but the line

Cancel = True

does nothing, because you can't cancel the command button's Click event.

if you only want to discard the record when ALL of the named fields are
null, then try this

With Me
If IsNull(.MyFieldA) And IsNull(.MyFieldB) _
And IsNull(.MyFieldC) And IsNull(.MyFieldD) Then
.Undo
End With

DoCmd.Close

note: the above code will allow the record to be written if even one of the
specified fields is NOT null. if you want to discard the record when even
*one* of the named fields is null, change the "AND"s to "OR"s. though,
instead, you might want to offer the user the choice of either entering the
missing information, or discarding the record.

also, if multiple records may be entered on this secondary form, the above
code will not check all the "new" records entered, only the record that is
"current" when the Close button is clicked.

hth


"Robert Nusz @ DPS" wrote in
message ...
Bruce, Thanks for responding but there are two tables. Table A, Main

Auto,
CASE_NUM is primary key, has driver of primary car, and other information.
Table B, is 2nd table, uses CASE_NUM from Table A as foreign key, in

addition
to driver number for secondary vehicles. So you have 1 Table A record,

then
you can have multiple Table B records with same CASE_NUM as part of the

key,
always starting with driver 2, to next record 3, to next record 4, etc.

so
table A key of 04-000001 has table B 04-00000102, 04-00000103,

04-00000104,
etc. records get written correctly except when exiting or returning to
primary screen, writes empty record 04-00000105, because 05 is being

applied
to record key number, sets update flag. You can have up to three people

with
address, city, state, zip code per table B record type, but you may not

have
a driver, just an owner, or maybe a driver and an interested party.

Record
is being built to keep track of people who were involved in accidents,

that
want to have departmental review of what happened, so blame can be

resolved,
some people want to attend meeting others dont. I just need to know how

to
test for no driver data or no owner data or no interested party data in

the
record so to prevent blank record being written.

Hope this helps rather than confuses the issue.

Robert

"Bruce" wrote:

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



  #5  
Old December 29th, 2004, 06:15 PM
Bruce
external usenet poster
 
Posts: n/a
Default

You don't say whether your PK is an autonumber or otherwise, but if you want
a number to increment without gaps you are much better off using the Dmax
function, perhaps compined with the Nz function. If the PK for tblMain is
MainID, its data type could be set to Number (long integer would probably be
safest). On the form, the text box that has as its control source MainID
could have a default value something like =Nz(Dmax("MainID","tblMain"))+1.
If you set the format to "00000" then the first record would be 00001, the
second 00002, and so forth. Or another text box (txtCaseNo) could contain a
default value something like =Format(Date(),"yy") & "-" &
Format("MainID","00000") to generate 04-00001, 04-00002, etc. A subform
based on the second table could contain a text box that concatenates
txtCaseNo & another incremented number (format "00"). The subform's On
Current event could check to see if the previous record's FK is different
than the current record's. If it is, the incremented "00" number from the
subform could be set to "01"; otherwise it could just be the next number in
the sequence. In other words, the first record in tblMain could be 04-00001,
and the first related record in the second table could be 04-0000101,
followed by 04-0000102, etc. The second record in the main form would be
04-00002. Without resetting the "00" the first related record in the second
table would be 04-0000203 (rather than 04-0000201).
This is just the general idea of how you could automate this if you wish. I
would suggest automation rather than manually entering numbers if it is
possible. Much easier on the user, for one thing. With a subform I think it
would be easier to avoid blank records. The system I suggested would also
need a way to start over at 00001 when the year changes, but again, code in
the main form's On Current event could check for that.

"Robert Nusz @ DPS" wrote:

Bruce, Thanks for responding but there are two tables. Table A, Main Auto,
CASE_NUM is primary key, has driver of primary car, and other information.
Table B, is 2nd table, uses CASE_NUM from Table A as foreign key, in addition
to driver number for secondary vehicles. So you have 1 Table A record, then
you can have multiple Table B records with same CASE_NUM as part of the key,
always starting with driver 2, to next record 3, to next record 4, etc. so
table A key of 04-000001 has table B 04-00000102, 04-00000103, 04-00000104,
etc. records get written correctly except when exiting or returning to
primary screen, writes empty record 04-00000105, because 05 is being applied
to record key number, sets update flag. You can have up to three people with
address, city, state, zip code per table B record type, but you may not have
a driver, just an owner, or maybe a driver and an interested party. Record
is being built to keep track of people who were involved in accidents, that
want to have departmental review of what happened, so blame can be resolved,
some people want to attend meeting others dont. I just need to know how to
test for no driver data or no owner data or no interested party data in the
record so to prevent blank record being written.

Hope this helps rather than confuses the issue.

Robert

"Bruce" wrote:

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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
select records by blank criteria Gerry Vaughan Running & Setting Up Queries 1 October 7th, 2004 06:18 PM
select records by blank criteria Gerry. Vaughan Running & Setting Up Queries 1 October 7th, 2004 02:10 PM
how to prevent null records from displaying in report? Carter Johnson General Discussion 4 September 19th, 2004 05:15 PM
Unique Values vs Unique Records Miaplacidus Running & Setting Up Queries 1 September 17th, 2004 08:24 PM
How do I prevent Excel from auto-opening a blank workbook at startup? Luda Setting up and Configuration 5 December 29th, 2003 10:19 PM


All times are GMT +1. The time now is 01:20 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.