Form and Subform record issues

[Corey-g via AccessMonster.com]

Hi All,

Using Access 2003.

I have a main form that is unbound, and used to enter order data. When all
data is entered, I will write the info to and Oracle back end. The issue I
have right now is that I wanted to allow the users to add items (from a
series of combo boxes) to a continuious subform. This subform was also to be
unbound, and again write the info to Oracle once everything was complete.

So I have everything working up to when I try to click the 'Add Item' button.
I thought I could just get the recordset clone of the subform, and add a new
record. But because it's unbound there isn't a recordset to clone. Is there
a way to do this?

Could I bind the fields to a recordset, then clear the recordset, in design
view? Then when the form is loaded, bind the form to a recordset
programmatically? Or any other work-around?

Thanks in advance for any thoughts or ideas...

Corey

 

[John W. Vinson]

Or any other work-around?

ummm... a local scratchpad table? Just empty it with a DELETE query on opening
the form (or some other appropriate event) and append its contents to the
linked Oracle table.

To prevent bloat, you may want to have a separate Access backend just for this
table (or for it and other temp tables).

John W. Vinson [MVP]

 

[Corey-g via AccessMonster.com]

Thanks for re-enforcing what I thought would be the correct way to handle
this.

My only trepidation on this was in regards to multi-user scenario's. If
there was 10 poeple using the data entry form, I need to ensure that I only
delete their info, not everything. My thought was that I could add in a
field to the table for the user ID, then use that to filter the subform as
well.

Thanks again,

Corey

 

[John W. Vinson]

My only trepidation on this was in regards to multi-user scenario's. If
there was 10 poeple using the data entry form, I need to ensure that I only
delete their info, not everything. My thought was that I could add in a
field to the table for the user ID, then use that to filter the subform as
well.

The solution to this (and to a LOT of other potential problems) is... DON'T
DO THAT!

That is, multiple users should *not* be sharing a form or a frontend database.
You should use Tools... Database Utilities... Database Splitter Wizard to
split the database into a frontend with the Forms, Queries, temp tables, code,
etc., linked to a backend containing the tables. Each user should have their
OWN scratch table.

See http://www.granite.ab.ca/access/splitapps.htm for a review of this very
important principle.

John W. Vinson [MVP]

 

[Corey-g via AccessMonster.com]

Hi John,

I do have a split database, in that Oracle is the back-end, and Access as the
front-end. The application will be run through Citrix, and each user will
have their own copy of the front-end (this was always the design - I have
read hundreds of posts where this was the problem). But to avoid the
database bloat as you have also mentioned, I decided to use Oracle for the
'scratch-pad'. Then I don't have to worry about compacting and repairing
either the front-end or the back-end once it's in production. But like I had
said, I will have multiple users, so I need to have a method of determining
who added what. Because a user can only ever be adding one record, I should
be able to user their User ID as the distinguisher between orders. And as
long as I remember to put in a clearing delete query (this is key) when the
form is closed / cleared / as well as checked during the open events, I
should be ok.

Does that sound reasonable to you? I ask because you seem pretty adamant
about using Access for a back-end scratch pad. That and you are a great help
to so many that I do value your opinion very highly.

Thank you again for your valuable input John.

Corey

 

[John W. Vinson]

Because a user can only ever be adding one record, I should
be able to user their User ID as the distinguisher between orders. And as
long as I remember to put in a clearing delete query (this is key) when the
form is closed / cleared / as well as checked during the open events, I
should be ok.

Does that sound reasonable to you? I ask because you seem pretty adamant
about using Access for a back-end scratch pad. That and you are a great help
to so many that I do value your opinion very highly.

It sounds like a bit more work and a bit more complexity, but if you're
willing to do that and it works for you, by all means Go For It!

I was just recommending the Access scratchpad because it guarantees user-user
insulation (assuming a split app) without having to code user ID's. As one of
my collagues here says (over my warm furry laptop's strenuous objections),
"there's always more than one way to skin a cat"!

John W. Vinson [MVP]