Data in continuous sub form goes blank when form opens in Access (1 Viewer)

[Bone2]

I have a main form (its record Source is a table, HC_Comp_Records_Table) called HC Forms and a sub form (its RecordSource is a table named HC_Product_Species_Table with column headings, Product_ID, Product_Type, Species, Net wt and Comp_ID), HC Species Subform which is set to open in continuous view. One the main form is a combo box called Company Name (its row source is a table called Companies List) which contains list of companies. There are two cascading combo boxes named ProductType (It's row source is a table, CompProductType table) and Species (Its row source is also a table, SpeciesListTable) on the subform. The values in ProductType depends on Company Name.

I have superimposed a textbox (txtSpecies) on the combo box Species

So far, at the point of data entry, the data I will select on the subform is visible. However, the subform becomes blank when the form opens or I navigate through records.

I have been searching the internet for almost a week now but no luck. I understand its difficult working with continuous forms as subform. Any help will be greatly appreciated

I have read through similar posts but i still cannot get mine to work

Database attached

 

[arnelgp]

did you set Allow Additions to Yes on your subform?

 

[MajP]

I have been searching the internet for almost a week now but no luck.

You should have come here a long time ago. You understand you cannot cascade in a continous form without some work around.
I use the overlaid textbox trick, but there is another

enabling cascading comboboxes in datasheet or continuous form

Having been asked about this recently, I reinvented some code I developed to work with datasheets and which also works with continuous and single forms. The attachment was developed in 2010 but saved as 2003. The db demonstrated two techniques for using cascading comboboxes. Technique 1 is...

www.access-programmers.co.uk

But you go more problems than that.
1. In your first subform, you should be saving the species ID and not the species name.
2. This for is not bound to the Species ID

 

[Bone2]

did you set Allow Additions to Yes on your subform?

Allow additions is set to Yes

 

[Bone2]

You should have come here a long time ago. You understand you cannot cascade in a continous form without some work around.
I use the overlaid textbox trick, but there is another

enabling cascading comboboxes in datasheet or continuous form

Having been asked about this recently, I reinvented some code I developed to work with datasheets and which also works with continuous and single forms. The attachment was developed in 2010 but saved as 2003. The db demonstrated two techniques for using cascading comboboxes. Technique 1 is...

www.access-programmers.co.uk

But you go more problems than that.
1. In your first subform, you should be saving the species ID and not the species name.
2. This for is not bound to the Species ID

thanks for your swift reply.
about using speciesID, does it mean i have to create two tables? one for product type and one for species and use a query for the subform?

 

[Bone2]

Thank you. I will take a look. i will be right back

 

[Bone2]

More fixes

MajP you rock. the issue now is with the second subform. I want the EU Number to appear based on the Vessel Name. Thanks

 

[MajP]

That form does not make any sense to me.
A vessel is registered to a company and that is in the Registered vessel table
What is the purpose of HC_Vessel Table? Is it a subset of all the companies registered vessels?
If that is the case then in the Registered vessels table you would simply have a field a boolean field "HC". No need to pull data from one table into another.

You have a lot of issues with normalization. You should never repeat fields in another table. If you store an ID field to another table do not store fields like Names in the child table. You main form should not have a combobox for Company Name and EU number. That should not be stored in the table ONLY Foreign Keys.

Also you need to use queries as your recordsources instead of using all these comboboxes to show related data.

 

[Bone2]

That form does not make any sense to me.
A vessel is registered to a company and that is in the Registered vessel table
What is the purpose of HC_Vessel Table? Is it a subset of all the companies registered vessels?
If that is the case then in the Registered vessels table you would simply have a field a boolean field "HC". No need to pull data from one table into another.

You have a lot of issues with normalization. You should never repeat fields in another table. If you store an ID field to another table do not store fields like Names in the child table. You main form should not have a combobox for Company Name and EU number. That should not be stored in the table ONLY Foreign Keys.

Also you need to use queries as your recordsources instead of using all these comboboxes to show related data.

I admit I have issues with normalization.

Registered Vessels table is meant to be solely be lookup table for the combo boxes "Vessel Name" and "Eu Number" whiles HC_Vessel_Table is meant to store the vessel data during data entry.

The issue is that some of the Companies are just processing plants (they do not own vessels) whiles other companies are not processing plants but operate vessels usually more than one.

The processing plants have their own EU Numbers but the vessel operators don't have EU Numbers (their vessels rather have EU Numbers)

Hope this helps

 

[MajP]

The issue is that some of the Companies are just processing plants (they do not own vessels) whiles other companies are not processing plants but operate vessels usually more than one.

I am sorry, but I still do not understand. Company 1 has two registered vessels. These can be shown in the subform based on the registered vessel table. Why do you have to copy them into table HC_Vessel table. What is the difference? If a company does not have a vessel what would you enter in this table? I am going to treat it as if a certification has a subset of the companies vessels.

I changed most of your keys. See if this makes more sense.

 

[MajP]

I am interpretting this is a one to many.
A company can have many certifications
A certification has many species
A certification has many vessels

The relationship is to the certification not the company for HC_Vessels and and HC_Species.

Do not use spaces in any names. Give primary keys unique names in all tables (not just ID). I always trail my foreign keys with FK. So they look like
PrimaryKeyName_FK
CompID_FK

 

[Bone2]

I am interpretting this is a one to many.
A company can have many certifications
A certification has many species
A certification has many vessels

The relationship is to the certification not the company for HC_Vessels and and HC_Species.

Do not use spaces in any names. Give primary keys unique names in all tables (not just ID). I always trail my foreign keys with FK. So they look like
PrimaryKeyName_FK
CompID_FK

Thank you for your patience. I know I have made a lot of mistakes. I am newbie. I am taking a look and come back ASAP

 

[Bone2]

Thanks MaJP. Its working perfectly now.

 

[MajP]

Good to hear. Next time come here before spending a week on the internet. Lots of people will be able to answer questions very quickly.