Relationships??

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Adam - 23 Apr 2005 13:29 GMT

1. I have a one-to-many relationship. But everytime i go back to the
relationship window Access automatically creates another relationship using
the same table on the one-side of the relationship and then adds a "_1" to
the end of the table name (making it different table in the relationship
view). Why is it creating this relationship all over again with a slightly
different table name on the one side??

2. I have a form that uses 2 tables (customer and payments) where you track
customer payments. It was working fine. Now when ever i add a new customer to
the customer table, then i go to add a payment record for them in the
payments form by selecting there name from a drop list on the form it doesn't
change the records below on the payments subform. But when i select another
person who was already in the database it looks up there payment records fine
and i can add a payment record for them with no problem??? Can anybody help??

Reply to this Message

Ken Snell [MVP] - 23 Apr 2005 17:17 GMT

Comments inline...

Signature

Ken Snell
<MS ACCESS MVP>

> 1. I have a one-to-many relationship. But everytime i go back to the
> relationship window Access automatically creates another relationship
[quoted text clipped - 3 lines]
> view). Why is it creating this relationship all over again with a slightly
> different table name on the one side??

That "_1" table is a copy of the original table. In order for ACCESS to
display a relationship between two tables where at least one of the linking
fields is used in more than one relationship between the two tables, ACCESS
creates a copy of the table (and names it by adding "_1" or "_2" to the
table's name) in the Relationships window. ACCESS also will do this if you
create a separate join in a query between the two tables and that one field
is used in that "query" relationship. In other words, what you're seeing is
normal and should be left alone.

> 2. I have a form that uses 2 tables (customer and payments) where you
> track
[quoted text clipped - 9 lines]
> and i can add a payment record for them with no problem??? Can anybody
> help??

Are you talking about data entry in a form? or directly in the table? Your
terminology is inconsistent here. If you're talking about a form and
subform, then I'm guessing that the subform control is not linked to the
main form via the subform control's LinkMasterFields and LinkChildFields
property, so the form is not inserting the correct foreign key field value
into the child table. But, to provide more info, you'll need to give us more
details.

Reply to this Message

Adam - 24 Apr 2005 13:22 GMT

Yes Ken I have talking about a Form and Subform. In the main form the data
comes from a table called customers...in the subform the data comes from a
table called payments. When ever you select a customers name in the main
form, the subform displays all the payment records for that customer.

Currently any old customers that are in the table work fine - i pick the
name and the records int he sub form show. But for any new customers i add it
just doesn't show there payment records...

> Comments inline...
>
[quoted text clipped - 36 lines]
> into the child table. But, to provide more info, you'll need to give us more
> details.

Reply to this Message

Ken Snell [MVP] - 24 Apr 2005 18:14 GMT

Are the main form and subform linked by the LinkMasterFields and
LinkChildFields?

Is AllowAdditions property set to Yes for the form that is being used as the
subform? If not, change the value; without it, unless there are payments
already in the table, the subform will not show a blank record to allow you
to enter payments.

Signature

Ken Snell
<MS ACCESS MVP>

> Yes Ken I have talking about a Form and Subform. In the main form the data
> comes from a table called customers...in the subform the data comes from a
[quoted text clipped - 59 lines]
>> more
>> details.

Reply to this Message