Intelligent Data Input

[thebarslider]

I have a database with two columns of Media Type and File Location. Both columns use drop down menu's for data input. The Media type Column contains information about what type of document it entity is. For example: Hard Copy, Word File etc..... The File location column contains information on Physical Locations, for example: Filing Cabinent, Hard Disk or Zip Disk. I want to be able to restrict the options taken when choosing the File location based on the Media Type. For example i dont want Filing Cabinent to appear in the options if the document is a Word File. I have tried to write SQL statements for this but to no avail. Please help.

Thanks for you time, it is much appreciated.

Mark Butler.

 

[LittleSmudge]

Okay Mark - quite a reasonable request.

How does the system know which options will be valid for any given entry in Media Type?

You need to have a Validity table. This will look very much like what some people call a 'Bridging Table' in the middle of a Many-to-Many relationship.
This is reasonable because any Media Type could be stored in one of many locations and any location could store many Types.

Create a table called tblValidity
tblValidity has TWO fields. Call them MediaTypeRef and FileLocationRef. These are both Foreign Keys and are of the same data type and size as the Primary Keys in tblMediaType and tblFileLocation respecively.
Make these two fields a combined Primary Key for this table.


Now populate tblValidity by putting the first MediaType Primary Key in MediaTypeRef and the first valid FileLocation Primary Key in FileLocationRef
In the next record repeat the same value of MediaTypeRef until you have all valid values of FileLocationRef.
Then move on to the next MediaTypeRef and do it all again.

THEN
In the MediaType_AfterUpdate event you have the following code
cboFileLocation.RowSource = "SELECT FileLocationTextField FROM tblFileLocation INNER JOIN tblValidity ON tblFileLocation.FileLocationId = tblValidity.FileLocationRef WHERE tblValidity.MediaTypeRef = " & cboMediaType & ";"


cboMediaType will need the PrimaryKey to be the Bound Column.
If the Primary Key is of Number type then the above is fine.
If the Primary Key is of Text type then you'll need to delimit the combo box input value
Eg. = '" & cboMediaType & "';"




'ope-that-'elps.

G LS

 

[LittleSmudge]

Oh, and in the MediaType_AfterUpdate event code, after the line

cboFileLocation.RowSource = .. ..

you need to requery the FileLocation combo box so:-

cboFileLocation.Requery



G LS