Form Sub-form Relationship

[Teady91]

Hi All,
I am designing an asset management db. In my db i have a general table with information that is relevant to any type of asset. I also have several tables that contain data that is only relevant to certain types of asset, for example i have a table that stores test data for electrical tools, a table that stores information about machine accessories. these are in a many to one relation ship with the general table linked by a common field called AssetID.

Now i have a form that has tabs, "general" , "testing and calibration" etc.... and on each tab i have a sub form that relates to the different aspect of the asset.

I have worked out how to get the sub forms to show only the records relating to the record in the general form using a filter, my issue is when i go to enter a new asset... i want to be able to tab accross and enter each aspect of the asset that the main form is pointing to without having to type in the asset id on each subform.

My design may be flawed in the first place so please feel free to offer alternative methods of doing this.

Cheers
Taison

 

[pbaldy]

The normal way of keeping a subform in sync is with the Master/Child link properties of the subform container. They will solve both of your problems. If you used the wizard to add the subform, it should have asked you about a linking field or something to that effect.

 

[Teady91]

Ok cheers for that, i didnt use the wizard i just dragged my form in...

i have set the master/child link properties now, and it filters my existing test results correctly, however when i go to create a new asset, or a new test result for that asset i get a message box:

The LinkMasterFields property setting has produced this error: 'The object doesn't contain the Automation object 'tblAssetsBase''

???

 

[pbaldy]

What tables are the form and subform bound to? The error implies that something is missing, like you're put in a field that doesn't exist. What did you put in by the way? You just want a field name.

 

[Teady91]

the forms are record sources are querys, the main form's query draws from the base table and i also included the asset id from the Testing table.
I used the little builder thing (...), it put tblElecTest.Asset ID in the Link Child Field property and tblAssetsBase.Asset ID in the link master fields property...

 

[dfenton]

Lose the table name. You need only the field name.

And, for what it's worth, the Link properties are not limited to fields. They can be any expression or control whose value provides the right data to make the link.

 

[Teady91]

Sorry, its still not liking it...
Im now getting a message box saying:
"the specified field 'AssetID' could refer to more then one table listed in the from clause of your SQL statement"
Any ideas as to what i might be doing wrong?

I am quite new to this... my first attempt at creating this db i had one massive table with all the fields in it, i have had to relearn a some methods of db design.

Cheers, Teady

ps thanks for your replies so far

 

[Teady91]

Hi All,
disregard that last post, i have had a play around and fixed my problem.
still not quite sure what was wrong, it was something to do with my querys that the forms were based on...
if anyone was watching this thread with a similar problem and want to know how i fixed it, chuck a reply up and i will try and explain what i did :s
Thanks,
Taison