Recognize change immediately

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have devised some code to verify that certain text boxes are filled in as
required. I have placed the code at the top of the code window, not attached
to any particular control. I think it's called General code or something.
Anyhow, the form is tabbed, and the user can exit from the tab in several
ways. Rather than write the code over and over I have been calling it.
Seems to work, but if anybody has some general information I should know, I'm
glad to hear it. I think it is called a class module, and I think that in
order to call it from another form (although I don't plan to) it would need
to be Public Sub (rather than Private).
Back to the several different ways of exiting: one way is to print a report
based on the information entered, another is to send an e-mail requesting the
next person to view the record in the database, etc. In any case, if a
control is empty a message informs the user that all fields must be filled
in, and places the cursor (set focus) in one of the empty controls. If the
user fills in the missing information and then clicks, say, the e-mail button
again, the message about missing information appears again. Clicking OK to
the message brings up the e-mail (sendObject) as intended. I can avoid the
error message by clicking outside of the field, but cannot figure out how to
accomplish the equivalent with VBA.
Also, as I have mentioned I can call the code at command button click
events, but would also like to call it when clicking on the next tab. I
can't seem to assign it to a click event of the tab (or what seems to be the
click event). I can assign it to the first control in the tab order for that
tab (two different senses of tab here, I know), but I wonder if there is
another way.
 
Bruce,

With the first problem, it sounds like the newly entered data has not
been saved at the time that the command button Click event checks for
the completeness of the the data. Try putting a line of code like this
in at the beginning of the Click event procedure...
DoCmd.RunCommand acCmdSaveRecord

With the second question, try putting your code on the On Change event
procedure of the Tab Control. This event is triggered when you move
from one tab page to another.
 
Thanks for the reply. If there are missing fields the record must not be
saved, so I can not save the record before checking to be sure all fields are
filled in. DoCmd.RunCommand acCmdSaveRecord does not work in my situation.
DArned if I can figure out what I'm doing wrong. Here is the synopsis:
Validate is a general sub, not attached to any control, form, etc.

Public Sub Validate

If IsNull([txtStartedBy]) Then
Me.txtStartedBy.SetFocus
GoTo Message
ElseIf IsNull([txtStartedDate]) Then
Me.txtStartedDate.SetFocus
GoTo Message
Else
GoTo Exit_ValidateRec
End If

Message: MsgBox "All fields must be filled in", , "Missing Information"

Exit_ValidateRec:
Exit Sub

I placed an experimental command button on the form, with just the code Call
Validate. It works as intended. If there is missing information it
generates a message and places the cursor in the control. Once the
information is filled in, clicking the command button again does not generate
an error message. However, when I try to send an e-mail I cannot save the
record after making a change. My Send E-mail code (command button) is
something like this:

Private Sub cmdEmail_Click()

If IsNull([txtStartedBy]) Or IsNull([txtStartedDate]) Then
Call Validate
GoTo Exit_cmdEmail
Else
DoCmd.RunCommand acCmdSaveRecord
DoCmd.SendObject acSendNoObject etc.
End If

Exit_cmdEmail:
Exit Sub

Both have error handling and End Sub (not shown here)

The code behind cmdEmail works as intended when all of the fields are filled
in. When a field is empty the message box from Public Sub Validate appears.
After clicking OK and entering the missing information, clicking cmdEmail (to
take another crack at sending the e-mail message) generates the message box
again. After clicking OK and clicking cmdEmail yet again the email message
is shown, ready to be edited or sent. In short, if I enter missing
information I need to at least tab or click out of the field in order for the
newly-entered information go be recognized.
Any ideas how I can overcome this limitation?
 
Bruce,
... If there are missing fields the record must not be
saved,
Click to expand...

Therefore, the validation code should go in the form's Before Update
event. I would remove the Validate procedure, and your code might look
like this...

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.txtStartedBy) Then
Me.txtStartedBy.SetFocus
MsgBox "Enter Started By", , "Missing Information"
Cancel = True
ElseIf IsNull(Me.txtStartedDate) Then
Me.txtStartedDate.SetFocus
MsgBox "Enter Started Date", , "Missing Information"
Cancel = True
End If

Private Sub cmdEmail_Click()
DoCmd.RunCommand acCmdSaveRecord
DoCmd.SendObject acSendNoObject etc.
Exit Sub
 
I think I get it. I placed acCmdSaveRecord into the e-mail button, and the
validation code (checking to see if the text boxes are populated, similar to
the code you suggested) into the Before Update event. As I understand it,
attempting to save the record (from the E-mail button in this case) causes
the Before Update code to run. The Before Update code prevents the record
from being saved (which is what I expect Cancel = True is for). Once the
user fills in the field and clicks the e-mail button, the Before Update event
doesn't interrupt saving the record.
I see now that it works that you described the same thing initially. I
appreciate your taking the time to explain a little further. Now I just need
to add a message box to let the user know what's going on.
 
Back
Top