Solved Form and SubForm - Identify outstanding item on subform - Reveal on Main Form (1 Viewer)

kengooch

Member
Local time
Today, 11:14
Joined
Feb 29, 2012
Messages
137
I have a form that is basically a logbook that provides a consecutive sequence for tracking samples sent to an outside lab.

The main table and associated "Enter Daily Send Outs" form track the actual event. (Who what when where why and how. See attached image)
The sub form embed in the "Enter Daily Send Outs" tracks the various items that are sent, ie. blocks, slides, reports, etc. there may be 2 or 3 or more items sent for each actual event in the main table.

We also track the date when the individual items in the subform that were sent out, are returned.
Note also that the Concur field automatically changes color depending upon the selection, Red for No Concurrence, Green for Concur, Yellow for Pending etc.
Note also on the "Daily Sendout Log Entry Form" that you can click t he Large Blue "Record Details" button and the "Daily Sendout Log Entry Form" pops up with all the details.

So... two things I want to achieve.
1) If an item has not been returned, the date field is empty, and if it is a slide or a block, I want all of the fields in that row to be highlighted light pink. Any other combination needs to stay white. I tried Conditional formatting but couldn't figure out how to handle two conditions Type field is either Block(s) or Slide(s) if so whole row is pink, otherwise white, but if a date is entered and it is a Block or a Slide it should then also turn back to white.

2) If there is an outstanding Slide or Block not returned on the the subform (ie. any item not returned) I need the large Blue "Record Details" button to be Red, otherwise to be Blue.

I've tried several things for both of these and no luck. I wrote VBA with an if conditional to change the color based upon the Slides and Blacks (their Unique ID is 1 and 2 responectively, thus the tSOTYpe.Value <3 in the On Current on the form.
Code:
Private Sub Form_Current()
    If tSOType.Value < 3 Then
        tSOType.BackColor = RGB(255, 200, 200)
        tSOType.ForeColor = RGB(0, 0, 0)
        tSOType.FontWeight = Bold
    Else
        tSOType.BackColor = RGB(255, 255, 255)
        tSOType.ForeColor = RGB(0, 0, 0)
        tSOType.FontWeight = Normal
    End If
End Sub
And this colors the actual tSOType field, but it does all of them in the continuous form whether they are <3 or not, and obviously, I would have to duplicate this for every field unless there is a way to tell them all. Or, if it's possible to for Conditional formatting to manage multiple conditions that would work. I did try the following Conditional Formating
Expression is = [tSOType] <3 (then chose the pink fill color)
Expression 2 = [tSODateRet] >"" (then chose white as the fill color)
In excel you can do that and set the order for things to work before or after, etc.

Thoughts and Suggestions Welcomed!!
 

Attachments

  • Sendouts Forms.png
    Sendouts Forms.png
    908.8 KB · Views: 369

pbaldy

Wino Moderator
Staff member
Local time
Today, 11:14
Joined
Aug 30, 2003
Messages
36,118
VBA won't work like you want on a form (it can on a report). Take a look at Conditional Formatting. You could use the same condition you have in your code, using Expression Is.
 

kengooch

Member
Local time
Today, 11:14
Joined
Feb 29, 2012
Messages
137
Was able to get the highlighting using Conditional Formatting and placing them in the following order
[tSODateRet]>0 highlight color white
[tSOType]<3 highlight color pink
Seem like conditional formatting processes them in sequential order.
 

kengooch

Member
Local time
Today, 11:14
Joined
Feb 29, 2012
Messages
137
Now I just need to figure out how to read each entry in the subform and if the tSOType is <3 and the ISNULL(tSODate) then highlight the Record Details Button red.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 11:14
Joined
Aug 30, 2003
Messages
36,118
Yes, the CF items are processed in sequential order. Coloring the button will be trickier, as CF can't be used on buttons. You could make a textbox look like a button and use that instead.
 

Users who are viewing this thread

Top Bottom