Requiring unbound field entry 1

[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)
Debug.Print "Office---" & [Forms]!frmScheduleRequest![Office] & "*****"
Debug.Print "LEN---" & Len(Nz([Forms]!frmScheduleRequest![Office])) & "****"
If Len(Nz([Forms]!frmScheduleRequest![Office], "") = 0) 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


Before the problem was, no matter what I did I couldn't get the validation to force the entry of the office.
Now, no matter what I choose (cboOffice) for office. I get the message "Office Required". I tested the debug.prints & len > 0 still shows & gets into that if statement. & that is the only location in my code that produces that msgbox.

Can someone Help me understand this?

Thanks,
Tamra

 

[PHV]

Replace this:
If Len(Nz([Forms]!frmScheduleRequest![Office], "") = 0) Then
By this:
If Len(Nz([Forms]!frmScheduleRequest![Office], "")) = 0 Then

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Blondie96]

Thank you PHV,

When I changed that, I found Office = 0. so I added an Or to the test & it works correctly now.

Thanks,

Tamra

 

[Blondie96]

This just reminds me, How much I love to hate programming. I remember years ago, a colleague working in cobol kept getting an error something like "expecting variable name found ,"

They were dealing with communications & declared variables:
comma.....pic x(3)..
commb.....pic x(3) or however that format was...

after looking at it for a bit, I realized COBOL thought his comma was a ",". I can't tell you how much time he spent looking at it before asking me...

In other words.... really appreciate the help..

"I LOVE to HATE programming.