Combo box filter from a combo box selection

Question

I have 4 tables
Department, cell, assets and work Orders.
I have 1 form
Work orders
On form work orders I have 3 combo boxes
Department With the data source as; SELECT TblDepartment.IDD, TblDepartment.Dept FROM TblDepartment;
Cell combo boxes data is dependent on the Department selected SELECT * FROM TblCell WHERE [Dept]=[Forms]![FrmWork_Order]![Department];
All the above works!
I would like to select the Assets that are related to the data selected in the Cell combo box
I have tried; SELECT * FROM TblAssets WHERE [Cell]=[Forms]![FrmWork_Order]![Cell];
This query doesn’t return anything.

Not sure what I’m doing wrong.

Answer 1

It appears from your post that the Department combo box's bound column is DepartmentIDD, though I suspect this might be a typo for DepartmentID. This would be the normal set up for such a combo box. If this is the case then the Dept column in TblCell should be a long integer data type as a foreign key referencing the DepartmentID primary key of TblDepartment, although you'll see the department name in the column. This will be the case if you used the 'lookup field' wizard to insert the column when designing the table. Check that this is the case. If it is the RowSource query should be:

SELECT CellID, Cell FROM TblCell WHERE Department = [Forms]![FrmWork_Order]![Department] ORDER BY Cell;

The combo box's other properties would be:

ControlSource: Cell
BoundColumn: 1
ColumnCount: 2
ColumnWidths: 0cm

If your units of measurement are imperial rather than metric Access will automatically convert the unit of the last one to inches. The important thing is that the dimension is zero to hide the first column.

In the Department combo box's AfterUpdate event procedure put|:

Me.Cell.Requery

This will reload the Cell combo box's recordset so that its list is restricted to those cells which relate to the selected department.

The same principles apply to the Asset combo box whose RowSource property would be:

SELECT AssetID, Asset FROM TblAsset WHERE [Cell]=[Forms]![FrmWork_Order]![Cell] ORDER BY Asset;

Similarly this combo box would be requeried in the Cell combo box's AfterUpdate event procedure with:

Me.Asset.Requery

Answer 2

Since I cannot see your database, this is just a guess. In the Focus event of your Combobox, try using:

Me.ComboboxName.Requery

Hope that helps...