Conditional Formatting based upon the presence of a record

[GrandMasterTuck]

[SOLVED] Conditional Formatting based upon the presence of a record

Hi folks. I hope you guys can help me solve this issue. I have a subform (called NAMES) on a main form (called MAIN) that lists names. Elsewhere on MAIN, I have a second subform (called ASSIGNED) that lists people that have been assigned.

When I double click a name on NAMES, it automatically adds them to the ASSIGNED list (who's record source is a table). What I want the NAMES form to do is conditionally format the NAMES form so that the record that's been added turns green. That way I can see who I've added and who I haven't yet added. Once I've added them all, the NAMES form is a simple list of all-green names. If there's a black name in the list, it's because I haven't yet added them to ASSIGNED.

Is there any way to do this? I've tried a DLookup function, and I've tried a Value= conditional formatting rule, and neither one are working... I'm thinking I have the syntax all wonky... any ideas? I already got the 'double click to add' thing working, now I just need the name to turn green when I've added them.

 

[JHB]

Why double click on the names when they all need to be added Assigned, why not just add them all at once with an append query?
To keep track of whom you've added, you need a checkbox fieldtype ("Yes/No") in your table, which is updated when you double click a name.

 

[GrandMasterTuck]

Because I may not be adding all the names. It depends on the day. I just need the NAMES list to turn a name green if I've added it to ASSIGNED.

And anyway, as I said, that's not the part I'm having issues with. It's the conditional format that I'm struggling with. For reasons too complicated to explain, I can't add a check box to either table and have it fill when they're added. What I need is a way for the NAMES form to look at the ASSIGNED form and color each name that it finds in both places.

 

[JHB]

.. For reasons too complicated to explain, I can't add a check box to either table and have it fill when they're added. What I need is a way for the NAMES form to look at the ASSIGNED form and color each name that it finds in both places.

For reasons too complicated to explain - the way you search doesn't exist, (it sounds very politely, doesn't it? )
You can use a query for the form's recordsource.
Select all the fields from the table and add an extra field to keep track on which name you have added.
Because if you don't have a indicator in each record/row you can't use the conditional formatting.
Else either will all records be green or no records will be green, it is the way it works in MS-Access.

 

[GrandMasterTuck]

JHB: Okay, I see where I've caused confusion. Let me try to explain another way:

