Looking for best practices, Locking dates so they are not changed by code. (1 Viewer)

Chief

Registered User.
Local time
Today, 16:18
Joined
Feb 22, 2012
Messages
156
Hello,

Step 1
1641343598489.png

Create function to hide or show buttons dependent on date field enabled true/false.
These buttons will overlay each other accordingly.
By Default the Date Fields are Enabled = False
! - Lots of buttons to code
? - Can I clean this up to be not so repetitive?
This function to be "Called" in:
Form - Load, Open, anywhere else?

Code:
' Hide/Show Buttons for locking/unlocking dates
Public Function VisLock()

    If TxtCarcassCut_Due.Enabled = False Then
        BtnUnLock_CC.Visible = True
        BtnLock_CC.Visible = False
    If TxtCarcassEdge_Due.Enabled = False Then
        BtnUnLock_CCE.Visible = True
        BtnLock_CCE.Visible = False
    ' If etc.
        ' etc....
    Else
        BtnUnLock_CC.Visible = False
        BtnLock_CC.Visible = True
        BtnUnLock_CCE.Visible = False
        BtnLock_CCE.Visible = True
        ' etc...
    End If
    End If

End Function

The Following is where I am not sure if the code can/should be working off an enabled.true/false senario?

Step 2 (See Step 3, this could be the other way around?)
The Lock Button on click
Makes the corrosponding Date Field.Enabled = True
Enables the user to change the date.
The UnLock Button on click
Makes the corrosponding Date Field.Enabled = False

Step 3 (Problem/Issues)
The Target dates are automatically populated thru a series of complicated code.
When enabling/changing dates, I need to be able to fix these dates into position without them being updated
the next time that code is run.
Also I will need to be able to re-activate/reset all of the target dates to update with the code if necessary.
? - Do I code it to say If Date Field.Enabled = True then ignore update for this field
But then the field is unlocked/enabled
? - Do I have the target date fields.enabled = True by default then if lock button clicked sets the date field.enabled = False
Then if date field.enabled = false don't run update code on those fields.

The Update Code is as follows:

