data is repeating itself

  • Thread starter Thread starter tboyce
  • Start date Start date
T

tboyce

I have set up a DB for our Transport Managment Dept. Containing a main table
holding our trucks details (Vehicle Details). I have created a Servicing,
Maintenance, Inventory, Equipment, Defects and Equipment tables. I have
created a Single Main form with sub forms for all the tables. Everything
seems fine with all the tables except Inventory subform. If i enter data
under one Registration, that entry is duplicated across the fleet and when i
delete that entry so do all the other 13 entries. I have checked the
relationships which is the same throughout all relationships (To Registration
Number) and includes all records and only those to the reg to show up in all
of them. I have only one Primary Key in each table which is an Auto number,
with the Reg as a Foreign Key ( I Think!!). What am i doing wrong with this
one table for it to duplicate data in this way. Many thanks to all you people
out there that spend an age helping others like little me!
 
It sounds like you have not correctly set up the
LinkMasterFields/LinkChildFields properties of your subform control.

Check in the table that stores the Inventory records. Are there really
duplicate records in there, or are there just records with no VehicleID
foreign key value?
 
It almost sounds like the master/child properties have not been created
correctly in the forms.
 
I have set up a DB for our Transport Managment Dept. Containing a main table
holding our trucks details (Vehicle Details). I have created a Servicing,
Maintenance, Inventory, Equipment, Defects and Equipment tables. I have
created a Single Main form with sub forms for all the tables. Everything
seems fine with all the tables except Inventory subform. If i enter data
under one Registration, that entry is duplicated across the fleet and when i
delete that entry so do all the other 13 entries. I have checked the
relationships which is the same throughout all relationships (To Registration
Number) and includes all records and only those to the reg to show up in all
of them. I have only one Primary Key in each table which is an Auto number,
with the Reg as a Foreign Key ( I Think!!). What am i doing wrong with this
one table for it to duplicate data in this way. Many thanks to all you people
out there that spend an age helping others like little me!
Click to expand...

sounds like something's wrong with that subform. The LinkChild and
LinkMaster properties are set and this still happens? Did you enforce
referential integrity between the two tables in the relationships
window?

To check the source of the problem, open the table and see how many
entries are in it. If that's the number of subform/child records for
every record in the main form, then that's the problem.
 
U hit the nail on the head there are not duplicate entries in the inventory
table, only one entry but no reg entered as i entered into the sub form of
the vehicle detail form ( Main form) so what have i not done right? Nice 1

Graham Mandeno said:
It sounds like you have not correctly set up the
LinkMasterFields/LinkChildFields properties of your subform control.

Check in the table that stores the Inventory records. Are there really
duplicate records in there, or are there just records with no VehicleID
foreign key value?
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

tboyce said:
I have set up a DB for our Transport Managment Dept. Containing a main
table
holding our trucks details (Vehicle Details). I have created a Servicing,
Maintenance, Inventory, Equipment, Defects and Equipment tables. I have
created a Single Main form with sub forms for all the tables. Everything
seems fine with all the tables except Inventory subform. If i enter data
under one Registration, that entry is duplicated across the fleet and when
i
delete that entry so do all the other 13 entries. I have checked the
relationships which is the same throughout all relationships (To
Registration
Number) and includes all records and only those to the reg to show up in
all
of them. I have only one Primary Key in each table which is an Auto
number,
with the Reg as a Foreign Key ( I Think!!). What am i doing wrong with
this
one table for it to duplicate data in this way. Many thanks to all you
people
out there that spend an age helping others like little me!
Click to expand...
Click to expand...
 
If i enter data
under one Registration, that entry is duplicated across the fleet and when i
delete that entry so do all the other 13 entries.
Click to expand...

Did you perhaps base one of the forms on a Query joining the two tables?
Don't: the mainform should be based on the "one" side table (Fleet?) and the
subform on the "many".
 
No i haven't based any of the forms on queries. Does that all the sub forms
are all built the same way. There is only 1 index under each table and all
the relationships are one to many that seem ok. Thanx for ur assistance John
 
Can u help me any further??

tboyce said:
No i haven't based any of the forms on queries. Does that all the sub forms
are all built the same way. There is only 1 index under each table and all
the relationships are one to many that seem ok. Thanx for ur assistance John
Click to expand...
 
Sorry, I thought you'd solved it.

You need to set the LinkMasterFields/LinkChildFields properties of your
subform control.

LinkMasterFields should be the name of a control on your main form that is
bound to the primary key.

