Conditional formatting on subdatasheets (1 Viewer)

[alikona]

Hi all,

Some more questions on the project I'm working on.

I have an Order Details form that contains two subdatasheets. One for Line Items and the other is for Shipments. Each order can have multiple line items, and each line item can have multiple shipments. How it is set up now, when you click/select a line item row it will display the corresponding shipments.

I would like to add some kind of visual indicator when a line item has been fully shipped. I could probably do this by adding another calculated field to my line item query or another text box on my main form, but its getting a bit full and I don't want the user to have to horizontally scroll to see info.

I thought maybe I could use conditional formatting to change the color to visually signify this to the user. I've not really used conditional formatting much in access so I'm not sure if what I want to do is not possible or if i'm doing it wrong. In my searches, I also seen a few people recommended changing the subform to continuous and then placing a colored box behind the controls that turns visible when the conditions are met. I would like to see if its possible to do what I want with datasheets before I redesign my subforms.


Database info
Main Form: frm_OrderDetails
Subform 1: subf_LineItems
Subform 1 control: ctr_LineItems
Subform 2: subf_Shipments
Subform 2 control: ctr_Shipments

On the subf_Shipments form, I have a text box in the footer called ShipTotal with the following as the Control Source:

=Sum([QtyShipped])

For my conditional formatting, I am trying to compare LineQty from the ctr_LineItems with the ShipTotal from ctr_Shipments. If the values are equal, it will highlight a field in ctr_LineItems. Here is the expression I have:

[LineQty]=Forms!frm_OrderDetails![ctr_Shipments].Form!ShipTotal

Now, this somewhat works. In my sample data I have 3 line items, two of which I've completed shipments on. This conditional formatting will change the first line item to the desired highlight but not the second. If I select the second line item and hit apply, it will apply the highlight but remove it from the first line item.

Any ideas?

 

[theDBguy]

Hi. First, just to let you know, I don't use subdatasheets, so I'm no expert on this subject. Second, I don't know if conditional formatting can be applied to tables, queries, or subdatasheets, since I've only used them in forms and reports. Good luck!

 

[vba_php]

alikona,

I don't have much to offer, like always p), but I'm pretty sure datasheets can't accept formatting because the definition of those objects have specs in them (that are created by the platform creators) which only cater to hard data (bytes). I may be offbase, but that's my side view. Also, I did this illustration years ago of "striping" a report with different colors based on alternating records of a continous form look. I'm not sure if you can get anything from it or not....but it came to mind.

 

[isladogs]

As already stated you can't add conditional formatting to datasheets (or subdatasheets) in tables or queries though some colour formatting is possible.
See http://www.mendipdatasystems.co.uk/add-query-colour/4594424152.

Like the previous responders, I don't use subdatasheets as they can be confusing to end users and also reduce performance

However whilst you can of course use conditional formatting with datasheets in forms, I don't think that's possible with subdatasheets in forms.

Suggest you use continuous forms and subforms instead

 

[alikona]

As already stated you can't add conditional formatting to datasheets (or subdatasheets) in tables or queries though some colour formatting is possible.

Like the previous responders, I don't use subdatasheets as they can be confusing to end users and also reduce performance

However whilst you can of course use conditional formatting with datasheets in forms, I don't think that's possible with subdatasheets in forms.

Suggest you use continuous forms and subforms instead

Perhaps I was not clear in my original post. They are not subdatasheets per-say. My form has several subforms that are displayed as datasheets. They do not contain subdatasheets.

It appears you can use conditional formatting on subforms in datasheet view, as my expression partially works. Messing around with it, I was also able to use conditional formatting using an expression that compares fields from both subforms using the following expression on the QtyShipped field in the ctr_Shippments subform control. I would post an image but I do not currently have enough posts to do so.

Sum([QtyShipped])=[Forms]![frm_OrderDetails]![ctr_LineItems].[Form]![LineQty]

Why it does not work the other way around, no idea. Perhaps it will only look at the current record selected in the Line Item instead of all of them.

 

[theDBguy]

I would post an image but I do not currently have enough posts to do so.

