Detect the next control to get the focus before the OnExit event of the current one

[Kafrin]

So I've got a form set up like this:
~ There's a dropdown box that gives you options from a table to choose from.
~ There's a 'Jump' button to click so that if the option you want isn't listed you can jump to the appropriate form, add a new record, and then come back.
~ The dropdown, which gets the focus whenever the form is opened, has code in its OnExit event to see if it has anything in it. If not (and this isn't a new record), the user gets a message asking if they really want to leave it blank - if yes they continue, if no the Exit of the control is cancelled.

The problem is that if the user is on the dropdown and clicks the button to add a new option, they go through the OnExit event and get the message, which is frustrating when you're clicking the button in order to sort it out!

So, is there any way to tell, when they try to exit the dropdown, that they're going to the button?

Ideally, this doesn't involve code on the button and a public variable to see what the last control was, as I'd then need to add code to the other 40+ controls on the screen.

I know Access may not let you do this. Any thoughts are appreciated!

 

[Mr. B]

I am afraid that to do what you are describing would reaquire some mind reading. Assuming that there are other controls in your form, there is no way to know to which control a user might move the focus.

Have you condsidered using the OnNotInList event of your combo box control to allow the user to add an new value? Using this event, you could create a macro (if you do not want to use VBA code) that would provide the functionality.

Just my thoughts and suggestion.

 

[gemma-the-husky]

why not leave the blank message until right at the end

in the form_beforeupdate you can have the check

if nz(mycbo,0)=0 then
  if msgbox("Are you sure you want to leave the cbo choice blank",vbyesno) = vbno then
     cancel=true
     exit sub
  end if
end if

 

[Kafrin]

Gemma: unfortunately the requirement is that the dropdown is checked even if the user just views the record, they don't have to edit it. That's why the focus starts off on the dropdown. Although there may be something I can do there with setting the record to Dirty=True in the OnCurrent of the form. Hmm...

Mr B: I'm not exactly trying to mind-read - I want to know if the user actually clicked on the button and that's why the dropdown is losing the focus - although Access does seem to treat this as mind-reading. I hadn't used the NotInList event because usually when I do it's to auto-enter a record in the dropdown's table and in this case the table of options actually has quite a lot of fields. It suddenly occurs to me though that I could use that event to hop off and open the form instead...

Thanks guys, you've helped me switch round how I'm looking at this. If anyone has any more thoughts please do still post



[sorry edited by accident - not changed]

 

[gemma-the-husky]

solution to the first bit - use the current event, as you suggested

just test the value in the current event, and deliberately "dirty" the record in some way. if you have record selectors visible, you will be able to check that the record is dirty. Then the before update will click in

current event

if nz(cbocombo,0) = 0 then
   msgbox("The combo box is not selected. Please select a value ")
   cobcombo=0  'maybe this will dirty the record
   cbocombo.setfocus
end if

 

[lelo]

an example ....

Dim ctrl As String
Dim errMsg As String

Private Sub cbDept_GotFocus()

    If ctrl <> "" Then
        If errMsg <> "" Then
            MsgBox errMsg
            errMsg = ""
        End If
    
        Controls(ctrl).SetFocus
        ctrl = ""
    End If

End Sub

Private Sub cbDept_LostFocus()

    Dim ret As String
    
    If ctrl <> "" Then
        Exit Sub
    End If
    
    If IsNull(Me.cbDept.Value) Then
    
        ctrl = Me.cbDept.Name
        Exit Sub
        
    End If
            
    ret = getDeptName(Left(Me.cbDept.Value, 5))
    If ret = "" Then
        
        'MsgBox "部門代碼錯誤，請重新輸入"
        Me.cbDept.Value = ""
    
        errMsg = "部門代碼錯誤，請重新輸入"
        ctrl = Me.cbDept.Name
        
    Else
    
        Me.cbDept.Value = ret
    
    End If


End Sub

Private Sub txtInDate_GotFocus()

    If ctrl <> "" Then
        If errMsg <> "" Then
            MsgBox errMsg
            errMsg = ""
        End If
        
        Controls(ctrl).SetFocus
        ctrl = ""
    End If

End Sub

Private Sub txtInDate_LostFocus()

    Dim idate As Date

    If ctrl <> "" Then
        Exit Sub
    End If

    If IsNull(Me.txtInDate.Value) Then
        Me.txtInDate.Value = ""
    
        ctrl = Me.txtInDate.Name
        Exit Sub
        
    End If

    If Not repDateValidate(Me.txtInDate.Value) Then
    
        'MsgBox "日期錯誤，請重新輸入"
    
        Me.txtInDate.Value = ""
        errMsg = "日期錯誤，請重新輸入"
        ctrl = Me.txtInDate.Name
    
    End If

End Sub

Private Sub cmdCancel_Click()

    ctrl = ""
    errMsg = ""

End Sub

My language is not English, I may not back here ....

 

[DavidAtWork]

Can you not do your OnExit check and if blank ask the user if they would like to add a new record to the list and proceed to open the appropriate form and forget about the jump button.
I don't see any way you can check where a user is about to go when you use the OnExit event of a combobox.

David

 

[gemma-the-husky]

just thinknig about this again

i do this in a couple of ways.

one way is to use the notinlist event to open the new item form. after an item is added the combo box automatically refreshes and positions at the new item

the other way is to have a menubar button (therefore not exiting the combo box) to open the appropriate maintenance form.

I then have combobox.dblclick do a me.requery which refreshes the items in the combo box - and therefore adds the new item.

maybe one of these ideas will help

 

[lelo]

Can you not do your OnExit check and if blank ask the user if they would like to add a new record to the list and proceed to open the appropriate form and forget about the jump button.
I don't see any way you can check where a user is about to go when you use the OnExit event of a combobox.

David

The check is not do in OnExit event, It is do in OnLostFocus event.

I do'nt make a real program. I only present a method. It can cancel the check.