Continuous Form alternate background color (1 Viewer)

oxicottin

Learning by pecking away....
Local time
Today, 17:09
Joined
Jun 26, 2007
Messages
856
Is it possible to alternate the background color based on a text box (txtProductID) in each row in a continious form?
For example, my forms detail section has a back color and an alternate back color which I still want BUT I want it based.
on the text box (txtProductID).

In the example image (sorry its crappy) it starts out as yellow and highlights the first 3 because the (txtProductID) numbers are all a 1. then the second section it highlights 2 rows because the (txtProductID) numbers are all 2 and last it highlights 3 because the (txtProductID) numbers are all a 3 and so on alternating between two colors down the continuous form.

The (txtProductID) could be any number I just made them 1,2,3 what matters is they are grouped together, and that block needs to be the same color background. I have done this on a report but no idea how OR if it would work in a form.

Capture.JPG
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 17:09
Joined
May 21, 2018
Messages
8,609
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:09
Joined
Oct 29, 2018
Messages
21,553
Not sure but you might be able to simulate this by using Conditional Formatting on a control that covers the entire Details section.

Edit: Oops, too slow...
 

oxicottin

Learning by pecking away....
Local time
Today, 17:09
Joined
Jun 26, 2007
Messages
856
@MajP im having a bit of trouble. I get everything working like in their example under the example I'm looking for frmFormReportSimple and the qryFormReportSimple BUT if you see in their example, they have one company name under table C and it only shows on the first line and the same company name under table nwdOrderDetails it shows on every line. How did they get the company name to show on just one line, thats what im after along with the coloring which that part is working...
 

Attachments

  • Capture1.JPG
    Capture1.JPG
    99.7 KB · Views: 59
  • Capture2.JPG
    Capture2.JPG
    139.3 KB · Views: 60

June7

AWF VIP
Local time
Today, 13:09
Joined
Mar 9, 2014
Messages
5,497
Look at the form's RecordSource SQL. This effect is achieved by complex nested subquery with alias name of C which retrieves a single OrderPK for each customer and LEFT JOINS on this field.

Accomplish this in a report with textbox HideDuplicates property.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 22:09
Joined
Feb 19, 2013
Messages
16,683
it is in the sql - 'table C' is a query

(SELECT nwdCustomers.CompanyName, Min(nwdOrderDetails.OrderPK) AS MinOfOrderPK FROM (nwdCustomers INNER JOIN nwdOrders ON nwdCustomers.CustomerID = nwdOrders.CustomerID) INNER JOIN nwdOrderDetails ON nwdOrders.OrderID = nwdOrderDetails.OrderID GROUP BY nwdCustomers.CompanyName) AS C

This returns the minimum orderPK for each customer. In the main query, using a left join it is only returned for the rows which have the minimum orderPK

Ahh - beaten by June
 

oxicottin

Learning by pecking away....
Local time
Today, 17:09
Joined
Jun 26, 2007
Messages
856
Thanks for explaining @June7 and @CJ_London but I'm so confused on how to do this.... attached is an image of my query and I wanted to do this with Product which is the column I want only the first name like their "Customer Name" can you explain how I can do this with mine? Thanks!

SELECT tbl_InventoryDetails.InventoryOverviewID, tbl_InventoryDetails.ProductDataID, tbl_Products.ProductID, tbl_Products.Product, tbl_ProductData.Item, tbl_InventoryDetails.Count, tbl_ProductData.strProductLength, rownumber([tbl_Products]![Product]) AS grp1
FROM tbl_Products INNER JOIN (tbl_ProductData INNER JOIN tbl_InventoryDetails ON tbl_ProductData.ProductDataID = tbl_InventoryDetails.ProductDataID) ON tbl_Products.ProductID = tbl_ProductData.ProductID
ORDER BY tbl_Products.Product;
 

Attachments

  • Capture3.JPG
    Capture3.JPG
    76.1 KB · Views: 55

June7

AWF VIP
Local time
Today, 13:09
Joined
Mar 9, 2014
Messages
5,497
Why do you want to do this on a form? The resulting dataset is not editable because it involves aggregate query.

Posted SQL does not show attempt to apply example.

Provide data and I might be able to figure this out.
 
Last edited:

CJ_London

Super Moderator
Staff member
Local time
Today, 22:09
Joined
Feb 19, 2013
Messages
16,683
agree - provide some data - and an example of what the form should look like
 

oxicottin

Learning by pecking away....
Local time
Today, 17:09
Joined
Jun 26, 2007
Messages
856
@June7 and @CJ_London here is a stripped-down version of what I have so far.
 

Attachments

  • Capture1.JPG
    Capture1.JPG
    54.6 KB · Views: 60
  • Test_FormLikeReport_v1.0.accdb
    796 KB · Views: 77

June7

AWF VIP
Local time
Today, 13:09
Joined
Mar 9, 2014
Messages
5,497
Here it is in 3 steps, maybe that will help to understand:

Query1:
SELECT tbl_ProductData.ProductID, tbl_Products.Product, Min(tbl_ProductData.Item) AS Itm
FROM tbl_Products INNER JOIN tbl_ProductData ON tbl_Products.ProductID = tbl_ProductData.ProductID
GROUP BY tbl_ProductData.ProductID, tbl_Products.Product;

Query2:
SELECT tbl_ProductData.ProductDataID, tbl_ProductData.ProductID, Query1.Product
FROM Query1 INNER JOIN tbl_ProductData ON Query1.Itm = tbl_ProductData.Item;

