Uninserted Record cont

  • Thread starter Thread starter Dan McClelland
  • Start date Start date
D

Dan McClelland

Tried using form's BeforeInsert event, but it is triggered
upon entering your first character in any control on a new
record. This isn't the event I want. BeforeUpdate event
was working fine, even for new records, except that I
can't cancel the insert. (Why does BeforeInsert fire upon
entering the first character in a new record?)
 
BeforeInsert is as its name implies before any data is inserted into the
recordset. Stick with the BeforeUpdate event, you can cancel an entry into
a control with the Me.Undo command, which will undo the last extry into a
textbox. A second Me.Undo will cancel all entries to the current record.

Kelvin
 
Thanks Kelvin, but I must partly disagree. I agree that
BeforeInsert, as its name implies, means it will be fired
before ANY data is inserted. True enough. But simply
typing a few characters into a textbox control in a new
record isn't actually inserting anything. The insert
event does not take place until you save the record, move
to another record or close the form.

I suppose the term "insert" in this case means inserting
text into a textbox control, as opposed to inserting a
record into a table. What a silly event? Seems to me
that having the ability to validate data before inserting
it into a table is much more valuable than validating
before inserting it into a textbox control that hasn't
even been inserted into a table.
 
The Insert events are for new records only. If you are changing existing
data you are not inserting anything. However, when you enter something into
a new record, since that record doesn't exist yet, this Insert event is
triggered to insert a new record into the recordset. This new record is
actually a temporary record, that's why you can undo it, but it is still
created. This will occur the first time you try to add anything into the
new record, since a new record will not exist until that point. Then when
you are done, it will update the record with what was typed in then trigger
the AfterInsert event telling it that it is done adding the new record.

The insert and update events can be thought of in programming terms.

Dim NewRecord as Record -> BeforeInsert
Set NewRecord = RecordInForm ->BeforeUpdate and AfterUpdate
Set NewRecord = Nothing -> AfterInsert (This actually occurs just before
clearing the variable)

Kelvin
 
Ahhhhh. So, if I understand this correctly, after
entering the first character in a new record, the
BeforeInsert event is triggered and a temporary record is
created which holds your data. When you do a Save Record,
the temporary record is updated and it goes into the table
as a "permanent" record? Why didn't they create a true
BeforeInsert event for inserting the WHOLE and completed
record, and a separate BeforeInsertTemporaryNewRecord
event which fires upon entry of your first character?

And is this temporary one-character record allowed to
ignore validation rules? Because, to me, inserting a
record means you want to have your newly entered data
verified against your validation rules. And having Insert
events should take this into consideration. It appears
they don't. I can't think of a single reason I'd want to
put any kind of code in a BeforeInsert event, as there
would be no data in the new record except for the first
character typed. What is there to validate? What is the
point?

And finally, does this BeforeInsert event work the same
way on a form bound to SQL Server data? My issue is
simple, I want my users to enter a new record, and I want
it validated before being permanently inserted, and I want
the user warned if they missed something or broke a
validation rule. (And I want to avoid the design overhead
of an unbound form, a SAVE button and pages of code to
manually validate).

BeforeUpdate works well, except that when I use the Access
intrinsic constant acDataErrContinue as the value of the
Response argument (to disable the Access warning message
and use my own customized message), the user is returned
to their new record but it is blank. All the information
they entered is gone (quite a few fields) and they'd have
to start over.

Sigh. This all used to work so well with Jet.
 
See below:

Dan McClelland said:
Ahhhhh. So, if I understand this correctly, after
entering the first character in a new record, the
BeforeInsert event is triggered and a temporary record is
created which holds your data. When you do a Save Record,
the temporary record is updated and it goes into the table
as a "permanent" record? Why didn't they create a true
BeforeInsert event for inserting the WHOLE and completed
record, and a separate BeforeInsertTemporaryNewRecord
event which fires upon entry of your first character?
Click to expand...

Insert just creates the place holder, it doesn't actually put anything into
it. The update event is what adds the record. In my opinion, the
BeforeInsert actually should be triggered as soon as you enter the new
record. I guess Microsoft thought it would be better to wait until
something was entered just so the program wouldn't be creating these place
holders if it wasn't needed.
And is this temporary one-character record allowed to
ignore validation rules?
Click to expand...