MAINFORM control source: Schedule table (which has dates/shifts for each schedule created.

SUBFORM1 control source: WORKING query (which filters tblEmployees by only returning records who's assigned shift matches the SHIFT control on MAINFORM, and then removes records who's DAY OFF matches the WEEKDAY control on MAINFORM)

SUBFORM2 control source: ASSIGNMENTS query (which filters tblAssignments by only showing records who's Date and Shift values match the DATE and SHIFT fields on MAINFORM.

When I double click a record in SUBFORM1, a macro runs:

1. Create a new record in tblAssignments
2. Set NAME column of new record to match value in SUBFORM1's NAME column for the record that was clicked
3. Set DATE column of new record to match value in DATE on MAINFORM (which is showing whatever date you're setting a schedule for)
4. Set SHIFT column of new record to match value in SHIFT on MAINFORM
5. Refresh SUBFORM2 so now the newly added record shows up in the query results, and you can then set the location or notes or whatever

I want SUBFORM1's NAME to turn GREEN when I double click it and add it so it shows up in SUBFORM2's list.

I hope that helps... LOL... this isn't an easy database to work with...

 

[JHB]

I've made some small sample for you in the attached database, open MainForm and double click in the ENo control in the subform BaseTable.

 

[GrandMasterTuck]

Okay, JHB, I see how you accomplished that, and it works great, but not for the database I'm using. In your example, you have that ENo table, which I guess would represent my Employees table. In your example, the ENo table has a column called "IsClicked", and the value of that column changes from zero to one when you add it to a schedule, and conditional formatting which colors it based upon the presence of a one. But in my database, I will have a hundred different schedules, each one with that particular employee on it, and if the column switches from zero to one when I add him to the first schedule, then he'll show up as colored for all subsequent schedules.

Basically, I need the EMPLOYEES form to check to see if the listed employee record has a match in the QUERY RESULTS that feed the ASSIGNMENTS form. Each EMPLOYEE appears only once in the EMPLOYEE table, but appears over and over and over again in the ASSIGNMENTS table, and the query filters on the date and shift that are entered into a field on the MainForm. The query results are then returned to a continuous form that's set as a subform on MainForm.

Thanks for the attempt, though. I appreciate your efforts!

I have recreated a simplified version of the database below, and have added some instructions on what I want it to do, for those of you that are having trouble following my thoughts. Please open the database below, and open frmMainForm, and on there is (hopefully) a more clear look at what I'm looking to accomplish with the existing structure of the database.

EDIT: Argh, okay, I forgot to create the form for sbfmWorking. In my example, sbfmWorking is a datasheet, but in the real database, sbfmWorking is a continuous form. I know you can't apply Conditional Formatting to a datasheet... If you need me to make the correction and reup, let me know. Thanks again!

 

[JHB]

Maybe it now fits your requirement, database attached.

 

[GrandMasterTuck]

Okay... I think I see what you did there... Can you explain this in a little more detail for me? I'm still a bit new at SQL...

Private Sub Form_Current()
  Dim dbs As Database, rstClone As Recordset
   
  Set dbs = CurrentDb
  Set rst = dbs.OpenRecordset("SELECT EmployeeName FROM tblAssignments " _
  & "WHERE ID= " & Me.ID)
  Set rstClone = Me.[qryWorking subform].Form.RecordsetClone
  rstClone.MoveFirst
  If Not rst.EOF And Not rstClone.EOF Then
    Do
      rst.FindFirst "EmployeeName='" & rstClone![EmployeeName] & "'"
      rstClone.Edit
      If Not rst.NoMatch Then
        rstClone![IsAdded] = 1
      Else
        rstClone![IsAdded] = 0
      End If
      rstClone.Update
      rstClone.MoveNext
    Loop Until rstClone.EOF
  Else
    Do
      rstClone.Edit
      rstClone![IsAdded] = 0
      rstClone.Update
      rstClone.MoveNext
    Loop Until rstClone.EOF
  End If
End Sub

Is this updating the IsAdded column in the Employees table every time I switch schedule records? Again, thanks SO MUCH for getting it to work, but can you tell me what, exactly, it's doing?

And just to make sure I've covered the essentials towards adding it to my current database, I have to a)Add that SQL to the OnCurrent of MainForm, b) add the SQL below to the DoubleClick event of the EmployeeName field on the sbfmWorking:

Private Sub EmployeeName_DblClick(Cancel As Integer)
  DoCmd.SetWarnings False
  DoCmd.RunSQL "INSERT INTO tblAssignments ( ID, AssignmentDate, AssignmentShift, EmployeeName ) " _
  & "VALUES (" & Me.Parent.ID & ", #" & Me.Parent.ScheduleDate & "#, '" & Me.Parent.ScheduleShift & "', '" & Me.EmployeeName & "')"
  Me.Parent.sbfmAssigned.Requery
  Me.IsAdded = 1
  DoCmd.SetWarnings True
End Sub

and c)update qryWorking to include the IsAdded field (which I will add to the Employees table as you did)?

And how would this code differ if the sbfmWorking were a Continuous Form as opposed to a Datasheet?

Did I miss anything?

 

[JHB]

Okay... I think I see what you did there... Can you explain this in a little more detail for me? I'm still a bit new at SQL...

I've insert some comments in the code below

[COLOR=Blue]'The form's Current is triggered each time you move to a new schedule ID
[/COLOR]Private Sub Form_Current()
  Dim dbs As Database, rstClone As Recordset
   
  Set dbs = CurrentDb
[COLOR=Blue]  'Select all EmployeeName FROM tblAssignments where (schedule) ID is equal to the new schedule ID move to
[/COLOR]  Set rst = dbs.OpenRecordset("SELECT EmployeeName FROM tblAssignments " _
  & "WHERE ID= " & Me.ID)
[COLOR=Blue]  'Get/select all EmployeeName shown in "qryWorking subform"
[/COLOR]  Set rstClone = Me.[qryWorking subform].Form.RecordsetClone
[COLOR=Blue]  'Move the pointer in rstClone to the first EmployeeName
[/COLOR]  rstClone.MoveFirst
[COLOR=Blue]  'If rst or rstClone without records (EmployeeName) go to the Else part of the If statement[/COLOR]
  If Not rst.EOF And Not rstClone.EOF Then
    Do
 [COLOR=Blue]     'Find the "EmployeeName" which rstClone points to in the tblAssignments
[/COLOR]      rst.FindFirst "EmployeeName='" & rstClone![EmployeeName] & "'"
      rstClone.Edit
 [COLOR=Blue]     'If found, the set rstClone![IsAdded] = 1, else to 0
 [/COLOR]     If Not rst.NoMatch Then
        rstClone![IsAdded] = 1
      Else
        rstClone![IsAdded] = 0
      End If
[COLOR=Blue]      'Save the changes
[/COLOR]      rstClone.Update
[COLOR=Blue]      'Move to next EmployeeName
[/COLOR]      rstClone.MoveNext
 [COLOR=Blue]     'Do it until all EmployeeName shown in [qryWorking subform] has been checked
[/COLOR]    Loop Until rstClone.EOF
  Else
[COLOR=Blue]    'One of rst or rstClone is without records (EmployeeName), then set all records in rstClone ([IsAdded]) = 0[/COLOR]
    Do
      rstClone.Edit
      rstClone![IsAdded] = 0
      rstClone.Update
      rstClone.MoveNext
    Loop Until rstClone.EOF
  End If
End Sub

Is this updating the IsAdded column in the Employees table every time I switch schedule records?

Yes.

And just to make sure I've covered the essentials towards adding it to my current database, I have to a)Add that SQL to the OnCurrent of MainForm, b) add the SQL below to the DoubleClick event of the EmployeeName field on the sbfmWorking:

Private Sub EmployeeName_DblClick(Cancel As Integer)
  DoCmd.SetWarnings False
  DoCmd.RunSQL "INSERT INTO tblAssignments ( ID, AssignmentDate, AssignmentShift, EmployeeName ) " _
  & "VALUES (" & Me.Parent.ID & ", #" & Me.Parent.ScheduleDate & "#, '" & Me.Parent.ScheduleShift & "', '" & Me.EmployeeName & "')"
  Me.Parent.sbfmAssigned.Requery
  Me.IsAdded = 1
  DoCmd.SetWarnings True
End Sub

Yes exactly - but remember to change the table, field and control name to fit yours.

and c)update qryWorking to include the IsAdded field (which I will add to the Employees table as you did)?

It is necessary.

And how would this code differ if the sbfmWorking were a Continuous Form as opposed to a Datasheet?

Try it!

Did I miss anything?

I don't think so.
Oh yes - one thing, you must set up the Conditional formatting part.

 

[GrandMasterTuck]

I don't know if anyone's told you this lately, JHB, but it's people like you that make this forum so worthwhile and valuable for us newbs. Thank you so very much! You, sir, are awesome.

Take care!

 

[JHB]

Take care!

You too.