Conditional Format Based on Previous and Current Record

[sanmisds]

I did. I soon realized that I posted my question in existing thread. Please see below:

http://www.access-programmers.co.uk/forums/showthread.php?t=148123

Thanks in advance for your help. This may be is very simple for you but this is holding me up for a while now. Thanks.

 

[johnnyc]

I know this is an old post but I am hoping someone can assist. I was able to download Wayne's Sample and it works great.

When I implement the same idea on a continuous form, it works only for the 1st record and doesnt run the function for the other records.

Is there a property or something I am missing? I am currently on Access 2016.

Thanks for any help.

 

[MarkK]

When I implement the same idea on a continuous form...

Wayne's sample is a continuous form. For more help, show your code, show your form's SQL, post your Db, show your work. For us to see what you are missing, you need to show us what you are doing.
Hope this helps,
Mark

 

[johnnyc]

Thanks Mark, Yes mine is also a continuous form.

When I create a blank db and recreate Wayne's code on a continuous form it works as I would expect.

The database we are currently developing has too many company specific information to post and may remove the item that is stopping the function from looping if I attempt to modify.

I understand this will be more difficult to assist but if I could possible get specific things your everyone's experience that would stop a function from looping through each record on a continuous form that might give me some avenues to continue my investigation.

I have checked; Open Events, and Mouse Down Events but removing these does not seem to fix the not looping issue. the Form's datasource is an embedded query that pulls in many different tables so I am wondering if that has something to do with it or maybe even the table relations.

Just trying to identify reasons why a continuous form would not loop through its conditional formatting and only run once if possible.

Thanks

 

[MarkK]

This has little to do with the form. The function call needs to be in the SQL, and then the function is called for every row of the query when the query is run, independent of the form. Then the conditional format in each row leverages the value in the field returned by the query's call to the function.
But I'm just guessing, without seeing your SQL, or your function call, or anything.
hth
Mark

 

[johnnyc]

Yes that makes sense, when I review the query behind the form all values reflect 255.

Below is what I am able to provide, let me know if this triggers an idea of why all values seem to reflect 255.

VBA (copied from above)

Public CurrentNumber As Long
Public CurrentColor As Long
Public Function GetFieldColor(TheNumber As Long) As Long
Debug.Print TheNumber
If Nz(CurrentNumber) = TheNumber Then
GetFieldColor = CurrentColor
Else
CurrentNumber = TheNumber
If Nz(CurrentColor) = 255 Then
CurrentColor = 0
Else
CurrentColor = 255
End If
GetFieldColor = CurrentColor
End If
End Function


Query: (picture attached)

Query Results: (picture attached)

thanks for your help

 

[MarkK]

I cannot see from what you have shown why that fails.
Mark