LinkChildFields is the name of the field in your subform's recordsource that
contains the related (foreign) key.
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

tboyce said:
U hit the nail on the head there are not duplicate entries in the inventory
table, only one entry but no reg entered as i entered into the sub form of
the vehicle detail form ( Main form) so what have i not done right? Nice 1

Graham Mandeno said:
It sounds like you have not correctly set up the
LinkMasterFields/LinkChildFields properties of your subform control.

Check in the table that stores the Inventory records. Are there really
duplicate records in there, or are there just records with no VehicleID
foreign key value?
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

tboyce said:
I have set up a DB for our Transport Managment Dept. Containing a main
table
holding our trucks details (Vehicle Details). I have created a
Servicing,
Maintenance, Inventory, Equipment, Defects and Equipment tables. I have
created a Single Main form with sub forms for all the tables.
Everything
seems fine with all the tables except Inventory subform. If i enter
data
under one Registration, that entry is duplicated across the fleet and
when
i
delete that entry so do all the other 13 entries. I have checked the
relationships which is the same throughout all relationships (To
Registration
Number) and includes all records and only those to the reg to show up
in
all
of them. I have only one Primary Key in each table which is an Auto
number,
with the Reg as a Foreign Key ( I Think!!). What am i doing wrong with
this
one table for it to duplicate data in this way. Many thanks to all you
people
out there that spend an age helping others like little me!
Click to expand...
Click to expand...
Click to expand...
 
I have checked the relationship which is based on the Vehicle Reg Field in
both tables as it is with all the other sub tables to the main form. All the
Reg fields are set to Integer with a field size of 8. All seems to be the
same detail throughout the tables, except the inventory. But as you said when
i enter data via the sub form and check to Inventory table the reg field is
blank. Does this help you any? Thanx again.
 
Almost certainly this is a problem with the LinkMasterFields/LinkChildFields
properties of your subform control. (See my previous follow-up post)

Have you checked them???
 
I am sorry if i am being thick here. I am trying to say the "Vehicle
Registration" is my Master/Child link to the Sub Form. As it is with all the
Sub forms that I have set up. All the others are ok and this relationship is
the same as all the others so what am i doing wrong?

Graham Mandeno said:
Almost certainly this is a problem with the LinkMasterFields/LinkChildFields
properties of your subform control. (See my previous follow-up post)

Have you checked them???
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand


tboyce said:
I have checked the relationship which is based on the Vehicle Reg Field in
both tables as it is with all the other sub tables to the main form. All
the
Reg fields are set to Integer with a field size of 8. All seems to be the
same detail throughout the tables, except the inventory. But as you said
when
i enter data via the sub form and check to Inventory table the reg field
is
blank. Does this help you any? Thanx again.
Click to expand...
Click to expand...
 
I am sorry if i am being thick here. I am trying to say the "Vehicle
Registration" is my Master/Child link to the Sub Form. As it is with all the
Sub forms that I have set up. All the others are ok and this relationship is
the same as all the others so what am i doing wrong?
Click to expand...

This may be repeating, but I haven't seen these particular questions answered:

What is the Recordsource of the mainform? Please post the SQL.
What is the Recordsource of the subform? Please post the SQL.
If you open the Recordsource query for the main form do you see repeating data
for each vehicle registration number?
Ditto for the child form?
 
Now i know i am thick. when i go to record souce on main form it asks me if i
want to invoke query builder so i clicked yes and it only said
Select From [Vehicle Details] as the query is empty. I am sorry for wasting
your time here and am not being very helpful for you. I it saying the same
for sub form do i want to invoke query builder. I can tell you that the main
form/table is fine no data repeats itself. It only in inventory form and when
i open that table all the data is there except the reg is missing. Please
dont give up me yet nobody else has answered by question. Thank you John
 
Now i know i am thick. when i go to record souce on main form it asks me if i
want to invoke query builder so i clicked yes and it only said
Select From [Vehicle Details] as the query is empty. I am sorry for wasting
your time here and am not being very helpful for you. I it saying the same
for sub form do i want to invoke query builder. I can tell you that the main
form/table is fine no data repeats itself. It only in inventory form and when
i open that table all the data is there except the reg is missing. Please
dont give up me yet nobody else has answered by question. Thank you John
Click to expand...

Now wait a minute.

I thought the problem was that - as the subject line says - "data is repeating
itself". Now it seems that "the reg" - whatever the reg might be, I don't know
- is missing.

