Cant get sub form to execute code when user leaves sub form.

[Phred]

I have a parent form with three sub forms:

Entity Form = Parent
Address Sub Form = Child
Phone Form = Child
Email Form = Child



The end user enters the entity First Name and Last name and then tabs to the Address Subform. In the address sub form the user can enter several addresses such as Home, Work, etc. The end user must check one of the addresses as "Main" in the Main Check box. See form.



The end user enters all addresses. When the user is done and exits the sub form I want the code to test and make sure one of the Addresses is checked as main. My Query and Code seems to work fine.

Query "TestAddressForMainChk" This works fine when run independently.

SELECT dbo_EntityAddress.MainAddress
FROM dbo_EntityAddress
WHERE (((dbo_EntityAddress.MainAddress)=[Forms]![EntityFRM]![EntityAddressFRM].[Form]![Main]));

The following code executes fine seems to work fine and is captured by the Query.

Private Sub EntityAddressFRM_Exit(Cancel As Integer)
Dim intRecordset As VariantintRecordset = DCount("*", "TestAddressForMainChk")
If intRecordset = 0 Then
   Call MsgBox("One Address must be checked as Main." _
   & vbCrLf & "" _
   & vbCrLf & "Click OK to  exit and check Main Address." _
   , vbExclamation, "Missing Data")
   Exit Sub
End If
End Sub

This is a data quality check. They can enter as many addresses as they want but they must check MAIN for one, before they can exit the address sub form.

The code is currently loaded in Address FORM.

I have tried it On Lost Focus, On Deactivate, ON Unload but none of them are working. So each component works fine. But together on the form they won't work.

What am I missing? I need to do this, or something like it, in many different parts of the DB.

Thanks

Phred

[June7]

MsgBox has two formats, one is just an information popup, the other is a function. When used as a function by incorporating () it must be on the right side of an expression, like:

intResponse = MsgBox("message", vbYesNo, "Missing Data")

Remove the () to invoke as information only:

MsgBox "message", vbExclamation, "Missing Data"

No need for Call.