Solved Creating Function To Lock Text Boxes and Combo Boxes When A Form Loads

[Benginner2212]

But, locking the record in the current event doesn't actually solve the problem of bad data. The user could just press the "button" to unlock the form and lose focus and lean on the keyboard and if you have no validation code, the bad data is simply saved. You've gained nothing.

I might decide for whatever reason that for some particular application or just some particular form, I want the additional protection of locking the record. But I ALWAYS have validation code for every condition I can think of. Just about the only fields where validation isn't really possible is fields for free text but you can still ensure that something is entered if the field is required even though you may not be able to determine what might be a valid value for the field.

That is an issue that has been in the back of my mind. And I have added some validation checks in to my database, but the validation checks are mostly to make sure that a field in the record isn't left blank. I don't have the validation checks in the BeforeUpdate Event and after this conversation I will be trying to change that.

I know that there a fields on my records that data entered will never change. For example, the category and number that is assigned to a cable won't change and the type of cable that is installed most likely won't change. But I am running into a issue where my co-workers wanted to leave a cable in an equipment rack in the hopes of being able to reuse the cable instead of physically removing the cable from the racks. So now I have to figure out how to handle what happens if someone wants to reuse the cable number. Hopefully that would be as simple as allowing a user to change the other information besides the category, number and type of cable used.

At this point, I am guessing that I need to figure out to revert any changes that were made to the fields that should never changed after the record is created and put that code in the BeforeUpdate event on the form as the primary safeguard against recording bad data.

 

[Pat Hartman]

The form's BeforeUpdate event is the LAST event that runs before a record gets saved. Think of it as the flapper at the bottom of a funnel. If the funnel is closed (cancel = true), the record can not pass through and be saved. If you don't cancel the update, the record proceeds to be saved. Although there are occasional reasons for using the control's BeforeUpdate or Change event for validation, the primary validation should be in the form's BeforeUpdate event. Also, NO control event can be used to validate whether a control contains data or not since if the focus never enters a control, none of its events would ever run.

Fields that NEVER change should be locked. Or, if you don't lock them, for that particular validation, you could use the Change event. If the record is new, allow entry, otherwise use:
Me.ControlName.Undo to back out the change and display a message.

Personally, I prefer to not have to dig around to see all the validation logic so I just put it all in the BeforeUpdate event. In some cases, I might have additional tests in the control's BeforeUpdate event if for example, I want to prevent a duplicate and give the user an immediate warning so he doesn't finish entering all the data only to find he can't save the record.

You might want to look at these two videos.

Bad Data is Bad for Business #1 and #2

I just added #2 --- that link is further down. It is much shorter than #1. I also added a copy of the database I used for the video. Enjoy:) I created a database to help people to understand how and why to use the Form's BeforeUpdate event to validate data. @Uncle Gizmo and I made a...

www.access-programmers.co.uk

 

[Benginner2212]

The form's BeforeUpdate event is the LAST event that runs before a record gets saved. Think of it as the flapper at the bottom of a funnel. If the funnel is closed (cancel = true), the record can not pass through and be saved. If you don't cancel the update, the record proceeds to be saved. Although there are occasional reasons for using the control's BeforeUpdate or Change event for validation, the primary validation should be in the form's BeforeUpdate event. Also, NO control event can be used to validate whether a control contains data or not since if the focus never enters a control, none of its events would ever run.

Fields that NEVER change should be locked. Or, if you don't lock them, for that particular validation, you could use the Change event. If the record is new, allow entry, otherwise use:
Me.ControlName.Undo to back out the change and display a message.

Personally, I prefer to not have to dig around to see all the validation logic so I just put it all in the BeforeUpdate event. In some cases, I might have additional tests in the control's BeforeUpdate event if for example, I want to prevent a duplicate and give the user an immediate warning so he doesn't finish entering all the data only to find he can't save the record.

You might want to look at these two videos.

Bad Data is Bad for Business #1 and #2

I just added #2 --- that link is further down. It is much shorter than #1. I also added a copy of the database I used for the video. Enjoy:) I created a database to help people to understand how and why to use the Form's BeforeUpdate event to validate data. @Uncle Gizmo and I made a...

www.access-programmers.co.uk