Perhaps when you can, you might consider posting a copy of your db instead of just images, so we can better see what you mean. Just a thought...

 

[vba_php]

I would post an image but I do not currently have enough posts to do so.

i forget...can you post links with less than 10 posts? if you can, post a link to a cloud drive where the image sits on that server. i do that all the time with google drive around here. dbGuy is correct though, an actual DB file would be a better way to go.

 

[isladogs]

I'm still unclear what you mean.
You can post images now if you zip them but next post you hit 10 anyway

 

[alikona]

See attached images. On the first line item, the conditional formatting is correct. In image 2, the conditional formatting does not apply, even when the focus is on it.

If I were to select the second record in Line Items, open the conditional formatting window and click apply, it would highlight the 2nd record while unhighlight the 1st.

The conditional formatting works correctly on the Shipments subform though.

 

[theDBguy]

See attached images. On the first line item, the conditional formatting is correct. In image 2, the conditional formatting does not apply, even when the focus is on it.

If I were to select the second record in Line Items, open the conditional formatting window and click apply, it would highlight the 2nd record while unhighlight the 1st.

The conditional formatting works correctly on the Shipments subform though.

Hi. Looking at your images doesn't tell me what your conditional expressions are to be able to say why they don't work.

 

[isladogs]

Well that's nothing like the original question seemed to be asking about.

Can you do a screenshot for the conditional formatting for the problem field.
BTW P/N# contains two non alphanumeric characters which isn't good if its the field name.
Similar issues with . characters in other field names
Also P/N# has a combo box indicating a table level lookup field. Also not recommended as well as probably being the reason why your CF is failing

 

[alikona]

Well that's nothing like the original question seemed to be asking about.

Can you do a screenshot for the conditional formatting for the problem field.
BTW P/N# contains two non alphanumeric characters which isn't good if its the field name.
Similar issues with . characters in other field names
Also P/N# has a combo box indicating a table level lookup field. Also not recommended as well as probably being the reason why your CF is failing

P/N# is just the label content, the actual field is named PartNumber. Same with the others that have spaces, the actual field name does not. (see screen) This field is set up as a combobox with a query that pulls info from tbl_PartNumber as the row source.

The query references CustomerLink, which is an unbound textbox on the main form that pulls the CustomerID autonumber from the dropdown box that displays the Customers name. This filters only the Part Numbers that are associated with that Customer ID.

Query code:
SELECT tbl_PartNumber.PartID, tbl_PartNumber.PartNumber, tbl_PartNumber.PartDesc, tbl_PartNumber.ProductLine, tbl_PartNumber.StandardCost, tbl_PartNumber.CustomerID, tbl_PartNumber.Type FROM tbl_PartNumber WHERE (((tbl_PartNumber.CustomerID)=[Forms]![frm_OrderDetails]![CustomerLink]));

The subform is also not directly displaying a table.

ctr_LineItems is displaying subf_LineItems which is linked to tbl_LineItems. Same with the ctr_Shipments, it is displaying subf_Shipments which is linked to tbl_Shipments and a few unbound text boxes that provide totals in the footer.

As for the conditional formatting expression that is not working as expected

[LineQty]=Forms!frm_OrderDetails![ctr_Shipments].Form!ShipTotal

So it should be looking at LineQty (Quantity in the Line Item table) and then the unbound textbox ShipTotal which is in the footer of Shipments subform.

It's not actually referencing the Part Number field. I tried the same expression on the LineQty field that it is actually referencing in the expression and it still is not working as expected.

 

[moke123]

alikona,
The screenshot you submitted, as Colin has pointed out, indicates you have a TABLE level lookup field which is evident from the little dropdown arrow. I could be wrong but thats what it looks like,

These are not recommended as they mask the actual data in the field. It is a common point where comparisons fail.
Google "The evils of lookup fields" for details.

 

[theDBguy]

Hi. Not sure what Google might give you, but here's a link to what I think moke123 meant for you to see.

The Evils of Lookup Fields in Tables

 

[moke123]

Hi. Not sure what Google might give you, but here's a link to what I think moke123 meant for you to see.

The Evils of Lookup Fields in Tables

Yes, I was just too lazy to look for it when I posted.