Access Guru's: Is This Possible?

[Alias]

Hello all,

Is it possible to do the following with NEW RECORDS ONLY:

1. Prevent record saves if FullName field is blank.
2. Allow move from record if fields dirty, but DON'T SAVE if FullName is
still blank.

I'm trying code below in OnExit property, but data still saves. Can someone
help me fix the code, or write a new code that will work:

Private Sub Dept_Exit(Cancel As Integer)
Dim intnewrec As Integer
intnewrec = Form.NewRecord

If intnewrec = True Then
If IsNull(Me.FullName.Value) And Me.Dirty Then
MsgBox "Employee field cannot be left blank."
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdUndo
DoCmd.SetWarnings True
End If
End If

End Sub

 

[Douglas J. Steele]

It's not possible to move from a record if it's dirty without saving.

 

[Alias]

Douglas,

Well now I'm really confused especially after seeing Access Help section's
(Dirty Property) which say's the following:

"You can use the Dirty property to determine whether the current record has
been modified since it was last saved. For example, you may want to ask the
user whether changes to a record were intended and, if not, allow the user to
move to the next record without saving the changes. Read/write Boolean."

I'm no expert but having seen the above, will the example used in the Dirty
Property also work for New Records?

-A

 

[Albert D.Kallal]

I suppose you could use forms before update event.....

if isnull(me.FullName) = true then
me.undo
end if

On the other hand, why not jet set the field to "required" in the table
design, and then the record can't be saved unless the user enters
fullname....

 

[MtnView]

Hello, I'm not sure what you want to happen to the new record. Would
suggest you validate the form by using the BeforeUpdate Event for the
Form. Something like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim intnewrec As Integer
intnewrec = Form.NewRecord

If intnewrec = True Then
If IsNull(Me.FullName.Value) And Me.Dirty Then
MsgBox "Employee field cannot be left blank."
DoCmd.SetWarnings False
'Don't need to undo DoCmd.RunCommand acCmdUndo
Cancel = True
DoCmd.SetWarnings True
End If
End If

End Sub

 

[Alias]

To all,

Albert, I set the Name field to required in the Main table which works fine.
Now, how do I move to a different record if I don't want to fill in the new
record.

What happened is I made a selection from combo, then moved to different
record and got popup message. Now I can't move away because the popup message
keeps coming back and I'm stuck in the new record.

Any suggestions on how to get out of filling in the new record?

-A

 

[John Vinson]

Any suggestions on how to get out of filling in the new record?

Hit the <Esc> key twice (to cancel the current field and then the
current record), or, as Albert suggested, include a line Me.Undo in
the BeforeUpdate event.

John W. Vinson[MVP]

 

[Alias]

All Reponders,

I have things working great now thanks to all responders. Without your help
I would still be spinning my wheels. All of you have "A Beautiful Mind"

-Alias

 

[tina]

well, you can undo all data changes when a new record is "dirtied" but
missing data in the FullName field. at that point the user could go to
another (existing) record, but all data s/he'd entered in the new record
would be lost. that seems kind of harsh - what if the user simply forgot to
enter the full name data? wouldn't it be better to give the user a choice
between going back and entering the data, or erasing the new record
entirely? to do that, try

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Me.NewRecord And Me.Dirty And IsNull(Me!FullName) Then
If MsgBox("The full name is required data. Click OK to enter " _
& "the data, or Cancel to erase all the data in this record.", _
vbOKCancel) = vbOK Then
Cancel = True
Me!FullName.SetFocus
Else
Cancel = True
Me.Undo
End If
End If

End Sub

hth

 

[Alias]

tina,

Like the tv commercial says "Brilliant". I feel so lucky today

-Alias

 

[tina]

you're welcome

btw, the solution that i posted will do absolutely *nothing* to prevent a
user from deleting the data from the FullName field in an *existing* record
in the form - either accidentally or deliberately - and then saving the
record without a FullName value. if that's an issue, post back and we'll
address it.

hth