Add data to table

[BB]

When I enter data in a form, I would like to add some of the info in a second table. What is the best way to do this? The tables have a one-to-one relationship.

Thanks, BB

 

[Atomic Shrimp]

The inevitable question will be:
Why bother having two separate tables with a one-to-one relationship; can't you combine them into a single table? (the only time I've ever seen a real need for a one-to-one relationship was where one of the tables was linked in from an external source)

 

[BB]

I have several tables (CreditCardTransactions, Rental, WorkOrder, FuelTransaction, Adjustments) that involve transactions. It was suggested that I include an AccountTransaction table to bring all the transactions together, making it easier to do my reports(invoices).

 

[Rich@ITTC]

Hi Mike

I take your point about usually only needing one table, but what about the situation where you want to keep some areas of information confidential? For example, each member of staff is only on one pay scale and has given me only one set of account/bank details ... but the other staff details can also be seen by many other users of the database. The tblStaffPay is a table that is then only available to high level users (set via Security), but it has a one-to-one relationship with the main table, tblStaff.

Rich Gorvin

 

[Alexandre]

My 2 cents

I usually rely on hidding controls or dynamically opening/closing subforms for security matters.
the most common occasions when I had to use One to One relationships so far were:
-In a table, a subset of records have specific properties (fields) that would be left blank for many records if I used only one table.
-A subset of records or all records have temporarily associated properties, that I would like to easily delete on will, and without wasting space.

Alex

 

[BB]

I am doing something similar to what Pat suggested, but am getting an error: "The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again."

I am able to enter one record in my form, but when I try to enter a second record, I get the above statement. Any ideas what I might be doing wrong?

Thanks, BB

 

[larryajax]

Yes I know what is wrong. You have your query backwards I think. You must use the join field (cannot be a primary key) from the many side(the data being used for your auto fill) and all the fields from the one side (Do not include the join field which must be a primary key) which have the data you want auto filled. Confusing? I know. Once you get all that right (use the help menu) you will say "ah ha!".