form_BeforeUpdate(), What is my problem with this?

  • Thread starter Thread starter Frederick Wilson
  • Start date Start date
F

Frederick Wilson

I have tried to find the answer via goggle, no luck.

Access XP

My data entry form has 3 bound controls, the form is based on a table with
three fields.

Form.Dataentry=YES
Allowedits=yes
allowadditions=yes
allowdeletes=yes

In the code I have
select case len(me.txtIdentify)
case 0
Msgbox ...
cancel = true
me.txtIdentify.setfocus
case <>6
MsgBox ... too short
cancel = true
me.txtIdentify.setfocus
case else
me.txtIdentify=ucase(me.txtIdentify)
End Select

I have a button cmdAddProd that I have tried
me.refresh
me.requery

when you click the button it fires the beforeupdate, the problem gets
noticed the appropriate message box displays but the code continues to
execute instead of stoping at cancel = true. The code continues back to the
onclick_cmdAddProd but errors out complaining about the process was canceld.

Ideally, I would like to ensure that the information in the form is correct
prior to it being added to the table. The the form will reset as a blank
form to add the next product.

I thought BeforeUpdate was the way to go.

Can you help
 
The most important issue is, Which event are you using?

Because you do not know if the user will ever visit this control, the only
event that can reliably test it is Form_BeforeUpdate. This event fires
before the record is written to the table, regardless of which controls have
had focus.

In the BeforeUpdate event of the form, test if the control is Null:
Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.txtIdentity) Then
Cancel = True
MsgBox "Must enter txtIdentity"
Me.txtIdentity.SetFocus
End If
End Sub

Now you also want to insist the exactly 6 characters are entered. Do that in
the BeforeUpdate event of the *control*:
Private Sub txtIdentity_BeforeUpdate(Cancel As Integer)
If Len(Me.txtIdentity) <> 6 Then
Cancel = True
Msgbox "Enter exactly 6 chars, or press <Esc> to give up."
End If
End Sub

To force the accepted entry to upper case, use the AfterUpdate event of the
control:
Private Sub txtIdentity_AfterUpdate()
Me.txtIdentity = UCase(Me.txtIdentity)
End Sub

In summary:
- use Form_BeforeUpdate to test if there was an entry;
- use the control's BeforeUpdate to validate the field;
- use the control's AfterUpdate to modify the entry.
 
Allen,

With a little tweaking this worked great. I did not like the response to the
beforeupdate at the control level as it would not let you leave the control
until you fixed the problem. However, your suggestions got me going again.

Thanks,
Fred
 
Back
Top