Multiple Subforms with Related Data

[funkymuppet]

Hi,



Looking for a bit of direction on designing a form to display information on Additions, Transfers and Usage for a specific Material ID - the DB relationships are shown below. The DB is a fly-tying materials DB, and materials are purchased, transferred between locations and used in tying. DB approach has been to define material types, and then have separate tables for additions, transfers and usage--the aggregate of the transactions then indicating the remaining balance in each location.



The MaterialID is the common data between each of the transaction tables. I use separate tables to define LocationID, SizeID, ColourID etc.

I need a form that show the transactions for a given MaterialID, and that also would allow me to filter which transactions appear in each of the three subforms using LocationID, SizeID and ColourID (as they are common to all three tables). Can anyone give a few pointers as to how to approach it?

I've had difficulty so far - as if I create a form based on the tblMaterialTypes table, and then have a combobox that selects MaterialID, then I get the Autonumber error when trying to change the combobox value and assigning it to MaterialID.

Ultimately, I'd like to generate a report that lists all the relevant transactions, and then gives me the aggregate balances in each of the locations. But that's for another day.

Many thanks indeed,

JW

[June7]

I don't understand "change the combobox value and assigning it to MaterialID".

If this combobox is for entry/selection of filter criteria it must be UNBOUND.

A dynamic parameterized query as form RecordSource is one way to filter records. Review: http://www.datapigtechnologies.com/f...tomfilter.html

Another method is to set form Filter and FilterOn properties: http://www.allenbrowne.com/ser-62code.html