Why is my BeforeUpdate code not working??

[tlyczko]

I have a BeforeUpdate where I need to ensure that no matter what, the
first four fields on the form (one text box, 3 combo box lists) have
data entered in them before the user closes the form or before the user
moves off the form to a subform on the main form, regardless of whether
the end user even put focus to any of the four form fields.

(It is easy to know if all 4 fields have values, but not if less than
four fields have values.)

I've been using code like this in the form's Before Update event (only
one example since the code is the same for all four form fields):

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.cboReviewDescription) = True Or
Me.cboReviewDescription.Value = "" Or Len(Me.cboReviewDescription) < 1
Then
Call MsgBox("Please choose a description for this review.",
vbExclamation Or vbSystemModal, "Required Data Entry")
Cancel = True
Me.cboReviewDescription.SetFocus
Me.cboReviewDescription.Dropdown
Exit Sub
End If
End Sub

I have the same kind of in the OnExit event of each of the controls I
need to make sure have data in them.

The code seems to work well for some of the fields, but upon clicking a
btnClose (which does nothing more than close the form's window) neither
the form's BeforeUpdate or the controls' OnExit event works properly,
the message box comes up twice (or more) to be clicked OK, but the
focus does not go back to the form, the form closes without saving any
data.

If the end user doesn't put focus to any of the four controls or focus
to any of the controls on the form, I don't care, but if they have
entered data in only 1, 2, or 3 of the required controls, then I need
to make them go back to the control that needs data entry done.
Basically if data was entered in any 1 or more of the four controls, I
have to validate all four control before closing the form or moving to
the subform.

While typing, I realized I could put some tests into the OnClick event
of the close button control, I will try that now as well.

I've been trying different things for the past day, sigh...I haven't
found anything on the NGs about why my particular code seems to keep
failing, I tried re-compiling, the table for the form's query has the
four fields set to required and not to allow zero length...

Thank you, Tom

 

[tina]

when you have code running in the form's BeforeUpdate event of a form, and
you use a command button to close the form, you need to explicitly save the
current record in the command button's procedure, *before* the Close command
runs - and then trap the error that results when the BeforeUpdate event is
cancelled.

take a look at my explanation and sample code at
http://groups.google.com/group/comp...es*+author:tina&rnum=1&hl=en#54bd02708d914487

hth

 

[tlyczko]

Yes, I added this code to my btnClose OnClick event, and it appears to
do what I need.

I have one more question, if you (or someone else) don't mind.

After I validate that the four fields have data in them, I do this
code, to make sure they have entered the "right/correct" and I ask them
to 'double-check':

If MsgBox("The values you selected are:" & vbCrLf & _
vbCrLf & Me.dtmReviewDate & vbCrLf & vbCrLf & _
Me.txtReviewDescription & _
vbCrLf & vbCrLf & Me.cboProgramID.Column(0) & vbCrLf & vbCrLf &
Me.txtLocation & vbCrLf & vbCrLf & _
"Are these correct? Do you want to add them?" & vbCrLf & _
"Please be SURE before you add them." _
, vbQuestion + vbYesNo, "Add to database?") = vbNo Then
Cancel = True 'return to editing form, these values are not yet
locked
'Exit Sub
End If

This all works very well...but it also makes it possible to close the
form WITHOUT putting at least one record's worth of data (the visible
fields) into this form's SUBFORM.

Can I use similar BeforeUpdate code in the SUBFORM to ensure that I
have data in the first two visible fields on the first new record the
main form's subform?????

If this isn't making any sense, please tell me...I'm now going to
consult Access Hacks and Access Annoyances.

Thank you, Tom

P.S. You have a lot of stars by now!! You're very good at this.

 

[tina]

well, let's see if i understand the situation. you're able to validate the
data in the main form to your satisfaction before the current record is
saved, and you're satisfied with how that's working for you, correct? but
you find that the user can enter data in the main form and then close the
form - without entering any records in the subform, correct? you want to
force the user to add at least one record to the subform, before closing the
main form - or moving to another record in the main form, correct?

well, that can get a little tricky - trying to force the user into adding a
record to a subform, while at the same time not boxing the user into a
situation where s/he has to add a record *even when it's not appropriate*.
i'd probably start out by trying the following:

add code to the main form's AfterUpdate event, to count the number of
records in the subform. if it's less than 1, move the focus to the subform,
and then to the first record in the subform. something like

Me!SubformControlName.Form.RecordsetClone.MoveLast
If Me!SubformControlName.Form.RecordsetClone.RecordCount < 1 Then
Me!SubformControlName.SetFocus
Me!SubformControlName!FirstControlInSubform.SetFocus
End If

then check the recordcount again, on the subform control's Exit event, and
cancel the Exit if there are no records in the subform. something like

Me!SubformControlName.Form.RecordsetClone.MoveLast
If Me!SubformControlName.Form.RecordsetClone.RecordCount < 1 Then
Cancel = True
Msgbox "Enter a record in the subform, please."
End If

note that in both instances, the code is running in the *main* form, not the
subform. also, if there are no records in the subform, and yet you do *not*
get an error message when you click the Close command button, then you'll
have to put the AfterUpdate event code in the Close button's event
procedure, as well.

if you're not clear on the difference between a subform *control* and a
subform *form object*, go to
http://home.att.net/~california.db/instructions.html and click the
SubformControlName link for an illustrated explanation.

hth

 

[tlyczko]

Thank you, sorry for the double post...I know about newsgroups...but I
thought my question might get lost, and it's something I'm quite behind
on...and I thought if I explained better a second time, I might have a
better chance of getting an answer. Your answer is great!

I knew I had to work with the events but not well enough where/which
events, your explanation was a big help.

I do try a lot of things before I post...

Thank you, Tom

 

[tina]

you're welcome

 

[tlyczko]

I struggled for about an hour with your suggestion, which made perfect
sense...
It kept not working, then I searched around some more with different
phrases and found this link:

http://groups.google.com/group/micr...orce+subform+data+entry&#doc_d6e5a0aaa4057b04

It talks about using the subform's Dirty property...

So I have in After Update:
'4/17/06 set subform to dirty and set focus to it, this prevents
leaving the form
'4/17/06 must disable close button on the window for this to work,
however
'4/17/06 reference link:
http://groups.google.com/group/micr...orce+subform+data+entry&#doc_d6e5a0aaa4057b04
If Me!fsubAddNewRecordReviews.Form.RecordsetClone.RecordCount < 1 Then
Me.fsubAddNewRecordReviews.SetFocus
Me.fsubAddNewRecordReviews!cboConsumerID.SetFocus
Me.fsubAddNewRecordReviews.Form.Dirty = True
MsgBox "testing form.dirty"
End If

I am still testing, but it seems to work as necessary.
I only test to make sure they have at least one consumer record in the
subform, since I can't know in advance how many sub-records there
should be...

Thank you again, Tom