BeforeUpdate woes

NauticalGent · Feb 5, 2017

Good evening AWF...

I started a thread yesterday: https://www.access-programmers.co.uk/forums/showthread.php?t=291863

And the events are firing as they should...that being said I cant get them to behave the way I want them to.

In a nutshell, I want Access to evaluate each record and force the user to fill in certain fields. To do this is used this code for the Current event:

Code:

Private Sub Form_Current()
    Me.UPD.SetFocus
    If IsNull(Me.UPD) Or IsNull(DEPT) Then
        Form.Dirty = True
    Else
       
    End If
End Sub

This ensures the user cannot move to another record if no changes are made and it is working like a charm.

On the BeforeUpdate event I have this:

Code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim oContr As Control
    For Each oContr In Me.Detail.Controls
        If IsNull(oContr) = True Then
            If MsgBox(oContr.Name & " is empty", vbOKCancel) = vbCancel Then
                Cancel = True: oContr.SetFocus: Exit Sub
            End If
        End If
    Next oContr
End Sub

I borrowed this from an MSDN website when I was at my wits end because my code was not working. Ultimatley, I plan on using the tag property to determine which fields to test for Null.

When I open the form, go to a "bad record" and attempt to leave without populating the fields (ComboBoxs), I get a Runtime Error 2110 "...cant move focus to..."

The debugger has the "Cancel = True: oContr.SetFocus: Exit Sub" highlighted.

I cant believe something as simple as this is so hard...there has to be something simple that I am overlooking.

Any lifelines will be greatly appreciated...

CJ_London · Feb 5, 2017

I've not used colons in code as you have. Not saying it won't work but have you tried

Cancel = True
oContr.SetFocus
Exit Sub

sneuberg · Feb 5, 2017

This might not be related to your problem but I think I'd put another check for control type before checking for null. Something like

Code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim oContr As Control
    For Each oContr In Me.Detail.Controls
      [COLOR="Blue"]  If oContr.ControlType = acTextBox Or oContr.ControlType = acComboBox Then[/COLOR]
            If IsNull(oContr) = True Then
                If MsgBox(oContr.Name & " is empty", vbOKCancel) = vbCancel Then
                    Cancel = True: oContr.SetFocus: Exit Sub
                End If
            End If
        [COLOR="Blue"]End If[/COLOR]
    Next oContr
End Sub

NauticalGent · Feb 5, 2017

CJ_London said:
I've not used colons in code as you have. Not saying it won't work but have you tried

Cancel = True
oContr.SetFocus
Exit Sub

Yes sir I have...it was the method I used before I went looking for a solution online. Same results. Frustrating as all get-out.

missinglinq · Feb 5, 2017

As sneuberg suggested, you have to specify what Type of Control to check, else the Access Gnomes will be checking Controls such as Labels, for Nulls, and since Labels don't have a Value Property, the code will error out.

This won't be necessary, of course, if/when you use the Tag Property to tell Access which Controls to check for Nulls .

Linq ;0)>

sneuberg · Feb 5, 2017

@Missinglinq I didn't know whether checking controls other than textbox and combo box for null would produce errors but I suggested that they be check just in case. My curiousity got to me and I checked this in the attached database and at least labels and command buttons don't cause the code to produce errors. If you check a command button or label for null the answer is apparently false.

Something else must be going on in his form as I couldn't reproduce the error he is getting with the code he posted.

CJ_London · Feb 5, 2017

If you check a command button or label for null the answer is apparently false.

Interesting. I suspect code would fail if you tried testing ctrl.value for null since the value property would not exist for labels and buttons

sneuberg · Feb 6, 2017

CJ_London said:
Interesting. I suspect code would fail if you tried testing ctrl.value for null since the value property would not exist for labels and buttons

Yes that produces Run time error 438, "Object doesn't support this property or method"

NauticalGent · Feb 7, 2017

Well SportsFans, it appears I may have found the issue. I neglected to include the fact that this was a split form.

While trying to find solution to another issue, I came across some posts speaking out about the evils of split forms. I had heard them before but since I wasn't doing anything fancy, I never had an issue.

On a whim, I converted the form to a single form and now the BeforeUpdate codes works like it is supposed to.

Who knew?!?

CJ_London · Feb 7, 2017

you can make you own split form quite easily. Just use a datasheet subform to display the records and in that forms current event put

parent.filter="ID=" & me.ID
parent.filteron=true

Make sure the subform linkchild/master properties are blank

NauticalGent · Feb 7, 2017

I was researching that very topic, appreciate the tip!

BeforeUpdate woes

NauticalGent

Ignore List Poster Boy

CJ_London

Super Moderator

sneuberg

AWF VIP

NauticalGent

Ignore List Poster Boy

missinglinq

AWF VIP

sneuberg

AWF VIP

Attachments

CJ_London

Super Moderator

sneuberg

AWF VIP

NauticalGent

Ignore List Poster Boy

CJ_London

Super Moderator

NauticalGent

Ignore List Poster Boy

Similar threads

Users who are viewing this thread