Okay, I was able to watch the two videos that you recommended and I can see what you are saying about having the validation code in the the Before_Update Event and I am working on getting the validation code on my forms moved over to the Before_Update Event on the form. The question that I have at this point is what is the difference between error checking and validation. In my thinking, error checking and validation are the same thing.

Right now, the code that I have in my before_update event is

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim deleteRequest As Integer
Dim errMessage, errTitle As String
errMessage = "Fill In Missing Information"
errTitle = "Critical Information Missing"


        On Error GoTo SaveError
            If Me.cboCableCategory = 0 Then
                MsgBox errMessage, vbOKOnly, errTitle
                Me.cboCableCategory.SetFocus
            ElseIf IsNull(Me.CableNumber) Then
                MsgBox errMessage, vbOKOnly, errTitle
                Me.CableNumber.SetFocus
            ElseIf IsNull(Me.cableType) Then
                MsgBox errMessage, vbOKOnly, errTitle
                Me.cableType.SetFocus
            ElseIf Me.cboCableSource = 0 Then
                MsgBox errMessage, vbOKOnly, errTitle
                Me.cboCableSource.SetFocus
            ElseIf IsNull(Me.srcDescription) Then
                MsgBox errMessage, vbOKOnly, errTitle
                Me.srcDescription.SetFocus
            ElseIf IsNull(drawingNumber) Then
                MsgBox errMessage, vbOKOnly, errTitle
                Me.DrawingNum.SetFocus
            ElseIf Me.cableDestination_PK = 0 Then
                MsgBox errMessage, vbOKOnly, errTitle
                Me.cableDrawingUpdate_PK.SetFocus
            ElseIf Me.cboDestination = 0 Then
                MsgBox errMessage, vbOKOnly, errTitle
                Me.cboDestination.SetFocus
            ElseIf IsNull(Me.dstDescription) Then
                MsgBox errMessage, vbOKOnly, errTitle
                Me.dstDescription.SetFocus
            Else
                Me.ActiveCable.Value = True
                Me.cableRecordLocked.Value = True
'                DoCmd.RunCommand acCmdSaveRecord
                MsgBox "Record Saved", vbOKOnly, "Record Saved"
'                DoCmd.GoToRecord acDataForm, "frmCableInformation", acNewRec
            End If
               
 
       
HandelSaveError:
    Exit Sub
SaveError:
    MsgBox Err.Description
    Resume HandelSaveError
   
End Sub

Which all that does for me is check to make sure that none of the fields are left blank, but beyond that it wouldn't stop a user from entering bad data into the form. The two fields that a never going to change after a record are created are Me.cboCableCategory and Me.cableNumber.

Currently when a record is saved I update the value of a check box and then on the forms current event, I have an if loop that checks the value of the box and if the check box's value is true, a function is called that locks all of the certain fields on the form. If a use wants to edit the record, they click an edit button an that unlocks all of the fields of the form.

Would a better solution be to call a function when the before_update event runs that just locks the fields that I know aren't going to change, never give the user a way to unlock those fields and then use the change_event to check if the user intended to change the other fields?

 

[Pat Hartman]

That code won't do what you want it to do which is to stop the bad data from being saved. It gives you error messages but the bad data still gets saved. try it.

You have to use
Cancel = True
to stop access from saving a record with invalid data.

Error checking and validation are the same thing from opposite perspectives. You check for errors or you check for valid values

 

[Benginner2212]

That code won't do what you want it to do which is to stop the bad data from being saved. It gives you error messages but the bad data still gets saved. try it.

You have to use
Cancel = True
to stop access from saving a record with invalid data.

Error checking and validation are the same thing from opposite perspectives. You check for errors or you check for valid values

Yes, I can see how my current code wouldn't stop bad data from being saved. I keep trying to think of how I would check for bad data and what situations where where bad data would most likely be entered into the form. I think on text boxes that require a description would be pretty easy. I would check to make sure that the data was entered was longer than 3 or four characters long before the form is saved. I am not too worried about the combo boxes on the form because Access kicks back an error if a use tries to enter a value that isn't already an option on the combo box.

The situation when I am not sure how to handle and could use some advice is when the user enters the number that is going to be associated with the cable we are installing. Currently we assign a cable to a category that consists of a 3 letter prefix followed by a 4 digit number. However in the past we have had outside contractors that have installed systems and the contractors method for labeling cable was string of 7 alpha/numeric characters so I am a little worried about what will happen to the old records if I create a validation rule that is based on how we assign cables now.

 

