data is repeating itself

[John W. Vinson]

Have you made any sense of my last post John? Or have you give up on me? If
you feel my post has gone too far off the original problem could you direct
me to a post that may help me further. Many thanks for your assistance
regardless.

Ummmm... I thought I had responded to the issues in your last post. Do you
have any thoughts about *my* suggestions?

You need to establish RI, and otherwise get your table structures correct.
Your forms will continue to cause problems if you don't.

 

[tboyce]

Ummmm... I thought I had responded to the issues in your last post. Do you
have any thoughts about *my* suggestions?

You need to establish RI, and otherwise get your table structures correct.
Your forms will continue to cause problems if you don't.

John W. Vinson [MVP]

 

[tboyce]

I dont know if this will help you figure out what going on. I tried an
experiment on my forms to see what would happen. I included the reg field
from my servicing and maintenance forms and they were duplicating the reg on
my main Vehicle Details form but on the Inventory form the reg field was
blank throughout the fleet. Does this help explain why is this happening?

 

[John W. Vinson]

I dont know if this will help you figure out what going on. I tried an
experiment on my forms to see what would happen. I included the reg field
from my servicing and maintenance forms and they were duplicating the reg on
my main Vehicle Details form but on the Inventory form the reg field was
blank throughout the fleet. Does this help explain why is this happening?

Here are your questions and my answers to which I have not seen a response.
Maybe the web interface was losing some posts (it does that at times).

Quoting:

I have
set a registration number field in each sub table and linked each table to
the main form via the registration number. Each table has a primary key of an
auto number field. The relationship is a 1 to many on all the relationships i
have set up, with no ref integrity on any to the registration number.

Why not? A relationship without referential integrity is no relationship at
all, other than cosmetically. It makes no sense to talk about maintenance work
on a nonexistant truck... if you are billed for such work, you should contact
a good lawyer!!! Establish RI.

All the
other sub forms work fine except this inventory sub form. On the main form
when i click on the sub form of the inventory form it has a test entry say 2
x wheel straps on the first entry . But when i change to main form to view
another vehicle and click the inventory form. Each vehicle has 2x wheel
straps even though i haven't entered that data into any of them as yet. When
i go into the inventory table the data is shown only once but the
registration has not been recorded for the 1 vehicle that i tried entering
the data for.

That just about convinces me that the Master Link Field and Child Link Field
properties of this subform are either blank, or erroneous. Doublecheck that
the datatype and size of the Registration Number field matches in the truck
table and the inventory table. While you're at it, make sure RI is enforced on
this relationship. You might need to create a record for a "non-truck truck"
if you have an inventory of parts that don't belong to any particular truck; I
would expect that you might have a stock of windshield wipers and chains and
ground straps that could be put onto any truck, right???

Then, try *deleting the subform control* from your mainform. Compact and
repair the database. Then drag the Inventory form from the forms window onto
the main form (to establish it as a subform). Make sure that the registration
number is used as the master/child link field. This should keep the subform in
synch.


End Quote


Just so I understand: Do the items in the Inventory BELONG TO A TRUCK?

IF NOT... then the Reg number *should simply not exist* in the Inventory
table. A one to many relationship implies that there is a relationship. If the
inventory and the trucks are independent - if there is no connection between a
particular truck and a particular strap or wiper or air filter in the
inventory - then there *is no relationship* and a Master/Child link is
inappropriate.

 

[tboyce]

Here are your questions and my answers to which I have not seen a response.
Maybe the web interface was losing some posts (it does that at times).

Quoting:


Why not? A relationship without referential integrity is no relationship at
all, other than cosmetically. It makes no sense to talk about maintenance work
on a nonexistant truck... if you are billed for such work, you should contact
a good lawyer!!! Establish RI.


That just about convinces me that the Master Link Field and Child Link Field
properties of this subform are either blank, or erroneous. Doublecheck that
the datatype and size of the Registration Number field matches in the truck
table and the inventory table. While you're at it, make sure RI is enforced on
this relationship. You might need to create a record for a "non-truck truck"
if you have an inventory of parts that don't belong to any particular truck; I
would expect that you might have a stock of windshield wipers and chains and
ground straps that could be put onto any truck, right???

Then, try *deleting the subform control* from your mainform. Compact and
repair the database. Then drag the Inventory form from the forms window onto
the main form (to establish it as a subform). Make sure that the registration
number is used as the master/child link field. This should keep the subform in
synch.


End Quote


Just so I understand: Do the items in the Inventory BELONG TO A TRUCK?

Yes that is my intention. The Equipment table holds all the equipment
available to the fleet. The Inventory table is the equipment that each truck
should have to hand each day. When the amount of equipment falls below the
requirement then i will order more for that truck. So if two trucks need 2
winch cables each i would be required to order at least four cables. if a
driver goes out with 1 short then we would possibly give him extra training
to ensure he is doing his vehicle checks properly to go about his daily
duties with the correct equipment. So that why i try to get this corrected.

 

[tboyce]

Hi again still got same problem. The answers to questions being asked are
There aren't any queries involved in these forms.
The data is not duplicated in any tables concerned but in the inventory
table there is no record of the reg of the truck invloved when i enter data
through the sub form (Inventory) on the main form (Vehicle Details). I.e when
i scroll through the records on the other sub forms i get the data relevant
to that vehicle reg as it should be. When i scroll through each Vehicle with
the inventory sub displayed i see the same data being displayed on each
vehicle even if that vehicle inventory has not been entered as yet.
When i look in the inventory table there is only one entry in the table but
the reg field which is my link field is blank when it should read "A4 SCO"
the vehicle i assigned that piece of equipment to.

When i try to display the SQL recordsource it asks me if i want to invoke
the query builder to which i have said yes to but only gives this statement

SELECT
FROM [Vehicle Details];

and the sub
SELECT
FROM [Inventory];

I set ref integrity on this relationship and that gave me the 1 - many
symbol the relaionship seems ok to me. Any ideas anyone