Recordset backed form - Inserting a New Record

[riyaz.mansoor]

Hi.

* Have a form which is backed by a ADODB.Recordset.
* Form does not have all the required fields
* Hence I need to programmatically add those fields just before
the Recordset NewRecord is inserted
* Form methods BeforeUpdate when called, the Recordset is still NOT in
AddNew state
* Hence I can't find a place where I CAN insert the fields that
are NOT on the form into the Recordset

* CONSTRAINT: Invisible fields do work, but is not an option - thats
just how it is!

* NOTE: all other CRUD operations are fine.

So is this doable?

Riyaz

 

[Douglas J. Steele]

Sorry, but saying "thats just how it is!" really isn't fair to us: why can't
you use invisible fields?

In any case, as long as the additional fields are in the form's
RecordSource, you should be able to refer to those fields in the currently
uninserted row as Me!NameOfField.

 

[riyaz.mansoor]

hi

Sorry, but saying "thats just how it is!" really isn't fair to us: why can't
you use invisible fields?

OK. The tables in my database have conventions built into them - the
first six fields are named the same and are used for such purposes as
auditing and others. Since the fields and their form based functions
are common, I have extracted the fields into a SubForm. Hence the
fields on the SubForm don't have anything in their ControlSource
property - the MainForm is backed by the appropriate Recordset. The
SubForm field values (for existing records) are placed by code in the
OnCurrent method in the MainForm. Record navigation, DELETE and UPDATE
(row exists in Recordset) operations work fine. Problem only occurs
for the INSERT operation - as described in the original post.

In any case, as long as the additional fields are in the form's
RecordSource, you should be able to refer to those fields in the currently
uninserted row as Me!NameOfField.

Were I to have hidden fields in the MainForm identical to those in the
SubForm, then I have to CopyPaste the controls and code which is error
prone and just unnecessary work (as I see it)

For a Recordset backed Form, when inserting new records, their must be
a way catch the Recordset during or after the field updation but
BEFORE the Recordset.Update operation? Form.BeforeUpdate event occurs
before the Recordset.AddNew method is called by Access. I can't find
an event that gets called AFTER Recordset.AddNew and BEFORE
Recordset.Update.

Help

Riyaz

 

[riyaz.mansoor]

I guess this CANNOT be done huh?

hi


OK. The tables in my database have conventions built into them - the
first six fields are named the same and are used for such purposes as
auditing and others. Since the fields and their form based functions
are common, I have extracted the fields into a SubForm. Hence the
fields on the SubForm don't have anything in their ControlSource
property - the MainForm is backed by the appropriate Recordset. The
SubForm field values (for existing records) are placed by code in the
OnCurrent method in the MainForm. Record navigation, DELETE and UPDATE
(row exists in Recordset) operations work fine. Problem only occurs
for the INSERT operation - as described in the original post.


Were I to have hidden fields in the MainForm identical to those in the
SubForm, then I have to CopyPaste the controls and code which is error
prone and just unnecessary work (as I see it)

For a Recordset backed Form, when inserting new records, their must be
a way catch the Recordset during or after the field updation but
BEFORE the Recordset.Update operation? Form.BeforeUpdate event occurs
before the Recordset.AddNew method is called by Access. I can't find
an event that gets called AFTER Recordset.AddNew and BEFORE
Recordset.Update.

Help

Riyaz

 

[Douglas J. Steele]

You never said what happens when you tried referring to the fields in the
BeforeUpdate event. Certainly I've been able to added values to other fields
in that event.

 

[riyaz.mansoor]

Hi

You never said what happens when you tried referring to the fields in the
BeforeUpdate event. Certainly I've been able to added values to other fields
in that event.

As I said, the first 6 fields are NOT on the MainForm. They are in a
SubForm and hence has NO ControlSource property.

Without the BeforeUpdate event: Access gives an SQL error saying some
fields (fields in the SubForm) cannot be NULL. Access CANNOT see the
fields in the SubForm.

I tried to compensate for this in BeforeUpdate event; However, when
this event is triggered, the MainForms Recordset (MainForm is backed
by a Recordset generated by code) has NOT called the AddNew => there
is no place to enter common fields in the SubForm. If I do try to
insert the values in the SubForm, it just overwrites the last record
the Recordset was on before the MainForm went into the new record.

Hope I've made myself clear.

Riyaz

 

[Douglas J. Steele]

Hi


As I said, the first 6 fields are NOT on the MainForm. They are in a
SubForm and hence has NO ControlSource property.

Without the BeforeUpdate event: Access gives an SQL error saying some
fields (fields in the SubForm) cannot be NULL. Access CANNOT see the
fields in the SubForm.

I tried to compensate for this in BeforeUpdate event; However, when
this event is triggered, the MainForms Recordset (MainForm is backed
by a Recordset generated by code) has NOT called the AddNew => there
is no place to enter common fields in the SubForm. If I do try to
insert the values in the SubForm, it just overwrites the last record
the Recordset was on before the MainForm went into the new record.

An unbound subform? I don't think you have any choice but to use an explicit
INSERT INTO SQL statement.

 

[riyaz.mansoor]

Hi

An unbound subform? I don't think you have any choice but to use an explicit
INSERT INTO SQL statement.

I decided on this already. Access does NOT have any event handler that
can catches the backing Recordset while its being updated.

Having explicit SQL inteferes too much with normal work flow. So I
duplicated the fields in the SubForm in the MainForm as invisible
fields. There is extra code that copies the values to and from those
fields to the SubForm. All of the functionality remains in the
SubForm. The duplication (for all the different MainForms) is not a
problem per se, but it seems to be something that Access should fix.

Riyaz