Form allowing null entry in field

[Blondie96]

I have a form which requires an office name(cbobox). I want it required.

I took a function from the MS resource scheduler which allows one to double click on that field and enter an office where it doesn't already exist. This part works.

If I tab past "Office" when I first enter the form, it allows it (I don't want it to). If I double click to add an office (but don't & just exit that form) When it returns to the main form it requires the "Office" be entered.

I want it to require the office, whether they dblclick & add a new office, or just try to tab by it initially.

The code is:

Private Sub Office_NotInList(NewData As String, Response As Integer)
MsgBox "Double-click this field to add an entry to the list."
Response = acDataErrContinue
End Sub

Private Sub Office_Exit(Cancel As Integer)
'Me.Office.Requery
If IsNull(Me![Office]) Or (Me!Office = "") Then
MsgBox "Office Required"
Cancel = True
End If

End Sub

Private Sub Office_DblClick(Cancel As Integer)

On Error GoTo Err_Office_DblClick
Dim lngOffice As Long

If IsNull(Me![Office]) Then
Me![Office].Text = ""
Else
lngOffice = Me![Office]
Me![Office] = Null
End If
DoCmd.OpenForm "Office", , , , , acDialog, "GotoNew"
Me![Office].Requery
If lngOffice <> 0 Then Me![Office] = lngOffice
Forms!frmScheduleRequest!Office = Me![Office]

Exit_Office_DblClick:
Exit Sub

Err_Office_DblClick:
MsgBox Err.Description
Resume Exit_Office_DblClick
End Sub


The Office form has:

Private Sub Form_Load()
If Me.OpenArgs = "GotoNew" And Not IsNull(Me![Office]) Then
DoCmd.DoMenuItem acFormBar, 3, 0, , acMenuVer70
End If
End Sub


Can someone Help me understand this?

Thanks,
Tamra

 

[TheAceMan1]

How are ya Blondie96 . . . .

Set the [blue]Required[/blue] property in the table.

See Ya! . . . . . .

 

[Blondie96]

I can't set the required property in the table. If I do, the dblclick to enter a new office will not work. I tried that before.

 

[TheAceMan1]

OK Blondie96 . . . . .

In that case, remove the code from [blue]Private Sub Office_DblClick[/blue] and try the following in the [blue]BeforeUpdate[/blue] event of the form:

[blue]   If Trim(Me![Office] & "") = "" Then
      MsgBox "Office Required"
      Cancel = True
      Me!Office.SetFocus
   End If[/blue]

Saving is rolled back if Me!Office fails.

See Ya! . . . . . .

 

[Blondie96]

If I remove the code from the Sub Office_DblClick, it will not allow a new office to be entered if the user dblclicks the office field. it does that now, & I want it to.

What I don't want is for it to bypass the office field if nothing is entered at all.

If it goes thru the dblclick code & returns, then it behaves correctly by requiring an office. But, if it doesn't go thru that code, it allows the field to be left blank. (this is what I'm trying to fix)

 

[TheAceMan1]

Sorry I meant remove from [blue]Private Sub Office_Exit()[/blue].

See Ya! . . . . . .

 

[Blondie96]

I commented out the on_exit, & placed your code in the before update

Results:

Without the on_Exit() code it passes the field w/o data entirely, even if it goes to the dblclick code

 

[TheAceMan1]

Blondie96 . . . . .

The forms [blue]BeforeUpdate[/blue] event doesn't trigger until [blue]you try to save a record[/blue] (click a save button, move to another record, close the form). This when the test for [purple]Me![Office][/purple]. If it fails, saving is rolled back and the focus is set accordingly.

See Ya! . . . . . .

 

[Blondie96]

Oh, Ok. That's why I had it in the On_exit event. I want it to fail and not go past that field if nothing is entered.

Any more ideas?

 

[TheAceMan1]

No . . . . thats about it Blondie96.

[blue]On Exit[/blue] is the best [blue]event trigger[/blue] you can use for your purpose!

As a matter of food for thought, I always perform all my validation in the forms [blue]BeforeUpdate[/blue] event. This allows the user to breeze thru the form unfeathered. Anys errors are caught by the event.

See Ya! . . . . . .