Code:
'------------------------------------------------------------
'Update Dates and Form
'
'------------------------------------------------------------
Private Sub BtnUpdate_Click()
    Dim db As Database
    Dim sql As String, sVal1 As String, sVal2 As String
    Dim con As Object
    Dim rs As Object
    Dim dDate As Date
    Dim iLoop As Integer, iDays As Integer, iAcct As Integer

    Set con = Application.CurrentProject.Connection
    Set db = CurrentDb

    If ChkScheduled = True Then
        Me!CboJobTypeID.Enabled = False
        Me!TxtCustomerPreferredDate.Enabled = False
    Else
        CboJobTypeID.SetFocus
        If CboJobTypeID.text = "" And Nz(Me.Lead_Date, "") = "" Then
            Exit Sub
        End If
        If CboJobTypeID.text = "" Then
            MsgBox "Please select a Job Type from the list", vbInformation, "Select Job Type"
            Exit Sub
        End If
        If (Lead_Date.Value & "" = "") And (ChkIFA_App = True) Then
            Lead_Date.Value = Now()
        End If
    End If

    Me.Refresh
    
    iDays = 0

    If ChkIFA_App = True Then
        sql = "UPDATE JobInfoT SET IFA_Due = #" & addDays(GetDays("IFA_Due"), 2) & "#,SampleSubm_Due = #" & addDays(GetDays("SampleSubm_Due"), 2) & _
        "#,IFC_Due = #" & addDays(GetDays("IFC_Due"), 1) & "#,SetOutDue = #" & addDays(GetDays("SetOutDue"), 1) & _
        "#,CarcassCut_Due = #" & addDays(GetDays("CarcassCut_Due"), 1) & "#,CarcassEdge_Due = #" & addDays(GetDays("CarcassEdge_Due"), 1) & _
        "#,PFBCut_Due = #" & addDays(GetDays("PFBCut_Due"), 1) & "#,PFBEdge_Due = #" & addDays(GetDays("PFBEdge_Due"), 1) & _
        "#,WhiteSatinCut_Due = #" & addDays(GetDays("WhiteSatinCut_Due"), 1) & "#,TwoPakPartsOut_Due = #" & addDays(GetDays("TwoPakPartsOut_Due"), 1) & _
        "#,PickHW_Due = #" & addDays(GetDays("PickHW_Due"), 1) & "#,HingeDrill_Due = #" & addDays(GetDays("HingeDrill_Due"), 1) & _
        "#,MachineShop_Due = #" & addDays(GetDays("MachineShop_Due"), 1) & "#,DrawerAss_Due = #" & addDays(GetDays("DrawerAss_Due"), 1) & _
        "#,AssemblyDue = #" & addDays(GetDays("AssemblyDue"), 1) & "#,TwoPakUnderC_Due = #" & addDays(GetDays("TwoPakUnderC_Due"), 1) & _
        "#,TwoPakPaint_Due = #" & addDays(GetDays("TwoPakPaint_Due"), 1) & "#,WrapQC_Due = #" & addDays(GetDays("WrapQC_Due"), 1) & _
        "#,Delivery_Due = #" & addDays(GetDays("Delivery_Due"), 1) & "# WHERE JobID = " & Me.JobID
    Else
        sql = "UPDATE JobInfoT SET IFA_Due = #" & addDays(GetDays("IFA_Due"), 2) & "#,SampleSubm_Due = #" & addDays(GetDays("SampleSubm_Due"), 2) & _
        "#,IFC_Due = """",SetOutDue = """",CarcassCut_Due = """",CarcassEdge_Due = """",PFBCut_Due = """",PFBEdge_Due = """",WhiteSatinCut_Due = """",TwoPakPartsOut_Due = """", PickHW_Due = """",HingeDrill_Due = """",MachineShop_Due = """",DrawerAss_Due = """",AssemblyDue = """",TwoPakUnderC_Due = """",TwoPakPaint_Due = """",WrapQC_Due = """",Delivery_Due = """" WHERE JobID = " & Me.JobID
    
    End If
    
    db.Execute sql

    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    Me.Refresh

End Sub

Thank you
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 18:18
Joined
Feb 28, 2001
Messages
27,001
The only way to lock something at code level is to always have the code doing the changes test for and honor the lock condition, because code can touch ANYTHING. Offhand, I don't know of a simple way to lock something so that code can't touch it. You can mark something with a flag that says "DON'T touch this" - but I don't know of any code to say "CAN'T touch this."
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 19:18
Joined
Feb 19, 2002
Messages
42,977
It isn't code that you need to lock out, it is users. YOU are in control of the code so don't update things that shouldn't be updated :)
Why are you not using a bound form?
What code is setting the control to Locked?
This is way more complicated than it needs to be.

If you insist on using an unbound form:(, you need to construct the SQL statement using VBA so you can exclude any control that is locked.

Using a bound form, you can prevent changes to controls at the time the user tries to change them. There is no need to lock them ahead of time. Or simply locking the fields en masse, will keep the user from modifying them and so, your update query would simply update the value to what it was originally.

Code:
Private Sub txtSomeFieldName_BeforeUpdate(Cancel As Integer)
    If "some condition" Then
        MsgBox "Some field may not be updated.", vbOKOnly
        Cancel = True
        Exit Sub
    End If
End Sub
 

Isaac

Lifelong Learner
Local time
Today, 16:18
Joined
Mar 14, 2017
Messages
8,738
I'll admit I'm not at all sure I 100% follow the details of what needs to be done, but is this? a case where you could loop through the controls on a form, and, using a combination of an IF statement + naming convention + tags, you could either make a button invisible or visible.

This is aircode (from memory/brain), just showing you the logic ... the nuts and bolts of the syntax you can probably search for and I may have messed up parenthesis, etc.

Code:
dim ctl as control, btn as commandbutton, txtbox as textbox, strName_Suffix as string
dim strName_TextboxDateControl as string, strName_ButtonControl as string

for each ctl in me.controls

    if typename(ctl)="Command Button" then
        set btn=ctl
        if instr(1,btn.tag, "ConditionallyLocked")=true then 'assumes the tag on any button that MIGHT should be locked includes that phrase
            set txtbox = me.controls("txt" & right(btn.name,len(btn.name)-3))
            'above line assumes that you name any date textbox txtSomething, you name any potentially invisible button btnSomething
            'AND that the "Something" text on the two corresponding items (button & textbox it relates to), match
            btn.visible = (txtbox.locked=false) 'if the textbox is not locked, then the button will be made visible, and the reverse
        end if      
    end if

next ctl

This loops through all controls
If the control is a button, it sets the button variable
If the tag contains "ConditionallyLocked", then it continues to evaluate as follows
If the text field (date), whose name matches the name of the button (minus btn at its beginning), then, if the date text box is locked, make the button invisible. (or whatever, switch it how you want).

hope this helps in some way
 

Chief

Registered User.
Local time
Today, 16:18
Joined
Feb 22, 2012
Messages
156
The only way to lock something at code level is to always have the code doing the changes test for and honor the lock condition, because code can touch ANYTHING. Offhand, I don't know of a simple way to lock something so that code can't touch it. You can mark something with a flag that says "DON'T touch this" - but I don't know of any code to say "CAN'T touch this."
Thank you
 

Chief

Registered User.
Local time
Today, 16:18
Joined
Feb 22, 2012
Messages
156
It isn't code that you need to lock out, it is users. YOU are in control of the code so don't update things that shouldn't be updated :)
Why are you not using a bound form?
What code is setting the control to Locked?
This is way more complicated than it needs to be.

If you insist on using an unbound form:(, you need to construct the SQL statement using VBA so you can exclude any control that is locked.

Using a bound form, you can prevent changes to controls at the time the user tries to change them. There is no need to lock them ahead of time. Or simply locking the fields en masse, will keep the user from modifying them and so, your update query would simply update the value to what it was originally.

Code:
Private Sub txtSomeFieldName_BeforeUpdate(Cancel As Integer)
    If "some condition" Then
        MsgBox "Some field may not be updated.", vbOKOnly
        Cancel = True
        Exit Sub
    End If
End Sub
G'Day Pat,

My Form is bound to the main table
1641762658734.png

But there is a lot of information being done when this form opens and it is very slow on the server. (something I do need to improve!)

With the Private Sub BtnUpdate_Click()
Should I add an If statement to control what dates get updated and what don't?

thank you
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 18:18
Joined
Feb 28, 2001
Messages
27,001
Should I add an If statement to control what dates get updated and what don't?

Don't know what Pat will say, but she probably would say "Yes." I know that I am saying yes, that is what I meant. You cannot stop code from making changes unless you give code a way to stop itself. Perhaps that will make the point clearer. If you put a test to see whether you are permitted to change the date, then honor the results of that test, you will be much safer.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 19:18
Joined
Feb 19, 2002
Messages
42,977
But there is a lot of information being done when this form opens
But are you trying to control things that you don't need to control? It shouldn't take a long time to simply open a form.

And yes, I agree with Doc. The way you keep your code from running away is by using If statements to control it.
 

Chief

Registered User.
Local time
Today, 16:18
Joined
Feb 22, 2012
Messages
156
Don't know what Pat will say, but she probably would say "Yes." I know that I am saying yes, that is what I meant. You cannot stop code from making changes unless you give code a way to stop itself. Perhaps that will make the point clearer. If you put a test to see whether you are permitted to change the date, then honor the results of that test, you will be much safer.
Thanks Doc
 

Chief

Registered User.
Local time
Today, 16:18
Joined
Feb 22, 2012
Messages
156
But are you trying to control things that you don't need to control? It shouldn't take a long time to simply open a form.

And yes, I agree with Doc. The way you keep your code from running away is by using If statements to control it.
Yes I am most likely controlling way too much on this form.
But it is old and had things added to the program over the years.
I need time to clean it all up.

Thank you
 

isladogs

MVP / VIP
Local time
Today, 23:18
Joined
Jan 14, 2017
Messages
18,186
An easy way of managing (e.g. Locking) a group of controls is by use the Tag property.

For example, if you have 20 controls that should all be locked, set the tag property of each to e.g. X then apply one line of code
Code:
LockControls True, "X"

For more details and related code, see
Set Controls - Mendip Data Systems (isladogs.co.uk)
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 19:18
Joined
Feb 19, 2002
Messages
42,977
You also need to keep in mind that only ONE control can ever have the focus at one time and on a form in continuous or DS view, only ONE row can ever have focus at one time. So, it isn't always necessary to lock things ahead of time since you can always identify immediately if something has been changed. Sometimes, the simplest solution is simply to trap the on dirty event and prevent the change that way.
 

Users who are viewing this thread

Top Bottom