Form/Subform

[Pwyd]

Let me pose this question in a different way, as i think i've misunderstood
some of the responses i've gotten. I have a form and a subform, each driven
by a seperate table. What i wanted, was a set of records on the main form,
and on the subform, a set of data that would be unique to each record.
Here's my problem. Other than having the user enter a seperate number, or
field, for their record in order to make it unique, i cannot think of a way
to generate a unique number to link the two tables in a relationship so that
they're in a one-to-one relationship. I was using the invoice number, but it
turns out that the invoice number is not necessarily unique. The way the
query on the subform was set up, if someone later uses the same invoice
number, the subform would pick up the records that belonged to the previous
invoice's record on the main form. Is this clear? I looked through the
northwind database, which was also suggested to me, but thats not the
situation i'm encountering here.

 

[Beetle]

In the previous thread you said you had a one-to-many
relationship. In this thread you say it's one-to-one. Are
you unsure of the nature of this relationship? One-to-one
relationships are not that common. They are typically used
for sub-classing.

Also, you said in the previous thread that you were using
Autonumber for the PK in the parent table, which should
be sufficient for generating a unique ID, but now you seem
to be indicating that you are trying to use some type of
user generated number.

Maybe you can explain a bit more about exactly what it
is you are trying to do.

 

[Pwyd]

Perhaps because i misunderstand the nature of the relationship. Access
claims a one to one relationship is where the child field only shows the
records with the same value as the parent field. I was under the
impression a "one to many" relationship meant that you had one record with a
relationship to several records in the child. which is correct?


As for it being autonumber, i've actually tried several different
iterations, but i'm stumped on finding one that works the way i want it to.
I'm willing to try anything that will accomplish what i'd like, without
adversely affecting the operation of the database, or its speed.

 

[Beetle]

One-to-one means that there can be no more than one record
in the child table for each record in the parent table.

As far as getting the proper FK value into the child table, this
is usually done with a form/sub form and is handled automatically
by the Master/Child link. if your invoice numbers are not unique
then you should use an autonumber PK in the main table with
a number field in the child table for the FK field. If the Master/Child
link in your form/sub form is set up correctly, your users won't ever
have to worry about trying to generate any of the key values.