Disallow null value

Jeff H
4StarLounger
Posts: 415
Joined: 31 Oct 2017, 20:07

Disallow null value

Post by Jeff H »

Ok, I think this must be an easy one, but I can't see what's wrong.

I have a combobox where I don't want the user to leave the control null so I put this code in the LostFocus event:

Code: Select all

Private Sub Service_LostFocus()

If IsNull(Me.Service) Then
    MsgBox "Please provide a Type of Service"
    Me.Service.SetFocus
    Exit Sub
End If

Set oCtrl = Me.Service
DeactivateCtrl oCtrl

End Sub
When I tab out of the combobox, I get the message but focus goes to the next control. How can I make the focus stay on the combobox until a value is selected?

One thing that might affect this is that this is a subform which must have one but can have more records and I don't know how to refer to the current record instead of the entire field. The key field (SvcProvidedID) is included in the subform footer, but I don't know how to incorporate it in the reference. What's more, this is the first control for each record in the subform, so upon entry there's no key field ID assigned. That's why I'm trying to localize the code to the active control.

Another thing that might be relevant is that it is not an unbound combobox. The control source is a field (Service) in a table (tblSvcItems).

(FYI, the 'DeactivateCtrl' procedure just removes shading I use to indicate the active control.)

- Jeff

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Disallow null value

Post by HansV »

I'd use the Before Update event of the control. It has an argument Cancel that you can set to True to prevent the user from leaving the control. Leave the DeactivateCtrl part in the On Lost Focues event procedure.

Code: Select all

Private Sub Service_BeforeUpdate(Cancel As Integer)

If IsNull(Me.Service) Then
    MsgBox "Please provide a Type of Service"
    Cancel = True
    Exit Sub
End If

End Sub

Private Sub Service_LostFocus()

Set oCtrl = Me.Service
DeactivateCtrl oCtrl

End Sub
Best wishes,
Hans

Jeff H
4StarLounger
Posts: 415
Joined: 31 Oct 2017, 20:07

Re: Disallow null value

Post by Jeff H »

Unfortunately, I had tried that first with this code:

Code: Select all

If IsNull(Me.Service) Then
    MsgBox "Please provide a Type of Service"
    Me.Service.SetFocus
    Cancel = True
End If
But just now I copied your code into BeforeUpdate and got the same result (focus moves to next control). I tried adding SendKeys "+{Tab}" but that had no effect. In fact, it doesn't even display the message, I guess because there's nothing to update so that event doesn't fire. I've also tried the Exit event to get in front of LostFocus, but still no joy.

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Disallow null value

Post by HansV »

Do NOT use the line Me.Service.SetFocus. You cannot do that in the Before Update event procedure.

Open the form in design view.
Select the Service control.
Activate the Event tab of the Property sheet.
I suspect that the Before Update event is blank.
Click in this event, then select [Event Procedure] from the dropdown list.
Click the builder dots ... to the right of the dropdown arrow.
This should take you to the Before Update event procedure.

Switch back to Access and save the form.
Best wishes,
Hans

Jeff H
4StarLounger
Posts: 415
Joined: 31 Oct 2017, 20:07

Re: Disallow null value

Post by Jeff H »

Yes, I did understand that and I used your code instead of my original code that had Me.Service.SetFocus.

I went through it again just now as you described. BeforeUpdate was not empty in the Property sheet and the code is the following:

Code: Select all

Private Sub Service_BeforeUpdate(Cancel As Integer)

If IsNull(Me.Service) Then
    MsgBox "Please provide a Type of Service"
    Cancel = True
    Exit Sub
End If

End Sub
The BeforeUpdate event is not firing. I added a msgbox in front of the If-Then to check and it didn't run either. I also tried by opening the subform alone and entering a new record, but the BeforeUpdate didn't work. If I select an item in the combo, then it fires.

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Disallow null value

Post by HansV »

Could you attach the database or email it?
Best wishes,
Hans

Jeff H
4StarLounger
Posts: 415
Joined: 31 Oct 2017, 20:07

Re: Disallow null value

Post by Jeff H »

Ugh!! And here I thought this would be an easy one.

The forum app still says the zipped file is too big. I'll email.

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Disallow null value

Post by HansV »

Ah - I see. If you never edit the combo box, the Before Update event won't occur. So you also need to check in the form's Before Update event:

Code: Select all

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If IsNull(Me.Service) Then
        MsgBox "Please provide a Type of Service"
        Me.Service.SetFocus
        Cancel = True
    End If
End Sub
And you could display a warning in the combo box's On Lost Focus event again - but not try to set the focus there; that isn't possible.

Code: Select all

Private Sub Service_BeforeUpdate(Cancel As Integer)
    If IsNull(Me.Service) Then
        MsgBox "Please provide a Type of Service"
        Cancel = True
    End If
End Sub

Private Sub Service_GotFocus()
    Set oCtrl = Me.Service
    ActivateCtrl oCtrl
End Sub

Private Sub Service_LostFocus()
    If IsNull(Me.Service) Then
        MsgBox "Please provide a Type of Service"
    End If
    Set oCtrl = Me.Service
    DeactivateCtrl oCtrl
End Sub
Best wishes,
Hans

Jeff H
4StarLounger
Posts: 415
Joined: 31 Oct 2017, 20:07

Re: Disallow null value

Post by Jeff H »

I don't know if I'm still doing something wrong, but neither SetFocus or Cancel is working in BeforeUpdate on my end. Putting the warning message in BeforeUpdate and LostFocus seems to be ok, but it still moves to the next control.

So I tried adding SendKeys to TimeIn.GotFocus.

Code: Select all

Private Sub TimeIn_GotFocus()
If IsNull(Me.Service) Then
    SendKeys "+{Tab}"
    Exit Sub
End If

Set oCtrl = Me.TimeIn
ActivateCtrl oCtrl

End Sub
That works repeatedly to send the focus back to Service when you tab out. Then I added the same to GotFocus for TimeOut and SvcHours. Oddly, one backward Tab (+{Tab}) works to return to the combobox from those two controls if the user mouse-clicks into them (even though they are 2 and 3 tabs away, respectively).

I don't really care for SendKeys, though. It seems to me I've found it a bit flaky in Excel. But it seems to be working in this context.

What do you think?

- Jeff

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Disallow null value

Post by HansV »

Just to be clear: if you don't select anything in the Type of Service combo box and move to the Time In text box, the Before Update event of the combo box will not occur - that only happens when Type of Service has changed. If you change any other bound control and try to move to a different record, the Before Update event of the (sub)form will occur, and this will check whether the combo box has been filled in.

An alternative would be to set the Required property of the Service field in the tblSvcsProvided table to Yes. Access will then force you to enter a value in the Service field in each record.
Best wishes,
Hans

Jeff H
4StarLounger
Posts: 415
Joined: 31 Oct 2017, 20:07

Re: Disallow null value

Post by Jeff H »

Yes, that does clarify a lot. I see I misread your previous post: I didn't notice you put that code in the FORM BeforeUpdate. :blush:

I will go over all this again tomorrow.

Thanks!!! :cheers: