Using VBA to distribute infomration from a form to respective tables

  • Thread starter Thread starter ITMA
  • Start date Start date
I

ITMA

I've several related tables and I want a form that has aspects of all the
tables. I cannot get Access to work with a form based on a Query that
relates the tables. It just refuses to recognises the key presses, I assume
because of referential integrity being enforced and it generally not knowing
which table takes priority.

I guessed the answer is to create a form with assorted unbound controls into
which the information can be entered and then when clicking a 'submit' or
'enter' type button, VBA kicks in and spoon feeds the tables in the correct
order and generally keeping Access happy.

Any pointers on how this is best done?
 
I would like to suggest you use subforms, instead of a query joining all the
related tables, review your normalization as well,
 
I would like to suggest you use subforms

They are great for presenting information already in the database but are
clumsy and require too much user thought for inputting information.
 
I would suggest this is a design issue, rather than being inherent to
the nature of subforms. With correct table structure, and correct form
design, I can not imagine a scenario where programmatically writing to
multiple tables from unbound controls would be advisable.
 
I've several related tables and I want a form that has aspects of all the
tables. I cannot get Access to work with a form based on a Query that
relates the tables. It just refuses to recognises the key presses, I assume
because of referential integrity being enforced and it generally not knowing
which table takes priority.

I guessed the answer is to create a form with assorted unbound controls into
which the information can be entered and then when clicking a 'submit' or
'enter' type button, VBA kicks in and spoon feeds the tables in the correct
order and generally keeping Access happy.

Any pointers on how this is best done?
Click to expand...

Well, an unbound form with VBA which opens multiple Recordsets can
certainly work. It's a lot of effort of course!

The *usual* way to do it is not to create a form based on One Great
Master Query, but to use a Form for the "one" side table with Subforms
for its "child" tables, perhaps with sub-subforms.


John W. Vinson[MVP]
 
Back
Top