Data Validation in ADO Form Recordset Versus DAO RecordsetClone
And I am sorry but I will say again that I think you are
mistaken. I was able to put the form recordsetclone in a
variable that was an ADO recordset, and perform all of these
operations on it, and it had NO effect on the current
(dirty) record on the form.
I think you must be doing something wrong, because it works
just fine for me here.
--
MichKa
-------------------------------------
don't send questions by e-mail unless
you're paying for it. (TANSTAAFL!) :-)
random junk of dubious value and
replica error and problem fixing at:
http://www.*-*-*.com/
Quote:
> Mischka, you used to be able to use the RecordsetClone to
check a new record
> being added against all the other records in the table in
Before Update
> Events in the current record in the form without the
current record having
Quote:
> been added or updated to the RecordsetClone and I don't
think you can do
> that any more unless you do find backwards from the record
being added or
> similar things but it could get complicated because maybe
you're checking an
> update on an already existing record. I am merely pointing
out that if I am
> not mistaken then certain RecordsetClone data validation
techniques that
> used to work with DAO may no longer work with ADO thats
all.
> (I have tried many variations on this including setting
the Form Recordset)
> Global in the Start Up Form
> Set cnn = CurrentProject.Connection
> In the Open Event of the Chart Maintenance Form, the
Recordsource of the
> form being an SQL script in the Form Properties
> Set rstChart = New ADODB.recordset
> Set rstChart = Me.RecordsetClone
> If you do the following in the Before Update Event of the
Primary Key field
> called 'No' in the form then on entering a value in the No
field and
> pressing enter
> If IsNull(Me!No.OldValue) then
> rstChartClone.MoveFirst
> rstChartClone.Find "No='" & Me!No & "'", 0,
adSearchForward
> If Not rstChartClone.EOF Then _
> Critical_Message "You cannot enter a Duplicate
Chart of Accounts
> Number", Cancel
> End If
> The message 'You cannot enter a Duplicate Chart of
Accounts Number' is
> displayed even if this is a new account number record
being added which
> suggests to me the current form record has already been
added to the
> RecordsetClone by the MoveFirst event which is not what
used to happen.
> It may be because I am using the global
CurrentProject.Connection or I may
Quote:
> be misunderstanding something or doing something dumb.
> Thank you very much for your help.
> Michael (michka) Kaplan
Quote:
> > This is not the behavior I am seeing. When I do things
with
> > the RecordsetClone, the record is still dirty and its
> > position is still the same and Find has no effect on
this
> > either. This is true in both an ADP and an MDB.
> > If you have an example that proves otherwise, please
post
> > it! But I do not see a problem here....
> > --
> > MichKa
> > -------------------------------------
> > don't send questions by e-mail unless
> > you're paying for it. (TANSTAAFL!) :-)
> > random junk of dubious value and
> > replica error and problem fixing at:
> > http://www.*-*-*.com/
> > > Thanks again Michael
> > > I've now got the ADODB RecordsetClone working in the
ADP.
> > > However, I am still pretty sure that ADP ADODB
> > RecordsetClone works just
> > > like the ADP Form Recordset. That is, as soon as you
do
> > > rs.MoveFirst
> > > rs.Find ......
> > > the current record in the form is added/updated to the
ADP
> > recordsetclone rs
> > > recordset which means you can't use recordsetclone to
> > validate records being
> > > added against records already present in a table like
you
> > used to.
> > > What I'm doing is limiting the Form Recordset to one
> > record and if I need to
> > > validate against the other records i do a separate
> > recordset on the server
> > > table. Thats to say the client has only one record
> > hopefully reducing
> > > network traffic and validation is done against the
records
> > on the server.
> > > This may be of interest to other people going ADP and
any
> > comments
> > > appreciated. Its my last comment on this and I hope
its
> > not all {*filter*}:)
> > > Michael (michka) Kaplan
> > > > There is something wrong with your config, as I can
do
> > the
> > > > following:
> > > > Dim rs As ADODB.Recordset
> > > > Set rs = Me.RecordsetClone
> > > > in any form in an ADP.
> > > > --
> > > > MichKa
> > > > -------------------------------------
> > > > don't send questions by e-mail unless
> > > > you're paying for it. (TANSTAAFL!) :-)
> > > > random junk of dubious value and
> > > > replica error and problem fixing at:
> > > > http://www.*-*-*.com/
> > > > > Thanks Michael for your response.
> > > > > I am in an ADP remember.
> > > > > I have tried
> > > > > Dim Chart_Clone as Recordset
> > > > > or
> > > > > Dim Chart_Clone as DAO.Recordset
> > > > > or
> > > > > Dim Chart_Clone as ADODB.Recordset
> > > > > with
> > > > > Set Chart_Clone as Me.RecordsetClone
> > > > > after setting the form's Recordset to an ADO one
> > > > > and I get a Run Time Error 429 ActiveX component
can't
> > > > create object.
> > > > > I have also tried various other similar things.
> > > > > The only way I can validate the record to be
> > > > added/modified against the
> > > > > existing ones in an ADP it would seem is by using
a
> > > > separate recordset on
> > > > > the connection for all the records in the table.
> > > > > I suppose this is conceptually what is to be
expected
> > in a
> > > > Client / Server
> > > > > system but IMHO it does not seem to be as clean as
the
> > > > previous
> > > > > RecordsetClone technique.
> > > > > There again maybe I'm missing something.
> > > > > Michael (michka) Kaplan
> > > > > > Um, have you tried using the RecordsetClone
after
> > > > setting
> > > > > > the form's Recordset to an ADO one?
> > > > > > In other words, you still want a clone, and you
do
> > NOT
> > > > want
> > > > > > to affect the actual recordset.
> > > > > > --
> > > > > > MichKa
> > > > > > -------------------------------------
> > > > > > don't send questions by e-mail unless
> > > > > > you're paying for it. (TANSTAAFL!) :-)
> > > > > > random junk of dubious value and
> > > > > > replica error and problem fixing at:
> > > > > > http://www.*-*-*.com/
message
> > > > > > > Can anybody help me with this general problem
on
> > ADO
> > > > Form
> > > > > > Recordsets?
> > > > > > > In DAO, you could validate a record being
added in
> > a
> > > > form
> > > > > > against the rest
> > > > > > > of the records in the forms underlying query
in
> > the
> > > > Before
> > > > > > Update event of a
> > > > > > > field by checking the rest of the records in
the
> > > > > > RecordsetClone of the form,
> > > > > > > without the form's current record being
affected
> > i.e.
> > > > > > without the record
> > > > > > > being added. Thus for example you could test
for
> > the
> > > > entry
> > > > > > of a duplicate
> > > > > > > Stock Item Code in the Before Update Event of
the
> > > > Stock
> > > > > > Item Code primary
> > > > > > > key form field control.
> > > > > > > In ADO Recordsets it seems that you cannot
> > validate a
> > > > > > record being added in
> > > > > > > the BeforeUpdate event because as soon as you
> > > > MoveFirst in
> > > > > > order to do a
> > > > > > > Find on all the records in the Recordset the
> > form's
> > > > > > current record is added
> > > > > > > to the Form Recordset, whether the form's
current
> > > > record
> > > > > > is complete or
> > > > > > > indeed only has its primary key added, which
is
> > what
> > > > you
> > > > > > are trying to
> > > > > > > validate.
> > > > > > > So how are you supposed to validate a new
record
> > being
> > > > > > added in a form in an
> > > > > > > ADP using an ADO Recordset without adding the
> > current
> > > > form
> > > > > > record to the
> > > > > > > Form recordset? Do you create a separate
recordset
> > on
> > > > the
> > > > > > underlying query
> > > > > > > or table? I don't think you can use the ADO
Clone
> > > > method
> > > > > > because it just
> > > > > > > clones the form recordset but maybe I'm
mistaken.
> > > > > > > Has anybody come across this Data Entry
Validation
> > > > problem
> > > > > > and solved it? Am
> > > > > > > I being dumb? Do I have to redesign my DAO
form
> > data
> > > > > > validation which
> > > > > > > extensively uses RecordsetClone?
> > > > > > > Responses greatly appreciated.
