Re: Update conditions not working

On 20 Aug 2007 03:21:25 GMT, OV <f@xxxxxxxxxx> wrote:

I knew this might get hard to explain, but perhaps I'm just doing it wrong.
The business case: We have a vendor product, as access app, that has a
table which contains "application" data. We need to add more data
(columns) than their system allows for. SO when we add a new record, we
need to add additional information. So I set up a separate database with
the same key structure. What I need to do is add records to the new table
ONLY when one exists in the old table.

If you need more than the 255 fields that Access allows... or for that matter,
probably if you need more than 30 or so fields - you ARE doing something
wrong. What's the structure of your tables? I very strongly suspect that
you're "committing spread***", storing one-to-many relationships by adding
more fields to the table, rather than having two tables in a one-to-many
relationship and adding new *rows*.

Access views "new records" in the new table the same, but for me there are
two cases: A new record that is asscciated with an existing record in the
Application table (The null side of an outter join, which I want to allow)
and a completely new record which shows up when the user scrolls down or
hits the >* button, which I do NOT want to allow. Essentially, I can't
figure out how to determine that latter condition. How do determine when
I'm at a *trully* new record condition in the master table (which is the
subform at this point). Maybe I need to reverse these forms or something.
I need to show the user every edittable record in the new form or perhaps
just insert a bunch of empty records with keys to sync up when I load the
form? DO I need to sync these manually and forget the master/child link?

It's certainly easier to have the master table on the master form, and the
child table on the subform. But creating empty "placeholder" records is
*NEVER* a good idea - they have a terrible habit of never getting filled in.

A one-to-one relationship still has a polarity - there's still a master record
and a child record.

The catch 22 is I was running into in syncing was associating a recordset
with the form: Copy the bookmark wasn't working when at EOF because I was
getting "no current record"

I humbly await your advice! Sorry for the delay...road trip!

I think your form problems stem from your table design. Again... if you have a
table with 256 fields, that's easily two hundred fields too many. Normalize!!!
"Fields are expensive, records are cheap!"

John W. Vinson [MVP]
.