[Pat Hartman]

The key to the validation, as it talks about in the videos, is using the form's BeforeUpdate event unless you have a specific reason to validate earlier, and then using "Cancel = True" to cancel the update. If you do not cancel the update, Access saves the data. You can display messages all you want, they don't stop the bad data from being saved. It is the "Cancel = True" that tells Access to not save the record.

"Intelligent" codes are always a problem. It all comes down to them usually being a violation of first normal form. They mush multiple attributes into a single field which is simply bad practice. However, you YOU assign the values for new cables, you have better control and since the input is done with separate fields, the validation is easier. You don't need to validate existing data so existing values that don't conform shouldn't be a problem. If you might have to enter non-conforming values in the future, it is probably best to do the validation and give the user a warning that the format is non-conforming. Then give him the choice of accepting the value as typed or fixing it.

Here is a sample that generates custom sequence numbers ("intelligent" codes). These codes are NEVER used as the PK. They have a unique index but they are treated as data. That allows them to be changed over time without affecting old values. For example, if you have a table that references a cable with an old format CableNum, then you pick it from a combo, you don't type it in so you never validate except when a record is created.

Custom Sequence Numbers

Two examples of a custom sequence number. One is a complex ID with concatenated parts. The other is just a sequence number for detail items and is not used as a PK. It has a renumber feature. See below

www.access-programmers.co.uk

Another thing to watch for is the default of "AllowZeroLengthStrings" for text data types. This default should always be changed from Yes to No. Here's a sample with code that does that for all text fields in your database. Run it in your BE since it makes table changes that can't be made from the FE.

Useful code AND the effects of using bad column names

This database has several useful code procedures that you can copy into your BE to fix up defaults you don't like: 1. Change the allow ZLS string property to No If you want to make a text field required, it makes no sense at all to have the Allow ZLS property set to Yes. 2. Turn off sub...

www.access-programmers.co.uk

 

[Benginner2212]

Thank you for the examples. They have been really helpful and I am getting a better handle on how to check for bad data. I would like to ask a couple of questions about your code to make sure that I am understanding what is going on

First the line:

If IsNull(Me.cboDeptName.OldValue) Then                     
    Else

will always be false after a record is saved. Because there is no code for the condition first part of the loop access won't touch anything on the record and move on to the second condition

Then the lines:

If Me.cboDeptName = Me.cboDeptName.OldValue Then             
    Else
            MsgBox "Dept Name may not be changed after record is saved.", vbOKOnly
            Cancel = True
            Me.Undo
            Me.cboDeptName.SetFocus
            Exit Sub
        End If

Since the first condition will never be true there is no code for that condition access executes the code for the other condition. This is where the update to the form is canceled and then Me.Undo is reverting any changes that were made to the form?

 

[Pat Hartman]

will always be false after a record is saved.

No. That is not true. The .OldValue always reflects the value (if any) that has been saved. A better name might have been .SavedValue.

When the Current event runs, if Me.NewRecord = True, ALL buffers are null (.Text, .Value, and .OldValue). If Me.NewRecord = False, then .Text is null but the other two are populated with whatever value came from the table so they may be null if the field is null in the table or populated if the field is populated in the table.

When the user starts typing into a control, the characters are typed into the .Text buffer. The .Value and .OldValue are unchanged. When the control loses focus, the characters are copied from .Text to .Value. I believe in current versions of Access, .Text remains as it was after data entry. After the record is saved, the characters are copied from .Value to .OldValue. I don't know what happens with .Text. In earlier versions of Access, the .Text buffer was only accessible when the control had the focus. So, you could refer to the .text property to examine the entered characters in the control's OnChange event but once the control lost focus, you were NOT able to reference .Text unless you first issued a SetFocus command to put focus back into the control you want to access.. That restriction seems to have been lifted so I'm going guess, that the .Text property holds its typed value until after a record is saved. It is probably cleared at that time. Because of this restriction, I almost never used the .Text property because the OnChange event has limited usefulness. It runs once for each character as it types so it tends to be used only to control data entry.

The If statements are out of context so I can't tell what you are talking about.

 

[Benginner2212]