Which validation are you referring to. Validation of the teextbox or the
form. Validation of the textbox will occur when you hit tab, enter, click.
Validation of the record isn't triggered until you go past the last field or
save. If you want validation when you type the first character, try the
Dirty event.
Because, to me, inserting a
record means you want to have your newly entered data
verified against your validation rules. And having Insert
events should take this into consideration. It appears
they don't. I can't think of a single reason I'd want to
put any kind of code in a BeforeInsert event, as there
would be no data in the new record except for the first
character typed. What is there to validate? What is the
point?
Click to expand...

None. Validation doesn't occur at this point. See above.
And finally, does this BeforeInsert event work the same
way on a form bound to SQL Server data? My issue is
simple, I want my users to enter a new record, and I want
it validated before being permanently inserted, and I want
the user warned if they missed something or broke a
validation rule. (And I want to avoid the design overhead
of an unbound form, a SAVE button and pages of code to
manually validate).

BeforeUpdate works well, except that when I use the Access
intrinsic constant acDataErrContinue as the value of the
Response argument (to disable the Access warning message
and use my own customized message), the user is returned
to their new record but it is blank. All the information
they entered is gone (quite a few fields) and they'd have
to start over.
Click to expand...

Are you sure you are returning to the same new record or is this going to
another new record. If you continue an error, it just ignores the error and
continues what it was doing. So if this is occuring while you are updating
the record (ie making it permanent), continuing through the error will
continuing saving the new record and take you to the next new record.
Sigh. This all used to work so well with Jet.
Click to expand...

I would remove the validation from the properties section and put them in
the events instead. If you want to check each character, use the change
event of the textbox, if you want to check the entire value, use the
beforeupdate event of the textbox and to check all fields in one time, use
the beforeupdate of the form. Instead of continuing through the error,
cancel the update using Cancel=True. Good luck.

Kelvin
 
First of all Kelvin, I really appreciate you sticking with
me on this. And I appreciate your help and knowledge.

The validation I'm referring to is in the design of the
FirstName and LastName fields of a SQL Server table. I'm
not actually attempting to validate anything at the
textbox or control level (other than experimenting). Nor
am I validating anything until they are done with the
whole record.

I've designed these two fields to NOT allow nulls, so a
new record will not be saved if there is no data in the
FirstName and LastName fields. Trust me on this, SQL
Server follows its rules and won't allow it. And I only
need the validation to take place when the user is done
and does anything that will attempt to update that new
record to the table (clicking my New Record button,
closing the form, going past the last field or exiting
Access).

When the form returns to a new blank record, I don't know
if it is the same record or a totally new one. I don't
care either way, cause the problem is, all the data that
the user entered is gone. Totally gone. Not stored in
the table, not available on the form, just gone. This is
my issue. I don't want the user to have to re-enter
everything.

You mentioned that when I continue an error, it just
igornes the error. Let me clarify. I've written code for
the form's OnError event. There are two arguments you can
set when this event fires: DataErr and Response. I'm
setting the Response argument to "acDataErrContinue".
According to everything I've ever read about this
argument, it only has one purpose. If you use Continue,
it will NOT display the standard Access message box
detailing the error and you can display your own. If you
use Display, it DOES display the standard Access error
message. It has nothing to do with continuing past the
error. The code I put in that event handles that. Oh,
and when I do set the Response argument
to "acDataErrDisplay", it shows the Access error message
and returns to the form WITH the user's data intact, so he
can continue.

All I want is simple (or at least it was using Jet). If a
user attempts to leave an incomplete record, he is warned
and is returned to it so he can finish. That's all I want.
 
Since using the Display option shows the message and returns to the record
with the user's data while the Contonue option does not, I'm guessing your
code is causing the form to go to the next new record without saving the
current new record which then losses the currrent record.

I personally try not to place validation in the tables design or on the
individual controls. I also don't like using the OnError event. I prefer
usint the BeforeUpdate event of the form. Here's an example.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(txtFirstName) Then
Cancel = True
MsgBox "The firstname cannot be empty."
txtFirstName.SetFocus
ElseIf IsNull(txtLastName) Then
Cancel = True
MsgBox "The lastname cannot be empty."
txtLastName.SetFocus
End If
End Sub

The example above should work even with a SQL Server table, since the
BeforeUpdate event is triggered before the validation from the table would
be triggered. The only problem to the above is that if you hit the standard
close button on the form you will get another standard Access message above
not saving the current data. I'm not sure if the OnError event would trap
this or not. You can get around that by using a command button to close the
form and just repeat the code above without the Cancel=True line, since
there is nothing to cancel at this point. Good luck.

Kelvin
 
Back
Top