Let's dig a bit more. Ok the mainform and subform are both based directly on
Tables. What are the (relevant) fields in these two tables - fieldnames and
datatypes? What - if any - Relationships do you have established involving
these tables, in the relationships window? What do you *expect* to see on the
form for some chosen record? What do you *actually* see on the form?
 
No i haven't set referential integrity on any of the relationships as of yet.
I have tried to explain that i have only one record in the Inventory table
but it has no Registration entered as the other tables have. I set up another
form on it today called Vehicles collected and delivered and that seems fine
too though i dare not enter data into it so that i dont increment the
autonumber field until i solve this issue. Thanks for ur help
 
Ok thank you for baring with me. The main Table/Form holds all the vehicles
in our fleet 14 trucks. It stores the TAX, MOT, Driver, Phone & Registration
Number.
I am trying to store data such as the inventory the servicing, maintenance,
reported defects (by drivers) for each vehicle via sub tables/forms. 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. 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. I hope everyone is getting this as this post is getting very
long and confusing (laughable) but many thanks for all your help. Someone
asked me to post the record source in SQL view but i haven't set the forms up
via a query. when i click on the builder in the record source in either the
main form or the inventory sub form it asks me if i want to invoke the query
builder but obviously its empty and when i click SQL view it only says Select
Vehicle details with no fields shown. Please help, I dont really want to
continue until this problem is solved

John W. Vinson said:
Now i know i am thick. when i go to record souce on main form it asks me if i
want to invoke query builder so i clicked yes and it only said
Select From [Vehicle Details] as the query is empty. I am sorry for wasting
your time here and am not being very helpful for you. I it saying the same
for sub form do i want to invoke query builder. I can tell you that the main
form/table is fine no data repeats itself. It only in inventory form and when
i open that table all the data is there except the reg is missing. Please
dont give up me yet nobody else has answered by question. Thank you John
Click to expand...

Now wait a minute.

I thought the problem was that - as the subject line says - "data is repeating
itself". Now it seems that "the reg" - whatever the reg might be, I don't know
- is missing.

Let's dig a bit more. Ok the mainform and subform are both based directly on
Tables. What are the (relevant) fields in these two tables - fieldnames and
datatypes? What - if any - Relationships do you have established involving
these tables, in the relationships window? What do you *expect* to see on the
form for some chosen record? What do you *actually* see on the form?
Click to expand...
 
Ok thank you for baring with me. The main Table/Form holds all the vehicles
in our fleet 14 trucks. It stores the TAX, MOT, Driver, Phone & Registration
Number.
Click to expand...

Is Registration Number the Primary Key of this table (it probably should be)?
I am trying to store data such as the inventory the servicing, maintenance,
reported defects (by drivers) for each vehicle via sub tables/forms.
Click to expand...

Don't forget: a Form IS JUST A WINDOW. You cannot store data in a Form; you
cannot make a relationship between a table and a Form. Get the tables right
first and then worry about the forms!
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.
Click to expand...

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.
Click to expand...

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.
I hope everyone is getting this as this post is getting very
long and confusing (laughable) but many thanks for all your help. Someone
asked me to post the record source in SQL view but i haven't set the forms up
via a query. when i click on the builder in the record source in either the
main form or the inventory sub form it asks me if i want to invoke the query
builder but obviously its empty and when i click SQL view it only says Select
Vehicle details with no fields shown. Please help, I dont really want to
continue until this problem is solved
Click to expand...

That just means that you're using the unsorted, unfiltered,
all-fields-selected table as the source of the form. That's ok, though you may
want at some point to (say) display the maintenance records in chronological
order; doing so would benefit from basing that subform on a Query sorting the
maintenance records.
 
No i haven't set referential integrity on any of the relationships as of yet.
I have tried to explain that i have only one record in the Inventory table
but it has no Registration entered as the other tables have. I set up another
form on it today called Vehicles collected and delivered and that seems fine
too though i dare not enter data into it so that i dont increment the
autonumber field until i solve this issue. Thanks for ur help
Click to expand...

Sorry I missed this message before I sent a reply to your other one...

If your inventory is an inventory of items *that do not belong to any truck at
all*, then a) you should not have a Registration Number in the inventory
table, b) you should not attempt to relate the tables on that number, and c)
you should not use the Registration Number as the master/child link field. How
will this inventory be used? Is it in fact a "shop stock" of items which
aren't yet assigned to any truck, but are available to do so?
 
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.
 
Back
Top