Solved New detail records being added despite disabled button in master form (1 Viewer)

[BlueSpruce]

I have a customer form that won't enable a button for opening a popup modal form for adding related contracts unless a customer is selected, and the customer's status is normal.

In the customer form's On Current event, I have the following code that locks the Add Contract button if the customer status is not Normal, yet users have been adding new contracts despite the status not being normal. The Add Contract modal form cannot be opened by itself, so I am baffled as to how users have been adding contracts for deceased customers.

Private Sub Form_Current()
  
   On Error GoTo locErrorHandler

   cmdSave.Transparent = True
   cmdUndo.Transparent = True
 
'4/17/2024 Efrain: Checks to see if customer has normal status.  If not then makes ribbon red and disables new contract button

   Dim lngRed As Long, lngGreen As Long
  
   lngRed = RGB(255, 0, 0)
   lngGreen = RGB(35, 183, 77)
    
   If Me.cboCustomerStatusID = 1 Then  'Normal Status
        Me.txtDisplayCustomer.BackColor = lngGreen
        Me.txtDisplayStatus.BackColor = lngGreen
        Me.cmdNewContract.Enabled = True
        
        DoCmd.GoToControl cmdNewContract.Name

   Else        'everything else
        Me.txtDisplayCustomer.BackColor = lngRed
        Me.txtDisplayStatus.BackColor = lngRed
        Me.cmdNewContract.Enabled = False
        
        DoCmd.GoToControl cmdClose.Name

   End If
    
locExitHere:
   Exit Sub
  
locErrorHandler:
   ErrorHandler Me.Name, "frmCustomer, On Current Event"
   If gErrorResponse = 1 Then Resume
   If gErrorResponse = 2 Then Resume Next
   Resume locExitHere
  
End Sub

 

[RonPaii]

if customer is NOT selected then the ID will be NULL. Add NZ to the if.

If NZ(Me.cboCustomerStatusID, 0) = 1 Then 'Normal Status

 

[cheekybuddha]

I'm not sure Ron's suggestion is the answer. If the customer is null you should fall through to the Else anyway.

I'm on my phone and can't see your image very clearly. Are you sure the subform has AllowAdditions property set to False? Could they be adding a new record via the navigation buttons?

 

[BlueSpruce]

if customer is NOT selected then the ID will be NULL. Add NZ to the if.

If NZ(Me.cboCustomerStatusID, 0) = 1 Then 'Normal Status

AllowAdditions property in the subform is set to False. The subform is readonly. Clicking anywhere on a subform row opens the contract form with the selected Contract#. The "Add Contract" button is not enabled unless there's a customer in the On Current event. It's not going to let you add an orphaned Contract without a related customer. The only thing I can think of is bypassing the form and adding a Contract record directly in the table. I have seen users who do that, and other tricks like changing the system date to enter pre and post dated records.

 

[RonPaii]

If the button is in the sub-form, you need to add the sub-form to the reference.

Me.SubFormControlName.Form.cmdNewContact.Enabled = False

 

[XPS35]

Are users able to change the status?

 

[BlueSpruce]

Are users able to change the status?

Yes. If users changed status to 'Normal', added new contracts, then changed status back to 'Deceased', then that would explain the issue.
However, I have a history table that tracks changes to the Customer Profile and I would have noticed that workaround, and they didn't do that.

 

[BlueSpruce]

If the button is in the sub-form, you need to add the sub-form to the reference.

Me.SubFormControlName.Form.cmdNewContact.Enabled = False

The "Add New Contract" button is not in the subform, it's in the parent Customer form.

 

[DHookom]

Do you have contracts without a related customer record? Is there a default value in the Customer field of the contracts table?

 

[BlueSpruce]

Do you have contracts without a related customer record? Is there a default value in the Customer field of the contracts table?

Nope, there's no orphaned contract records in the table, they all have a CustomerID in the foreign key.

The default value for "Status" when adding a new customer is "Normal"

 

[RonPaii]

The only thing I can think of is bypassing the form and adding a Contract record directly in the table. I have seen users who do that, and other tricks like changing the system date to enter pre and post dated records.

How is that possible?
Do the users have direct access to the tables?

 

[BlueSpruce]

How is that possible?
Do the users have direct access to the tables?

Users are clever, the native backend is not password protected.

 

[RonPaii]

Users are clever, the native backend is not password protected.

Then you have a much bigger problem the a button being enabled when it should be disabled. You have no data integrity!

 

[BlueSpruce]

Then you have a much bigger problem the a button being enabled when it should be disabled. You have no data integrity!

The button is properly disabled when it should be.
I repeat, I did not find any orphaned records.

 

[mike60smart]

Users are clever, the native backend is not password protected.

Are you able to upload a copy of the database with no Confidential data?

 

[BlueSpruce]

Are you able to upload a copy of the database with no Confidential data?

I cannot.

 

[mike60smart]

OK
Don't know if the following would help, would changing this line of Code:-

If Me.cboCustomerStatusID = 1 Then 'Normal Status

to

If Me.cboCustomerStatusID.Column(0) = 1 Then 'Normal Status

 

[BlueSpruce]

OK
Don't know if the following would help, would changing this line of Code:-

If Me.cboCustomerStatusID = 1 Then 'Normal Status

to

If Me.cboCustomerStatusID.Column(0) = 1 Then 'Normal Status

My current code is disabling the 'Add New Contract" button, as can be seen in post 14 screenshot, and the history audit table does not show users changing the status to normal in order to add contracts, and then changing status back to not normal.

 

[BlueSpruce]

I found out that users were opening inactive contracts of deceased customers and pressing the "Reproduce" button, which essentially recreates a new contract with the same items(s), loan amount, and allows users to edit the new contract if necessary. The previous developer provided that button so when customers redeemed their items, and they bring them back again, the users would not have to manually enter all the details from scratch.

However, I am questioning the users as to why they would clone inactive contracts from customers who are "decesased" before I go ahead and disable that reproduce button if CustomerStatus is not normal.

 

[Ken Sheridan]

As Ron Paii said in post #13, the fundamental problem is the lack of enforcement of data integrity. Preventing the insertion of a new row in the Contracts table where a Customer is deceased cannot be enforced in the physical model per se, as any attempt to do so would require the deletion of all existing contracts with the deceased customer. However, the Contracts table appears to include an EffectiveDate column. If a DateDeceased column were added to the Customers table, therefore, it would be possible to disallow the insertion of a row into the Contracts table where the EffectiveDate is on or later than the customer's DateDeceased.

The way to do this would be to apply a CHECK CONSTRAINT to the Contracts table. The constraint would require an SQL SELECT statement joining the Customers and Contracts table which would return any rows where the EffectiveDate is on or later than the DateDeceased to be NULL. It would be possible for a user to circumvent this by dropping the constraint of course, but this would need the user to be familiar with building DDL statements, which I'd imagine to be unlikely.