Determine what control has been clicked while in another control.

[cuylersmith]

I have an Access 2010 form. It has several controls. One control is an exit control accessed by clicking a command box.



I have two data controls that if moved from while blank pop up a message box stating the user must enter something and then returning the focus to that control. The code uses the lost focus event to determine if the control is blank, then sets the timer interval to 1. The lost focus event handles the code to determine that the control is blank. If the contorl is blank the lost focus event sets the timer event to 1. The form timer then handles the message box to the user, resets the timer to 0 and returns the user to the blank control.

The exit control returns the user to the calling form.

The problem is that when the exit command box is clicked when either of the two controls that require a data entry have the focus and are blank the lost focus event occurs before the exit control has the focus. If either of these two controls have the focus and are empty when the exit command is clicked I need a way to not display the message and move on to the code in the exit box. In other words if the exit command is clicked I want to cancel the pop up message and exit the form without showing the message.

Is there a way to do this? I hope I am being clear.

Cuyler Smith

[Missinglinq]

First off, Validation code to determine if a Control has been left empty (Null) simply has to be placed in the Form_BeforeUpdate event! Notice that I said the BeforeUpdate event of the Form, itself, not one associated with a Control.

When checking Controls for Nulls, tying code to events associated with those Controls is useless! All a user has to do, to get around the code, is to simply not enter the Control in the first place; the Validation code wouldn't fire! Placing the Validation code in the Form_BeforeUpdate does three things:

• It eliminates the problem I just spoke about
• It allows the update of the Record to be prevented (aka canceled) until the deficit is corrected
• It eliminates the problem you have, with the LostFocus code firing before and interfering with the exit code.

Here's some sample code of this type of thing; the example checks two Controls, but it can be modified to validate any number of Controls:

Private Sub Form_BeforeUpdate(Cancel As Integer)

 If Nz(Me.Control1,"") = "" Then
   MsgBox "Control1 Must Not Be Left Blank!"
   Cancel = True
   Control1.SetFocus
   Exit Sub
 End If
 
If Nz(Me.Control2, "") = "" Then
   MsgBox "Control2 Must Not Be Left Blank!"
   Cancel = True
   Control2.SetFocus
   Exit Sub
 End If

End Sub

I'm a little fuzzy as to your exit strategy, here. If you want the user to be able to exit the Form and not save the incomplete Record, i.e the Record that has required Fields that are empty, you need to add this line just prior to your code that closes the Form:

Me.Undo

Linq ;0)>

[cuylersmith]

I tried your code and it doesn't seem to work for me. I forgot to mention that each data control is an unbound control. I control in the programming what happens to the data and when. The code I use is checked when the user leave the field and when the <SAVE> button is pressed. If the field is empty at either point the user is requested to enter data or cancel. Here is the code in the "Lost Focus" event:

Private Sub ctlCustName_LostFocus()
If IsNull(ctlCustName) Then
strLabelName = "Customer Name"
Me.TimerInterval = 1
End If

End Sub

Here is the Form Timer event:


Private Sub Form_Timer()
On Error Resume Next

strPrompt = "You MUST ENTER a " & strLabelName & "!" & Chr(10) & Chr(13) & Chr(13) _
& "Click <OK> to enter a " & strLabelName & "." & Chr(10) & Chr(13) _
& Chr(13) & "Click <CANCEL> to return to 'Posting Tickers'."
Response = MsgBox(strPrompt, vbCritical + vbOKCancel, strLabelName & "!")

If Response = vbOK Then

If strLabelName = "Customer Account Number" Then
Me.ctlCustAcctNo.SetFocus
ElseIf strLabelName = "Customer Name" Then
Me.ctlCustName.SetFocus
End If

ElseIf Response = vbCancel Then
Call cmdCancel_Click
End If

Me.TimerInterval = 0
On Error GoTo 0

End Sub

This code is place in the <SAVE> button On Click event again before the AddNew command is issued.

As you can see in this form there are only two controls requiring data.

I think the reason your code doesn't work is because each of these data fields are unbound. When data is save a record souce is open for the underlying table and the data from the control is copied to the appropriate table field. Control is then passed to the calling form and this form is closed.

The Cancel button simple returns to the calling form and closes this form.

Here is a picture of the form:

Form View Design View



I'm not sure what went on. I exited Access and re-entered the program. Things then seemed to work correctly. By the way, when this form is entered the cursor is in the Account Number field. That and the Customer Name field are the two fields requiring data entry. Usually the user will move to the Customer Name field after entering an account number. Neither of these fields can be left blank unless the Cancel button is clicked.

Cuyler Smith

[Missinglinq]

[QUOTE=cuylersmith;159616]

...I forgot to mention that each data control is an unbound control...[/QUOTEA major oversight!

Several developers I know, experienced in Visual Basic database development and Access development, estimate that development, using Unbound Forms, by highly experienced developers, takes twice as long as it does when using Access and Bound Forms.

Your talk of "...control in the programming what happens to the data and when..." is a common excuse for using Unbound Forms, and very seldom a valid one! There is nothing that you have mentioned that cannot be done using Bound Forms, and done much easier! The problems that you're experiencing here just reinforces that!

If you insist on using Unbound Forms, you'd be far better off using a straight VB or C++ front end with a SQL Server or Oracle back end.

• You can create an EXE file which gives total protection to your code/design
  
• You can distribute the db to PCs without a copy of Access being on board
  
• Your data security is far, far better than anything you can do in Access

If you insist on taking this route all I can say is 'Good luck on your project!"

Linq ;0)>

[cuylersmith]

Then I probably need to lear much more about doing things the way you recommend. I am probably approaching a high level of programming skill. I would like to learn a lot more. What sources do you recommend if you don't mind me asking?