Query3:
SELECT Query2.Product, tbl_ProductData.Item, tbl_ProductData.strProductLength
FROM Query2 RIGHT JOIN (tbl_Products INNER JOIN tbl_ProductData ON tbl_Products.ProductID = tbl_ProductData.ProductID) ON Query2.ProductDataID = tbl_ProductData.ProductDataID
ORDER BY tbl_ProductData.ProductID, tbl_ProductData.Item;

This query could replace Query1 and Query2 although it might perform slower:
SELECT tbl_ProductData.ProductDataID, tbl_Products.ProductID, tbl_Products.Product
FROM tbl_Products INNER JOIN tbl_ProductData ON tbl_Products.ProductID = tbl_ProductData.ProductID
WHERE (((tbl_ProductData.[ProductDataID]) In (SELECT TOP 1 ProductDataID FROM tbl_ProductData AS Dupe
WHERE Dupe.ProductID=tbl_ProductData.ProductID ORDER BY Item)));
 
Last edited:

oxicottin

Learning by pecking away....
Local time
Today, 17:09
Joined
Jun 26, 2007
Messages
856
@June7 I created the 3 Querys from your SQLs above and ran the Query3 and it seperates like I wanted BUT now im missing my count that I needed. I tried adding the tbl_InventoryDetails to Query3 and the field "Count" and "InventoryOverviewID" but then all the format changed when I ran the form frm_InventoryOverview.
 

June7

AWF VIP
Local time
Today, 13:09
Joined
Mar 9, 2014
Messages
5,497
This would be a count of Items for each ProductID? Do you want the count to show only once for each product group like the Product name? Include a Count(Item) in Query1 and pull that field into subsequent queries.

Query3 result
ProductCIItemstrProductLength
Product1
4​
60005278​
10'
60005279​
9'
60005280​
8'
60005281​
Blank
Product2
7​
60005258​
6' 9"
60005259​
8'
60005260​
9'
60005261​
10'
60005262​
12'
60005263​
Blank
60005552​
7'
Oh, you want the Count field from tblInventoryDetails. BTW, that is a bad field name. Just a minute.
 
Last edited:

June7

AWF VIP
Local time
Today, 13:09
Joined
Mar 9, 2014
Messages
5,497
Okay, build another aggregate query.

Query2a
SELECT tbl_InventoryDetails.ProductDataID, Sum(tbl_InventoryDetails.Count) AS SumOfCount
FROM tbl_InventoryDetails
GROUP BY tbl_InventoryDetails.ProductDataID;

Change Query3
SELECT Query2.Product, Query2a.SumOfCount, tbl_ProductData.Item, tbl_ProductData.strProductLength
FROM tbl_Products INNER JOIN ((Query2 RIGHT JOIN tbl_ProductData ON Query2.ProductDataID = tbl_ProductData.ProductDataID) INNER JOIN Query2a ON tbl_ProductData.ProductDataID = Query2a.ProductDataID) ON tbl_Products.ProductID = tbl_ProductData.ProductID
ORDER BY tbl_ProductData.ProductID, tbl_ProductData.Item;


ProductSumOfCountItemstrProductLength
Product1
4.25​
60005278​
10'
5.75​
60005279​
9'
100​
60005280​
8'
14.5​
60005281​
Blank
Product2
60005258​
6' 9"
60005259​
8'
60005260​
9'
60005261​
10'
60005262​
12'
60005263​
Blank
60005552​
7'
 

oxicottin

Learning by pecking away....
Local time
Today, 17:09
Joined
Jun 26, 2007
Messages
856
Ok, @June7 this is a data entry form that needs a "Count" for each Product(s)/Length(s). This database is a monthly label count for inventory. Summing the count for each Product isnt what I needed I just need to enter a count each month for inventory.

Example:

Product1 8' the count is 3
Product1 9' the count is 5
product1 10' the count is 1

Product2 8' the count is 9
Product2........
Product2........

@June7 I tried the Query2a and I am unable to enter anything into the count column? Attached is updated with your querys...

Thanks,
 

Attachments

  • Capture.JPG
    Capture.JPG
    16.2 KB · Views: 50
  • Test_FormLikeReport_v1.1.accdb
    784 KB · Views: 58
Last edited:

June7

AWF VIP
Local time
Today, 13:09
Joined
Mar 9, 2014
Messages
5,497
It's a data entry form? The form's recordset is not editable. You can't enter data.
 

oxicottin

Learning by pecking away....
Local time
Today, 17:09
Joined
Jun 26, 2007
Messages
856
It's a data entry form? The form's recordset is not editable. You can't enter data.

Sorry I didnt see your post before adding files to above post... Yes its a data entry form and the only data entry in the subform is the Count text box.
 

June7

AWF VIP
Local time
Today, 13:09
Joined
Mar 9, 2014
Messages
5,497
I pointed this out back in post #8. My recommendation is you abandon this bizarre finagling of data and just use normal form/subform presentation.
 
Last edited:

CJ_London

Super Moderator
Staff member
Local time
Today, 22:09
Joined
Feb 19, 2013
Messages
16,683
Not being editable is also mentioned in the original thread you got the code from
 

oxicottin

Learning by pecking away....
Local time
Today, 17:09
Joined
Jun 26, 2007
Messages
856
I am leaving it the way I have it which IS editable, and the sections are highlighted, the only thing is I have the product name on each line which is fine... Thanks you guys for giving it a go, Sorry I didn't see the.... (Not being editable is also mentioned in the original thread you got the code from)
 

Users who are viewing this thread

Top Bottom