requiring a field in a form

[rgm949]

So I have a form for entering a new client to our database. The table (tblClient) is connected (1tomany) to tblHouseholds. The field in the form for adding to households is a combo box. When closing the form if data has been entered and nothing has been entered in the households field an error shows up: "The Microsoft acces database engine cannot find a record in the table 'Households' with key matching field(s)'HouseholdID'". I know why this shows up, I need help handling the error since the field is required. The household field is the first field when the form is opened so my thought was to add the following code to the lost focus event:

Private Sub Combo293_LostFocus()
If IsNull(Combo293) Then
MsgBox ("Household Field Required")
Combo293.SetFocus
End If
End Sub

This works just the way I want. The problem is if the user opens the form and doesn't enter any data and just tries to close the form, the msgbox shows up a bunch of times and the user has to exit out 2 or 3 times before the form closes. How do I get the onclose function to ignore the lost focus. I tried using the beforeupdate and onerror events but there was too much code and I was getting lost. Any help is appreciated!!

 

[Ranman256]

I do my checks via code (not in the field property)
All in 1 place, easier to change...

usage: say a SAVE_click event...
if IsValidForm() then docmd.openquery "qaAddNewRecord"

Private Function IsValidForm() As Boolean
Dim vMsg
Select Case True
   Case IsNull(dtpWeekOf)
      vMsg = "Date field missing"
   Case IsNull(cboUser)
      vMsg = "Teacher name is missing"
   Case IsNull(cboSubj)
      vMsg = "Subject field is missing"
End Select
If vMsg <> "" Then MsgBox vMsg, vbCritical, "Required Field"
IsValidForm = vMsg = ""
End Function

 

[missinglinq]

...This works just the way I want...

I bet it doesn't...you only think it does! In addition to the problem you've identified...what happens if the user simply ignores Combo293...i.e. simply doesn't enter the Combobox? Your validation will fail!

Validation to ensure that a Control actually has data, before saving a Record, cannot be done on any event tied to that Control, such as the LostFocus event, for this reason.

It can be done by making the underlying Field Required, at the Table level, or if done at the Form level (my preference) in the Form_BeforeUpdate event, the last event to fire before a Record is fired.

If the validation fails, at the Form level, you Cancel the update, pop a Messagebox, telling the user of their omission, and return Focus to the empty Control.

Linq ;0)>

 

[rgm949]

missinglinq,

The user can't ignore the combo293 because it is the first field when the form opens. So trying to move from that field will fire the lostfocus event.

I think I figured out the Before Update:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Combo293) Then
MsgBox ("Household Field is Required")
Cancel = True
End If
End Sub

Private Sub Form_Error(DataErr As Integer, Response As Integer)
Select Case DataErr
Case 2169
Response = acDataErrContinue
End Select
End Sub

Private Sub Form_Undo(Cancel As Integer)
Cancel = True
End Sub

Private Sub Form_Unload(Cancel As Integer)
If MsgBox("Close Form?", vbYesNo) = vbYes Then
Exit Sub
Else
Cancel = True
Combo293.SetFocus
End If
End Sub

I am newish to VBA coding and this gets me as close as I can get. The only problem is if the user enters data other then the household field and then tries to close the form and clicks yes to close form?(meaning that the user doesn't want to save the new client) the before update message fires again before the form closes.

 

[arnelgp]

Remove the code from the lost focust of control.
Add the code to the form's beforeUpdate event.