Before Update Even Not Working 1

[TriniGal]

Hello,

I'm fairly new to VB. I have the following code. It's stopping the user from going forward (adding new files) and thats it. The error is not popping up of the cursor is not going to the field. All I'm getting is a Microsoft Office Access Error "You can't go the the specified record."

Private Sub L_READING_BeforeUpdate(Cancel As Integer)

If Len(Me.L_READING & vbNullString) = 0 Then
        Cancel = True
        MsgBox "Reading is a Required Entry.", vbCritical, "REQUIRED ENTRY"
        Me.L_READING.SetFocus
End If
    
End Sub

Thanks for you time in advance.

 

[RoyVidar]

You are probably wanting to use the forms before update event in stead of the controls before update.

Roy-Vidar

 

[TriniGal]

Do you seed anything wrong with my code thought?

Thanks

 

[PHV]

Private Sub L_READING_BeforeUpdate(Cancel As Integer)
If Trim(Me!L_READING & "") = "" Then
  Cancel = True
  MsgBox "Reading is a Required Entry.", vbCritical, "REQUIRED ENTRY"
End If
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[RoyVidar]

If you move the code to the forms before update event, it should work, as long as the control name is correctly spelled.

The forms before update event will trigger when an attempt to save is made, so if you wish to prevent saving a record where there is no value in the field bound to the control L_READING, the save is prevented.

If you use the controls before update event, it will only be triggered if someone changes the content of that control. The event is fired before the value of the control is saved to the field it is bound to, effectively preventing that, should the validation fail.

But, if there are more controls on the form, one can happily save the records without any value in the L_READING, because unless the value in that control is changed, the event will not fire.

AND - if the before update event of a control is canceled, the focus will not leave the control. That means any attempt at setting focus, will fail, even if you try to set focus to the same control (this is the error you're getting).

So - if you need to use the controls before update event of a control, remove the line setting focus, as the focus will never leave the control if it doesn't pass the validation.

Roy-Vidar

 

[TriniGal]

PHV,

Thanks for your help with the code, its still not working.

Roy,

I'm using the controls before update event because I have another function in the forms before update event. Like I said before, I'm fairly new to VB, and from what I've seen, there is only one function per event, I'm not really sure. Any help would be appreciated. Thank you so much for explaining the control and form before update event.

 

[PHV]

there is only one function per event
In fact there is only one event procedure per event with as many code you want ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TriniGal]

PHV,

This is the code I had

Dim ctl As Control

For Each ctl In Me.Controls
    If ctl.Tag = "Required" Then
        If Len(ctl & vbNullString) = 0 Then
            Cancel = True
            MsgBox ctl.Name & " is a Required Entry.", vbCritical, "REQUIRED ENTRY"
            ctl.SetFocus
            Exit For
        End If
    End If
Next ctl

End Sub

That worked fine, but I had to change it to the following code

Dim varADDRESS As Variant
    
    If Me.NewRecord Then
    
        varADDRESS = DLookup("[ADDRESS]", "LEAKS FOUND", "[ADDRESS] = '" & Me.ADDRESS _
            & "' and [STREET] = '" & Me.LOCATION _
            & "' and [S_COMMUNITY] = '" & Me.S_COMMUNITY & "'")
            
        If Not IsNull(varADDRESS) Then
        
            If MsgBox("This record already exists." & _
                        "Do you want to cancel these changes and go to that record instead?", _
                        vbQuestion + vbYesNo, _
                        "Duplicate Address Found") _
                        = vbYes _
            Then
                Cancel = True
                Me.Undo
                Me.Recordset.FindFirst "[ADDRESS] = '" & varADDRESS & "'"
                bolCheckDuplicate = True
            End If
            
        End If
        
    End If

Exit_LOCATION_BeforeUpdate:
    Exit Sub

Err_LOCATION_BeforeUpdate:
    MsgBox Err.Description
    Resume Exit_LOCATION_BeforeUpdate
    
End Sub

Is there any way I can combine these codes into one under the BeforeUpdate Event of the form?

 

[PHV]

Where is the problem ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TriniGal]

How do I combine them into one code?

I had the second code in a control before update event and it wouldn't fire properly. I changed it to the forms before update event and its working fine. I moved the first code to the L_READING before update even and its not working.

Is it possible to combine the two codes into one and if so, how do I do it?

Thanks so much, I'm sorry I'm if I'm not getting this stuff easily.

 

[PHV]

In the Form's BeforeUpdate event procedure:

Dim ctl As Control
Dim varADDRESS As Variant
If Me.NewRecord Then
    varADDRESS = DLookup("[ADDRESS]", "LEAKS FOUND", "ADDRESS='" & Me.ADDRESS _
      & "' and STREET='" & Me.LOCATION _
      & "' and S_COMMUNITY='" & Me.S_COMMUNITY & "'")
    If Not IsNull(varADDRESS) Then
        If MsgBox("This record already exists. " _
          & "Do you want to cancel these changes and go to that record instead?", _
          vbQuestion + vbYesNo, _
          "Duplicate Address Found") = vbYes Then
            Cancel = True
            Me.Undo
            Me.Recordset.FindFirst "[ADDRESS]='" & varADDRESS & "'"
            bolCheckDuplicate = True '?????
        End If
    End If
End If
For Each ctl In Me.Controls
    If ctl.Tag = "Required" Then
        If Trim(ctl & "") = "" Then
            Cancel = True
            MsgBox ctl.Name & " is a Required Entry.", vbCritical, "REQUIRED ENTRY"
            ctl.SetFocus
            Exit Sub
        End If
    End If
Next ctl

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TriniGal]

PHV,

THANK YOU, THANK YOU, THANK YOU!!!

That worked great. Thank you so much for your patience and help. I have one question though, for future reference, why did you change "LEN" to "TRIM" in the code?

Thank Again.

 

[PHV]

If Trim(ctl & "") = "" Then
The above catch Null, ZeroLength and Space only.

If Len(ctl & vbNullString) = 0 Then
The above catch Null and ZeroLength but not Space only.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TriniGal]

PHV,

Very valuable, I'll try to remember that.

Thanks again.