Continuous Form alternate background color (1 Viewer)

[oxicottin]

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.

 

[MajP]

Making a form look like a report

As a result of this post https://www.access-programmers.co.uk/forums/threads/emulate-reports-grouping-levels-with-forms.326697/ I thought I would diddle around and come up with some examples. This is a simple format - only shows the customer once and uses conditional formatting to change the...

www.access-programmers.co.uk

 

[theDBguy]

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]

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

 

[June7]

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]

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]

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;

 

[June7]

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.

 

[CJ_London]

agree - provide some data - and an example of what the form should look like

 

[oxicottin]

@June7 and @CJ_London here is a stripped-down version of what I have so far.

 

[June7]

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)));

 

[oxicottin]

@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]

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

Product	CI	Item	strProductLength
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.

 

[June7]

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;

Product	SumOfCount	Item	strProductLength
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]

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,

 

[June7]

It's a data entry form? The form's recordset is not editable. You can't enter data.

 

[oxicottin]

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]

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.

 

[CJ_London]

Not being editable is also mentioned in the original thread you got the code from

 

[oxicottin]

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)