Solved New detail records being added despite disabled button in master form

[Deleted Bruce 182381]

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?

 

[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?

 

[DHookom]

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

 

[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?

 

[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!

 

[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?

 

[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

 

[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.

 

[isladogs]

As a developer, you can prevent end users doing methods such as items 2 & 3 and make it more difficult to do workarounds such as 1.

 

[Ken Sheridan]

Other ways users can circumvent are:

1. Change customer's status to normal, add contract, change customer's status back to deceased.
2. Add contract record directly into the backend table.
3. Pre-date the contract by changing the system date.
4. etc. . .

There are many ways users can trick Access apps. There's no way to make them 100% secure.

1 and 2 would be prevented by applying the CHECK CONSTRAINT I described to the table.

 

[RonPaii]

Other ways users can circumvent are:

1. 
   
2. Pre-date the contract by changing the system date.

There are many ways users can trick Access apps. There's no way to make them 100% secure.

If your computers are joined to an Active Directory domain, they can be configured to get date / time from the server and disable the ability to change the date and time.

 

[KitaYama]

If your computers are joined to an Active Directory domain, they can be configured to get date / time from the server and disable the ability to change the date and time.

Can you share some details about this?
thanks

 

[RonPaii]

Can you share some details about this?
thanks

Note: I contract out for server support but do understand the process.
On the domain control open "Group Policy Management", on your workstation OU configure "NTP Time from DC".

Note: None of my users have local administrator rights on the workstation. This eliminates most of the damage a virus can cause the computer and eliminates most of the "Are You Shure" prompts.

 

[KitaYama]

Note: I contract out for server support but do understand the process.
On the domain control open "Group Policy Management", on your workstation OU configure "NTP Time from DC".

View attachment 122515

Note: None of my users have local administrator rights on the workstation. This eliminates most of the damage a virus can cause the computer and eliminates most of the "Are You Shure" prompts.

Thanks
I was aware of that. I thought you mean something specific to Access.

 

[isladogs]

Whilst users can change the system time, APIs can be used allowing an Access app to compare the system time with the 'Internet time' from a trusted source. Where there is a discrepancy outside a specified tolerance limit, the app can be closed and / or the system time corrected automatically.
And before it is suggested that users could circumvent this by disabling the Internet connection, code can also be included to check for that and close the app if the connection is broken.

 

[RonPaii]

Do you have sample code that does that? I googled your post and it was unable to provide it.

Quick AI search got me to the following x64 code.
1) Net Time: Worked but was slow
2) WMI: Did not work because insufficient rights to make the WMI connection.
3) API: works and is fast.

Provide your Domain controller server name. and replace the logger with your own.

Option Compare Database
Option Explicit

Private Const ModuleName As String = "modADTime"

Private Declare PtrSafe Function NetRemoteTOD Lib "netapi32.dll" ( _
    ByVal UncServerName As LongPtr, _
    ByRef BufferPtr As LongPtr) As Long

Private Declare PtrSafe Function NetApiBufferFree Lib "netapi32.dll" ( _
    ByVal Buffer As LongPtr) As Long

Private Type TIME_OF_DAY_INFO
    todElapsedt As Long
    todMsecs As Long
    todHours As Long
    todMins As Long
    todSecs As Long
    todHunds As Long
    todTimezone As Long
    todInterval As Long
    todDay As Long
    todMonth As Long
    todYear As Long
    todWeekday As Long
End Type

Private Declare PtrSafe Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" ( _
    ByRef Destination As Any, _
    ByVal Source As LongPtr, _
    ByVal Length As Long)

Public Function GetDomainControllerTime(ByVal DCName As String) As Date
    On Error GoTo errGetDomainControllerTime
    Dim pBuffer As LongPtr
    Dim tod As TIME_OF_DAY_INFO
    Dim Result As Long
    
    Result = NetRemoteTOD(StrPtr(DCName), pBuffer)
    If Result = 0 Then
        CopyMemory tod, ByVal pBuffer, Len(tod)
        NetApiBufferFree pBuffer
        GetDomainControllerTime _
                = DateSerial(tod.todYear, tod.todMonth, tod.todDay) _
                + TimeSerial(tod.todHours, tod.todMins, tod.todSecs)
    Else
        Err.Raise Result, _
                  "GetDomainControllerTime", _
                  "Could not get time from DC"
    End If
doneGetDomainControllerTime:
    Exit Function
errGetDomainControllerTime:
    modUtility.Logger ModuleName & ".GetDomainControllerTime", , _
                      Err.Number, Err.Description
    Resume doneGetDomainControllerTime
End Function

 

[RonPaii]

This pub func appears to only runonce. What if a user launches an app while connected to the net, disconnects, and then alters system date? What if users are running the apps totally offline, like several do? Some apps are also single user with backends on the same workstation. Users can also change the system date/time in the CMOS BIOS.

You can trap network disconnects and shut down the application. Set the system time from the server time when the application reconnects. If properly configured the domain controller will reset the time within seconds of it being changed.

If your users are intent to break you company procedures, other than walking them out the door there is only so much you can do.