Hi again 2,
Adressing the sub-form;
There is an issue with Access when displaying multiple records like you want to do in the subform, whether in Datasheet view or in ContinuousRecord view, when cascade filtering. If you have four records all filtered on different values, you will only see the look-up on the one which has focus.
Starting with the query for sfrmOrder, on this occasion I have left tblItem included to get the item's description to mitigate the above mentioned problem.
The first comboBox is now cmbType, has RowSource selecting TypeID and Type, BoundColumn=1, ColumnCount=2, ColumnWidthe=0;2cms. Note that the ControlSource=TypeID (I did not mention in my last post but the ControlSources for cmbService and cmbWard were ServiceID and WardID, obvious really)
The second combo is now cmbItemID, where ControlSource=ItemID, ColumnCount=3, ColumnWidths=1.3;8;0cms, ListWidth=9.5cms but Width only = 1.3cms. The RowSource selects ItemID, Item and Type, where Type is filtered by the contents of the bound field of [cmbType].
The width has been reduced to 1.3cms but will expand to 9.5cms upon selection where the different item descriptions will be available.
Note that cmbTypeID and cmbWard are re-queried upon entry,e.g.
- Private Sub cmbItemID_Enter()
-
Me.cmbItemID.Requery
-
End Sub
-
Also, note that an After_Update event has been added to cmbTypeID to ensure that the Type cannot be changed to leave displayed an invalid Item for that Type.
-
Private Sub cmbTypeID_AfterUpdate()
-
Me.ItemID = 0
-
End Sub
The final change has been to add a textBox to display the item description. This has been called txtDescription and is bound to 'Item' from the query. On the DataTab it is set so Enabled=No and Locked=Yes, so it is for display only and can only be changed via the comboBox cmbItemID.
The subform can be displayed as continuous records (not very well aligned at the moment!)or in DataSheet mode, in the latter case check that the DatasheetCaptions are completed on the Others-Tab.
I think that's it!
S7