Best way to enter data, tables 1-1

[David Portwood]

I have a main table related 1-1 to another table. Whenever I enter a record
into the main table, I want a record in the other table as well. At present,
I have a form based on the first table. In the AfterInsert method, I use a
recordset variable to add a new record to the second table.

The above works, but I was wondering if I should be basing the form on a
query combining the two tables so I can enter data into both tables from
controls on the form. This might be faster and more reliable. I am concerned
about the possibility of leaving a record in the first table without a
record in the second - in case of network glitch, for instance.

And yes, I could have used one table for all. However, the data items in the
second table are grouped separately because they have a distinct, separate
use from the data items in the first table. Users can browse/edit the second
table without disturbing the first, which would seem a good thing in terms
of maximizing performance and minimizing network traffic.

Anyway, I tried basing the form on a query that combines the two tables and
adding some invisible controls for the second table's data to the form. When
I do this and try to enter data I get an error message "Cannot update the
control", referring to one of the invisible controls based on data from the
second table. I viewed the control's properties and it is enabled and not
locked. Is the query non-updateable in this circumstance?

What would y'all say is the best way to implement the scenario described
above? What I am doing now works, but I wonder if I am missing a possible
improvement.

Any comments would be appreciated. Thanks in advance.

David Portwood

 

[Baz]

Combine the tables and be done with it. Users shouldn't be directly
browsing tables anyway. Give each of the two kinds of user a different user
interface to suit their requirements. And forget about performance: there
is no way that splitting the tables in the way you describe is going to give
you any performance benefit worthwhile enough to outweigh the cost of
over-complicating your design.

 

[Hunter57]

Hi David,

Could you list the Tables and Fields pertaining to your question?
For Example:

TableName1:

FieldName1 Autonumber PK(Primary Key)
FieldName2 Text
FieldName3 Number
FieldName4 Date/Time


TableName2:

FieldName1 Autonumber PK(Primary Key)
FieldName2 Number FK(Foreign Key)
FieldName3 True/False
FieldName4 Text

Happy New Year,
Patrick Wood

 

[Hunter57]

Hi David,

I forgot to mention: could you post the SQL of your query here also?

Happy New Year,
Patrick Wood

 

[Hunter57]

Hi David,

I forgot to mention: could you post the SQL of your query here also?

Happy New Year,
Patrick Wood

 

[David Portwood]

Combine the tables and be done with it. Users shouldn't be directly

browsing tables anyway.

Users aren't directly browing the tables. They view the tables through
forms, of course.

Give each of the two kinds of user a different user interface to suit their
requirements. And forget about performance: there is no way that splitting
the tables in the way you describe is going to give you any performance
benefit worthwhile enough to outweigh the cost of over-complicating your
design.

In a multiuser environment, splitting the tables in this manner reduces the
chance of locking conflicts. I think this is a worthwhile performance
benefit. And I don't think I am complicating the design by reducing the
number of fields in a record. In fact, I believe such reduction typically
has the effect of simplifying design.

 

[David Portwood]

Well, I didn't want to get bogged down with too much detail. Should I expect
a form be updateable when based on a multitable query? Do you know of any
situations when it might not be?

 

[Baz]

Please yourself, I can't be bothered.

Users aren't directly browing the tables. They view the tables through
forms, of course.


In a multiuser environment, splitting the tables in this manner reduces
the chance of locking conflicts. I think this is a worthwhile performance
benefit. And I don't think I am complicating the design by reducing the
number of fields in a record. In fact, I believe such reduction typically
has the effect of simplifying design.

 

[Hunter57]

Hi David,

After reading your previous post I see that you have a good understanding of
database design.

The answer is definitely yes, I use queries with multiple tables to update
records in some of my forms. Here is a Microsoft site that explains in
detail when and when such a query will not allow updates or edits:

http://support.microsoft.com/kb/304473

Best Regards,
Pat Wood

 

[David Portwood]

Thanks, Hunter. I'll take another look. I might be setting the .locked
property of my controls somewhere in code.

 

[Hunter57]

Hi David,

Just a couple of notes to help you get started:
Put together a multitable query and see if you can edit the data. If you
can, then you can use it as the RecordSource for your Form. Do not use the
Primary Key for more than one table because if you use Referential Integrity,
your query will not allow updates. Use the Foreign Key from the other table
if you want, but do not allow edits to that field. The user does not need to
use it anyway.

Often I show the Primary key on my form, but the textbox is locked and I
give it a light gray back color. That way the PK ID number can't be changed
and I can find records using the PK ID Number like this.

' Create a variable to use to find the specified record
Dim strCbo As Long
strCbo = myMemberID ' In the Calling form, this is usually the FK
field.

' Open the Form to the specified Member's record
DoCmd.OpenForm "frmMembers"
' The PKFieldID textbox must be visible
Forms!frmMembers![myMemberID].SetFocus ' In this form this is the PK
DoCmd.FindRecord (strCbo)

I find this to be a little faster than using the Recordset Clone method.

Regards,
Patrick Wood