Thank you for the explanation, I think that I am following the code better.

 If IsNull(Me.cboDeptName.OldValue) Then                     
    Else
        If Me.cboDeptName = Me.cboDeptName.OldValue Then        
        Else
            MsgBox "Dept Name may not be changed after record is saved.", vbOKOnly
        Debug.Print Me.cboDeptName
            Cancel = True
            Me.Undo
            Me.cboDeptName.SetFocus
            Exit Sub
        End If
    End If

One question that I still have is the line If IsNull(Me.cboDeptName.OldValue) just checking to see if there was a value saved to the table? If so is that a kind of validation check?

In the event that there is a Dept Name recoreded to a table:

When the form is loaded, any data that is recorded on the table is populated in the corresponding field on the form . In this case, the form loads, the data that is recorded to the cboDeptName is populated on the form in the corresponding field and access moves on to the next part of the loop because the IsNull statement is false. Then if the user doesn't make any changes to cboDeptName field on the form, the If Me.cboDeptName = Me.cboDeptName.OldValue is true because there was no change to the .Text buffer which means that the .Value buffer is unchanged and access exits the loop.

If the users makes a change to the cboDeptName field on the form and user clicks away from the cboDeptName box, the .Text buffer copies the change to the .Value buffer and cboDeptName is no longer equal to the .OldValue buffer. Access displays the message that the Dept Name can't be changed, cancels the update, undoes the change to the cboDeptName, sets the focus back to the cboDeptName field and exits the before update event.

 

[MajP]

IMO this is really bad structure

If something then
  Do nothing
else
  Do something
end if

That is like nails on a chalkboard

Instead

If something else then
  do something
end if

Along with exit subs that adds no value since you are exiting anyways

If not IsNull(Me.cboDeptName.OldValue) Then                     
        If Me.cboDeptName <> Me.cboDeptName.OldValue Then       
            MsgBox "Dept Name may not be changed after record is saved.", vbOKOnly
            Debug.Print Me.cboDeptName
            Cancel = True
            Me.Undo
            Me.cboDeptName.SetFocus
        End If
End If

 

[Pat Hartman]

IMO this is really bad structure

Interesting. I frequently write conditionals using positive questions as the OP did. I especially avoid them when they start with "not". I think it is because I don't think of the code I write as being for myself. I think of it as being for others to read and in general, people have an easier time of following positive logic than following negative logic. They also have a tendency to skip over the leading "not" and so miss the point of the condition. Therefore, unless taking a positive path gets convoluted, that's my style. "Positive Patty"

One question that I still have is the line If IsNull(Me.cboDeptName.OldValue) just checking to see if there was a value saved to the table? If so is that a kind of validation check?

It has to do with the fact that when you compare null to ANYTHING, the answer is Null. It is never True or False.

So If A = Null Then --- will NEVER return true, even when A is actually null. So, If Null = Null -- is never true.

Most people check for null separately. But, you can sometimes get around the issue using a couple of different techniques.

If Me.fldA & "" = Me.fldB & "" Then - By concatenating null with a ZLS (Zero Length String), you end up with a ZLS which is not null and so will produce the expected result when compared.

Null & "" = Null & "" -- will result in True

 

[Benginner2212]

Interesting. I frequently write conditionals using positive questions as the OP did. I especially avoid them when they start with "not". I think it is because I don't think of the code I write as being for myself. I think of it as being for others to read and in general, people have an easier time of following positive logic than following negative logic. They also have a tendency to skip over the leading "not" and so miss the point of the condition. Therefore, unless taking a positive path gets convoluted, that's my style. "Positive Patty"


It has to do with the fact that when you compare null to ANYTHING, the answer is Null. It is never True or False.

So If A = Null Then --- will NEVER return true, even when A is actually null. So, If Null = Null -- is never true.

Most people check for null separately. But, you can sometimes get around the issue using a couple of different techniques.

If Me.fldA & "" = Me.fldB & "" Then - By concatenating null with a ZLS (Zero Length String), you end up with a ZLS which is not null and so will produce the expected result when compared.

Null & "" = Null & "" -- will result in True

Okay, now I see what is going on with the code.

Sorry for the delay in my response, the last couple of weeks at work have been busy.

Thank you Pat for everything. With your help I was able to really mitigate the possibility of critical data accidentally being overwritten.

I'll be marking this thread as solved.

Thanks Again

 

[Pat Hartman]

You are very welcome

With your help I was able to really mitigate the possibility of critical data accidentally being overwritten.

Understanding how Access uses the three control buffers helps a lot.