Subform Data Not Displaying - filtering by one parameter only

[dhmlofi]

I have a subform (OrderDetails) within a main form (OrderHeader). The subform is in datasheet view. The first four fields are OrderID (the parent and child field with the main form), OrderHeaderID, ProductID and ProductDetailID.



ProductID and ProductDetailID are combo boxes, pulling data from their respective tables (bound to the autonumber primary key). The ProductID field works fine. If it`s relevant here, it is filtered on the SupplierID field taken from the main form. My problem is with the ProductDetailID field. With no filter in the Row Source it displays all the data, but as soon as I put a Like Criteria in there to filter it to only pull the data that match the ProductID field on the subform, data starts to not show.

When this happens, I see the ProductDetailID data that matches only one ProductID - as you`ll see in the attached image. If I click in a record with missing data and do a Refresh, then it switches and I see the ProductDetailID data for that record and matching ProductIDs, but not the others any more.

I`ve also attached a screenshot of my Relationships. I have a feeling this is where the issue might lie but I can`t see where. I`d appreciate some advice please.

[June7]

Not quite following this. I need to work with the data and forms for this issue. If you want to provide, follow instructions at bottom of my post.

[dhmlofi]

Thanks June7. I`ve attached both front and back end. The form in questions can either be accessed from the main menu - Incomplete Orders, or from the Navigation Pane - OrderHeader.
Much appreciated!

[June7]

Why does Combo49 (could be more informative name) have a RowSource that joins OrdersAll with Customers? Customers already in the OrdersAll query.
SELECT CustomerTag, JobName FROM OrdersAll ORDER BY CustomerTag, JobName;
Same question for Combo51. This is the same query as Combo49, just the fields in different order.
SELECT JobName, CustomerTag FROM OrdersAll ORDER BY JobName, CustomerTag;

I don't understand this arrangement but I guess your issue is not with those comboboxes but concerns the comboboxes on the OrderDetails subform.


LIKE operator is only relevant with wildcard (*), just use =.

ProductDetailID combobox:
SELECT ProductDetailID, ProductDesc, ProductID FROM ProductDetail WHERE ProductID = [ProductID];

Where is the code that requeries the dependent comboboxes?

I don't understand the Products and ProductDetail tables. Data appears to be duplicated. Why is ProductDetail dependent on Products?

However, as you discovered, dependent comboboxes with lookups will not behave nice on Continuous or Datasheet view forms.

Recommend not setting lookups in tables http://access.mvps.org/access/lookupfields.htm

Won't resolve the issue of dependent comboboxes with lookups on form, I just don't like lookups in tables.

The only way I know to handle this is to have an unbound and locked textbox that displays the alias descriptive data and next to it is the combobox sized to just show the dropdown.

[dhmlofi]

Why does Combo49 (could be more informative name) have a RowSource that joins OrdersAll with Customers? Customers already in the OrdersAll query.
SELECT CustomerTag, JobName FROM OrdersAll ORDER BY CustomerTag, JobName;
Same question for Combo51. This is the same query as Combo49, just the fields in different order.
SELECT JobName, CustomerTag FROM OrdersAll ORDER BY JobName, CustomerTag;

I don't understand this arrangement but I guess your issue is not with those comboboxes but concerns the comboboxes on the OrderDetails subform.


LIKE operator is only relevant with wildcard (*), just use =.

ProductDetailID combobox:
SELECT ProductDetailID, ProductDesc, ProductID FROM ProductDetail WHERE ProductID = [ProductID];

Where is the code that requeries the dependent comboboxes?

I don't understand the Products and ProductDetail tables. Data appears to be duplicated. Why is ProductDetail dependent on Products?

However, as you discovered, dependent comboboxes with lookups will not behave nice on Continuous or Datasheet view forms.

Recommend not setting lookups in tables http://access.mvps.org/access/lookupfields.htm

Won't resolve the issue of dependent comboboxes with lookups on form, I just don't like lookups in tables.

The only way I know to handle this is to have an unbound and locked textbox that displays the alias descriptive data and next to it is the combobox sized to just show the dropdown.

Thank you so much for this. I appreciate too your notes about the other combo boxes and Like command.

Having done what you suggest I am getting the ProductDetailID to show all the record data again, however it gives me all the ProductDetailID records, not filtered to the ProductID field. I need to be able to filter it this way as ultimately there could be a lot of variations of the one product. How can I do this? I was able to get all the records showing before. It was when I tried to get them to filter to the ProductID field that it started to go wrong!

Also, you say that Datasheet and Continuous form combo boxes will not behave nice.....what do you mean by not behaving nice? This database is not for me, but for a client. I`ve not had issues with comboboxes in datasheets before so am really curious to know what you mean, especially if I should be advising them to go with form view.

Thanks again

[dhmlofi]

Ok, in trying to think about this further....I`m wanting the ProductDetailID combobox to be filtered on the ProductID field from the OrderDetail table. It is currently filtered to the ProductID field from the Products table.

I`ve tried different things to change this around, but each thing I try, I either see no change (i.e. I see all the ProductDetailID data) or I`m back to seeing only one lot of data (what I posted about in the first place).

[June7]

Since the ProductID combobox is bound to the ProductID field of OrderDetails, the ProductDetailID is filtered by the value of the ProductID field from OrderDetails.

I mean that comboboxes with lookups on continuous/datasheet form will act as you show in the image you posted. Because there is only one combobox, as can be seen in design view, filtering the RowSource affects all instances of the combobox. This means alias values will not be available for all records when the combobox is filtered.

Finally had to use this as the ProductDetailID RowSource:
SELECT ProductDetailID, ProductDesc FROM ProductDetail WHERE ProductID = Forms!OrderHeader.OrderDetails.Form.[ProductID];

