Filter Multiple Subforms/Data Entry

[captain1974]

I'm getting error 3341 when trying to enter data into subforms.

I have set up a database with mulitple tables, these are the relevant ones:
tblVineyard
VineyardID
VineyardName
VineyardAddress
VineyardRegion
etc
tblBlock
BlockID
VineyardID
BlockName
Variety
etc
tblBlockHarvest
HarvestID
BlockID
tblSectionHarvest
SectionID
HarvestID
Date
Tonnage
etc
One vineyard can have many blocks, one block can have many harvests and each harvest can have many sections. I am confident that the relational side of things is all ok, I have built queries which are all returning the desired results.
The form I am setting up to allow data entry:
The main form contains the vineyard name, the next subform contains the BlockHarvest details & the next subform contains the SectionHarvest Details. I have set up a filter to select the vineyard at the top from a combobox, the 2 subforms then display the relevant data. (a list of harvests in datasheet view, if you click on a record within this list it displays info on the SectionHarvest subform). I have also setup the filter to list in a combobox only the fields that belong to the vineyard selected at the top. This combobox is placed into the BlockHarvest section, the trouble is I am getting error 3341 "The current field must match the join key '?' in the table that serves as the 'one' side of one-to-many relationship." whenever I try to add new data to the BlockHarvest subform.

I have tried stripping down the form to allow data entry of the blockIDs (which is how I have entered some of the data thus far directly into the table tblHarvestBlock) & I am actually getting the same error without any of the code to control filtering etc.

If I open the subform directly on it's own without opening the main form, it allows data entry but the filter is not applied to only list blocks from the relevant vineyard. (ie it is a long list and you do not know which vineyard the blocks are from).

I have also tried building the form from a query, however this will only create new vineyard records rather than select from a list.
Any help would be appreciated, I would actually be happy to have pop-ups to enter the data rather than the subforms, however I am having difficultly filtering these.

 

[MStef]

As I can see you have got a form for "tblVineyard", (main form),
for "tblBlockHarvest" (subform), for "tblSectionHarvest" (next subform)
What about "tblBlock"??

 

[captain1974]

Yes, I have tried adding the subform tblBlock into the main form, with no success, same error 3341.

 

[unclejoe]

Hi,

Have you check the subform properties tab - Data - Link Child Fields and Link Master Fields?

Yes, I have tried adding the subform tblBlock into the main form, with no success, same error 3341.

 

[captain1974]

Hi,

Have you check the subform properties tab - Data - Link Child Fields and Link Master Fields?

The link Child and Master fields are set to VineyardID, I tried changing to BlockID, but this is not recognised & a prompt appears to enter the parameter value.

 

[Rich]

That's because you're adding the subforms to the wrong form,
Form block is a subform of Form Vineyard, Form Harvest is a subform of Form block, not Form vineyard etc

 

[MStef]

Hello captain!

Look at "DemoMF3subformsA2000.mdb",
I think it can help you. Adapt it on your task.

 

[captain1974]

That's because you're adding the subforms to the wrong form,
Form block is a subform of Form Vineyard, Form Harvest is a subform of Form block, not Form vineyard etc

Thanks, that has definitely put me on the right tracks

 

[captain1974]

Look at "DemoMF3subformsA2000.mdb",
I think it can help you. Adapt it on your task.

Thanks MStef! That's pretty much how I want my form to look. (The relationships are the same as mine, though I have autonumbers for primary keys, I assume this won't effect it.)
The subform within the form is obviously what I was doing wrong. When I tried to create the sub-subform I got a '+' sign to expand to the related records. How do I make it look like your subforms?
Thanks again

 

[MStef]

I don't know anything about '+' sign. Simply try.
Make all 4 forms on 4 tables, then put frmSectionHarvest as a subform in
frmBlockHarvest, them both together as a subform in frmBlock, and them all
(3) as a subform in frmVineyard. Look at in my Demo.

 

[captain1974]

I don't know anything about '+' sign. Simply try.
Make all 4 forms on 4 tables, then put frmSectionHarvest as a subform in
frmBlockHarvest, them both together as a subform in frmBlock, and them all
(3) as a subform in frmVineyard. Look at in my Demo.

Ok, the problem is that I when I create subforms they are in datasheet view, not tabular. So you're saying to create the forms first and then add them to a main form? I'll give this a try....