relationship between tables

[Niklas]

There are two talbes in database; tblA and tblB.

Case:
tblA contains Product's basic information
tblB contains Product's charging information

I try to maintain Product data through form.

Problem:
I cannot add or change a record in tblB because a related
record is required in tblA.

I've tried to change a relationship from one-to-one to one-
to-many and .... what ever. Any suggestions pls.

 

[Tim Ferguson]

Problem:
I cannot add or change a record in tblB because a related
record is required in tblA.

I've tried to change a relationship from one-to-one to one-
to-many and .... what ever. Any suggestions pls.

Need more information:

What is the PK for the Products table?

What is the PK for the Charges table?

How do you allocate the value to the FK field in the Charges table?-- in
other words, there must be a field in the Charges table called something
like ProductNumber to tell you which product is being charged for and this
must be filled in with the correct value.

If you are using a form/ subform setup, then setting the ChildLinkFields
and MasterLinkFields will usually allow Access to create all the records
correctly.

Best wishes


Tim F

 

[Niklas]

1)
the PK for the Products table is ProductID (auto number)
the PK for the Charges table is ChargeID (auto number)

Relationship Type is One-To-One (without any seletions)
Table/Query:tblProduct (ProductID) – Related
Table:tblCharge (ChargeID)

2)
There is the ProductID (number and Long Integer) in the
tblCharge but the value of that field seems to be 0 all
the time.

All connections from tblCharge to tblProduct are created
by ChargeID and Product Name (=normal text field in
tblProduct), and by looking at tblProduct connections are
created by Product ID and hmm…nothing (ProductID as normal
number field is 0 in the tblCharge).

Ive been living with this by opening tblCharge and adding
a new record straight to the tblCharge. After that the
form allows all editions so that values updates also in
the tblCharge. I do not have any kind of problems with
tblProduct.

 

[John Vinson]

There are two talbes in database; tblA and tblB.

Case:
tblA contains Product's basic information
tblB contains Product's charging information

I try to maintain Product data through form.

Problem:
I cannot add or change a record in tblB because a related
record is required in tblA.

I've tried to change a relationship from one-to-one to one-
to-many and .... what ever. Any suggestions pls.

If there is never going to be more than one record per product in
TblB, I'd suggest just using a single table; why add the complexity of
a second table?

If you will have more than one record in tblB per product (or want the
two tables anyway), use a Form based on tblA and a Subform based on
tblB, using the ProductID as the master/child link field. Don't use
table datasheets for data entry, they are much too limited in their
capabilities - use a Form, it's the right tool for the job!

 

[Tim Ferguson]

the PK for the Products table is ProductID (auto number)
the PK for the Charges table is ChargeID (auto number)

Relationship Type is One-To-One

You will have to go out of your way to create a 1:1 relationship with this
setup...

Table/Query:tblProduct (ProductID) – Related
Table:tblCharge (ChargeID)

Oh, hold on: you've tried to make a relationship between two autonumber PKs
-- I'm afraid this is never going to work because (by definition) you
cannot control what value goes in an autonumber field. Just delete this
relationship and start again.

2)
There is the ProductID (number and Long Integer) in the
tblCharge but the value of that field seems to be 0 all
the time.

Well, it needs to be the value of a valid tblProduct.ProductID, doesn't it?
THIS is what the relationship should be based on: tblProduct.ProductID ->
tblCharge.ProductID. The plan is to know which product each charge relates
to.

Oh, and another nastiness is that Access will default the DefaultValue of
numeric fields to zero, when it should be null. It will help your
application no end if you go back to table design and delete the zero for
the DefaultValue for the tblCharge.ProductID.

Ive been living with this by opening tblCharge and adding
a new record straight to the tblCharge.

Well, you need some way of making new tblCharge records: one is to have a
Charges form, with a method of picking which product they relate to;
another is to have a Products form with a subform showing details from the
Charges table.

By the way, all this is possitted on you actually wanting a 1:many
relationship between Charges and Products. If you really do want a 1:1
relationship... Why?

All the best

Tim F