Continuous Form alternate background color

[MajP]

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

If interested, I will look at this, but I would think you could make this editable. This is not super efficient but the list is never going to be super long so should not be a problem. You need to remove the aggregate query and use a function instead. This is the same approach people do when they want to show "row numbers" per record. The function will pass an item and productid and return the Product name if it is the first record (sorted by Item) or null if not.

FYI, the field name "Count" is on the naughty list and is confusing to me because it can mean so many possible things. Normally I would expect the word to by "quantity"

Learn about Access reserved words and symbols - Microsoft Support

support.microsoft.com

 

[MajP]

Editable version.
The other choice would be to simply make that quantity field a pop up, but that may not be user friendly. Or you could have a second subform with the editable row.

'Functions to Get the Group Label
Public Function GetItemLabel(InventoryID As Variant, ProductID As Variant, ItemNumber As Variant) As String
  If IsNumeric(InventoryID) And IsNumeric(ProductID) And IsNumeric(ItemNumber) Then
    If ItemNumber = GetFirstItem(InventoryID, ProductID) Then GetItemLabel = GetProductName(CLng(ProductID))
  End If
End Function
Public Function GetFirstItem(InventoryID As Variant, ProductID As Variant) As Long
 If IsNumeric(ProductID) And IsNumeric(InventoryID) Then
   GetFirstItem = DLookup("FirstItem", "qryFirstItemByInventoryByProduct", "InventoryOverviewID = " & InventoryID & " AND ProductID = " & ProductID)
 End If
End Function
Public Function GetProductName(ProductID As Long) As String
   GetProductName = DLookup("Product", "tbl_Products", "ProductID = " & ProductID)
End Function
'Functions to Get Group Color
Public Function isAlternate(InventoryID As Variant, ProductID As Variant) As Boolean
  If IsNumeric(InventoryID) And IsNumeric(ProductID) Then
     isAlternate = (RowNumber(InventoryID, ProductID) Mod 2 = 0)
  End If
End Function
Public Function RowNumber(InventoryID As Variant, ProductID As Variant) As Long
  If IsNumeric(InventoryID) And IsNumeric(ProductID) Then
    RowNumber = DCount("*", "qryInventoryProducts", "InventoryOverviewID = " & InventoryID & " AND ProductID <= " & ProductID)
  End If
End Function

 

[oxicottin]

Editable version.
The other choice would be to simply make that quantity field a pop up, but that may not be user friendly. Or you could have a second subform with the editable row.

'Functions to Get the Group Label
Public Function GetItemLabel(InventoryID As Variant, ProductID As Variant, ItemNumber As Variant) As String
  If IsNumeric(InventoryID) And IsNumeric(ProductID) And IsNumeric(ItemNumber) Then
    If ItemNumber = GetFirstItem(InventoryID, ProductID) Then GetItemLabel = GetProductName(CLng(ProductID))
  End If
End Function
Public Function GetFirstItem(InventoryID As Variant, ProductID As Variant) As Long
If IsNumeric(ProductID) And IsNumeric(InventoryID) Then
   GetFirstItem = DLookup("FirstItem", "qryFirstItemByInventoryByProduct", "InventoryOverviewID = " & InventoryID & " AND ProductID = " & ProductID)
End If
End Function
Public Function GetProductName(ProductID As Long) As String
   GetProductName = DLookup("Product", "tbl_Products", "ProductID = " & ProductID)
End Function
'Functions to Get Group Color
Public Function isAlternate(InventoryID As Variant, ProductID As Variant) As Boolean
  If IsNumeric(InventoryID) And IsNumeric(ProductID) Then
     isAlternate = (RowNumber(InventoryID, ProductID) Mod 2 = 0)
  End If
End Function
Public Function RowNumber(InventoryID As Variant, ProductID As Variant) As Long
  If IsNumeric(InventoryID) And IsNumeric(ProductID) Then
    RowNumber = DCount("*", "qryInventoryProducts", "InventoryOverviewID = " & InventoryID & " AND ProductID <= " & ProductID)
  End If
End Function

@MajP, thanks so much! ... Unfortunately Ill have to mess with it Monday when I get back...

The reason I wanted something like this is because when entering data into a list of 20ish different products who wouldn't want each row visibly show that it had changed to another product. I just thought it would be easier for myself or someone else to move around on the form end not enter data under the incorrect product.


The products/lengths that in in the database is the complete list I just named them Product1 ''''''' for the database example, so yes there isnt very many.

 

[MajP]

The reason I wanted something like this is because when entering data into a list of 20ish different products who wouldn't want each row visibly show that it had changed to another product. I just thought it would be easier for myself or someone else to move around on the form end not enter data under the incorrect product.

Well, how about something real simple, and maybe more useful.

Private Sub lstFilter_Click()
  Dim sfrm As Form
  Set sfrm = Me.sfrm_InventoryDetails.Form
  If IsNull(Me.lstFilter) Or Me.lstFilter = 0 Then
    sfrm.FilterOn = False
    sfrm.Filter = ""
  Else
    sfrm.Filter = "ProductID = " & Me.lstFilter
    sfrm.FilterOn = True
  End If
   
End Sub

If you wanted you could make this multi select so if you want to just look at product 7, 12,16 for example. Would allow you to compare two product quantities quickly without scrolling.

 

[MajP]

Here is a version with multi select