I am trying to create a simple customer support database that records what versions of which software products they own. I have three tables: system, productversion and product (trimmed down to the relevant fields here):
SYSTEM
----
systemid - primary key
productid - links to productversion.productvers
ionid
PRODUCTVERSION
----
productversionid - primary key
productid - links to product.productid
versiontext - e.g. "v2.4a (trial version)"
PRODUCT
----
productid - primary key
productname - e.g. "MyProduct Express Edition"
What I have therefore is a product, which has several versions for the user to pick from. So on my SYSTEM form, I don't know how to get values looked up so when I drop a combo on the form, it shows the names of the products looked up in PRODUCT instead of the productid from SYSTEM.
In other words, I have a combobox that shows numbers instead of names, which is kind of correct in the data sense (it's showing the primary key values) but not ideal, because I want the name.
The second part of my problem is the version combo. This has the same problem as above, in that it just shows PRODUCTVERSIONID instead of VERSIONTEXT, but it also needs to be able to narrow the list down so that instead of showing all available versions for every software product, the list updates to only show VERSIONTEXTs for the current product.
I have *almost* achieved my goal, but only by using unbound controls that I update myself - I'm sure there's a better, more elegant and less clunky way to do this...
Hopefully my explanation isn't too confusing - if you needs any clarification, just ask. All help much appreciated!
Start Free Trial
