question

John123-8936 avatar image
0 Votes"
John123-8936 asked KenSheridan-7466 answered

Combo box filter from a combo box selection

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.

office-access-dev
· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@John123-8936 Thanks for posting in our Q&A. Here is intune support. This issue is related to Microsoft Access. Given this situation, I will remove intune tag and add office-access tag. Thanks.

0 Votes 0 ·

It sounds like you're trying to use a cascading Combobox. Have you looked at any of the available online tutorials yet?

0 Votes 0 ·

No real help here

0 Votes 0 ·

Hi. Are you saying reviewing any of the available tutorials on cascading comboboxes was of no help to you at all? If so, was there any particular part you needed some clarification?

0 Votes 0 ·

Why isn't the second query working?

0 Votes 0 ·
thedbguy avatar image
0 Votes"
thedbguy answered

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...

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

KenSheridan-7466 avatar image
0 Votes"
KenSheridan-7466 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.