Then VBA code:
Private Sub ProductID_GotFocus()
Me.ProductID.Requery
End Sub

Private Sub ProductDetailID_GotFocus()
Me.ProductDetailID.Requery
End Sub

Because Access can sometimes be confused when controls and the object or field they are bound to have same name, I always give controls a name different from the object or field. This had me stymied for a while. Example: cbxProductID. Then the RowSource could be:
SELECT ProductDetailID, ProductDesc FROM ProductDetail WHERE ProductDetail.ProductID = [cbxProductID];
And the VBA code would have to reflect the change.

[dhmlofi]

Hmmmm....ok I think things are becoming clearer now (after many hours of wrangling with this!). In a nutshell, what I`m going to take away from your expertise is that basically I can`t do what I`m trying to do. Your workaround suggestion with the locked text box isn`t something I think I could convince my client to go with, so am going to recommend that they go without the ProductDetailID filter. I did try putting the OrderDetail subform as a single view form but again, couldn`t get everything to work as I wanted it.

Thank you very much for your time. I appreciate your input a lot. And duly noted about lookups on tables too!

[June7]

The locked textbox (also TabStop No) and adjacent combobox would have the appearance of a single control. The client need not even be aware of this design feature. I saw this applied by another poster here and it looked quite nice.

If they want a filtered list then give them a filtered list, why do they need to know the how?

[dhmlofi]

Thanks! Might be a long shot but do you happen to know where on this forum that application was talked about....so I can visualize it and figure out how it works?

[dhmlofi]

Right, I think I see where you`re going with this. So I have created an unbound combo box on ProductDetail and created VBA to update a text box with ProductDetailID as the control source. So far so good!

The upside being that I can filter the product details as I need them. Phew! Thank you. The downside being that when I click in one record on the subform, as it`s a datasheet, I see the same productdetail appear in every record. I think I will try doing the subform as single form view to get around this....unless you know of another way?

The ProductDetailID data of course is meaningless to the user so I`m also wondering how to show the ProductDesc field, i.e. instead of seeing the number 5, they see the data `red door`. I thought I could create another unbound text field for this, but haven`t got it to work yet. I tried =[ProductDetail]![ProductDesc] but that gives me the Name? error. Am I aiming for the right solution here and if so, how can I make it work?

Thanks again!

[June7]

Can't find the other thread, sorry.

Actually, the combobox should still be bound. This will save the ID.

Try the following to accomplish this approach.

RecordSource for the OrderDetails subform:
SELECT OrderDetail.*, Products.ItemName, ProductDetail.ProductDesc
FROM Products RIGHT JOIN (ProductDetail RIGHT JOIN OrderDetail ON ProductDetail.ProductDetailID = OrderDetail.ProductDetailID) ON Products.ProductID = OrderDetail.ProductID;

Bind textboxes to ProductDesc and ItemName fields. Set properties TabStop No, Locked Yes.

Bind comboboxes to fields of OrderDetail table.

If you want to be able to resize and arrange the controls that are 'grouped', need to remove layout. Select all the controls, right click > Layout > Remove.

[dhmlofi]

Can't find the other thread, sorry.

Actually, the combobox should still be bound. This will save the ID.

Try the following to accomplish this approach.

RecordSource for the OrderDetails subform:
SELECT OrderDetail.*, Products.ItemName, ProductDetail.ProductDesc
FROM Products RIGHT JOIN (ProductDetail RIGHT JOIN OrderDetail ON ProductDetail.ProductDetailID = OrderDetail.ProductDetailID) ON Products.ProductID = OrderDetail.ProductID;

Bind textboxes to ProductDesc and ItemName fields. Set properties TabStop No, Locked Yes.

Bind comboboxes to fields of OrderDetail table.

If you want to be able to resize and arrange the controls that are 'grouped', need to remove layout. Select all the controls, right click > Layout > Remove.

Hi June7. Ok, so I guess I didn`t get where you were going!

I changed the RecordSource for OrderDetails subform, created the text boxes and amended the combo boxes. I now have combo boxes which set the value in the text boxes. My issue now is that because the combo boxes are bound to the OrderDetail table, I see all products from the OrderDetail table, i.e. I see several of each product. The same goes for the ProductDetail combo box. What have I missed?

I`ve attached the most up to date version of what I`m working on. Although the combo boxes are bound to the OrderDetail table, I included the Product and ProductDetail tables respectively to the RowSource so that I could get the ItemName and ProductDesc fields to show respectively - so that there is more than just the ID field. Also so I could filter the Product to the SupplierID from the main form.

Many thanks again!!

[June7]

Need to set the subform as Continuous then resize and rearrange the controls horizontally to appear as Datasheet. Put the label controls in the form header section. To do these edits select all controls > right click > Layout > Remove.

Don't understand why you changed RowSources to be join with OrderDetail.

ProductID combobox RowSource:
SELECT ProductID, ItemName FROM Products WHERE SupplierID=[Forms]![OrderHeader]![SupplierID];

If you rename ProductID combobox to cbxProduct and fix code then ProductDetailID combobox RowSource:
SELECT ProductDetailID, ProductDesc FROM ProductDetail WHERE ProductID=[cbxProduct];

Not seeing code to requery the comboboxes.

[dhmlofi]

It`s working!!

Thank you, thank you, thank you! I have spent so many hours wrangling with this I thought it would never work. Thank you for your patience and your time in helping resolve this. I`d also never have thought about setting the continuous form up in a datasheet layout....so obvious!

I can see this database needing similar abilities down the road as it expands, so I now have the know-how.

You`re a star