Form refresh 1

[nq]

I have a main form with a command button.
This button opens a small pop-up form.
I make changes to data on the pop-up form and then close it to return to the main form.
I then check to see if a particular value has changed from the pop-up form. If it has, using conditional formatting, I change the colour of an unbound control to green, otherwise the control's fore and background colours remain the same as the form - ie invisible.
The result is that as a user selects records, if a value has been set via the pop-up form, a "little green light" will appear on the main form.
This all works perfectly with one minor glitch. It would be nice if, the "green light" would come on when the user closed the pop-up form. Unfornunately this only happens when moving to another record and then returning.

I have tried the following Form events to trigger a check of the data and a DoCmd.Requery but nothing seems to work:

Active
GotFocus
Current

Tried code is shown below:

Private Sub Form_Activate()
Dim varCES As Variant
varCES = DLookup("Completed", "tblCESLog", "[ID]= '" & [ID] & "'")
If varCES = -1 Then
Me!CESComplete = "Completed"
Else
Me!CESComplete = Null
End If
DoCmd.Requery
End Sub

The control "CESComplete" has the conditional formatting attached to it.

None seem to work. The Form "Load" and "Current" events work when moving from one record to another but none of them seem to work when closing the pop-up form to give focus back to the main form.

Nigel.

 

[TheAceMan1]

How are ya nq . . .

Transfer your code to the [blue]Close[/blue] event of the popup, then . . .

[blue][purple]Instead of:[/purple]
   DoCmd.Requery
[purple]Use:[/purple]
   Forms!FormName.ReCalc[/blue]

If this doesn't work you'll have to give more detail on what the popup is doing . . .

See Ya! . . . . . .

 

[nq]

Hi AceMan.

Sorry, didn't work. Here's some of the code from the pop-up and an explanation:
Two bound fields:
"Completed" is an Option box - tick or no tick
"LDate" is a date to be completed if "Completed" is ticked

After an update to "Completed", I saved the record (for the hell of it) and then issued the ReCalc (as well as on Close)
I checked the table and the change had happened before the pop-up is closed. However frmStudents didn't update.

I moved to another record and then back again and the "green light" comes and goes depending on the state of "Completed".


Private Sub Close_Click()
Forms!frmStudents.Recalc
DoCmd.Close


Private Sub Completed_AfterUpdate()
If Me.Completed = -1 Then
Me.LDate.SetFocus
Else
Me.LDate = Null
End If
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Forms!frmStudents.Recalc

 

[TheAceMan1]

nq . . .

Sorry to get back so late.

Need to fill in some blanks . . .
[ol][li]Whats the [blue]expression[/blue] your using in your conditional formatting?[/li]
[li]Are the MainForm & PopUp based on the [blue]same table?[/blue][/li]
[li]Is the MainForm a [blue]Continuous View form?[/blue][/li]
[li]Is [blue]CESComplete[/blue] a [blue]bound/unbound[/blue] control?[/li][/ol]

See Ya! . . . . . .

 

[nq]

AceMan.

The conditional formatting is not done by code (I suppose I should learnt how to do it) It is done via the "Format | Conditional Formatting" menu thingy. I have set this to normally display the control foreground and background color to the same color as the form background - ie the control is normally invisible. The condition is - if the field value = "Completed", then the background goes green and the foreground is white. ie displays a green box showing "Completed" in white text.

No they are not based on the same table. The main form is based on tblStudents and the pop-up is based on tblCESLog. In tblStudents the primary key is "ID" There is a one-to-one relationship with "ID" in the tblCESLog. This was done in case future requirements require a one-to-many relationship.

No the main form is Single view - contains a subform.

The completed control is bound to the tblCESLog table.


Nigel.

 

[nq]

Further to the above, I have implemented the conditional formatting in code and it works better than the Access internal method which left a border around the control when changing to the next record (?).

varCES = DLookup("Completed", "tblCESLog", "[ID]= '" & [ID] & "'")
If varCES = -1 Then
Me!CESComplete.ForeColor = 16777215
Me!CESComplete.BackColor = 4259584
Me!CESComplete = "Completed"
Else
Me!CESComplete.ForeColor = 16764057
Me!CESComplete.BackColor = 16764057
Me!CESComplete = Null
End If

However, Forms!frmStudents.Recalc still does not work on the pop-up Close event.

 

[TheAceMan1]

nq . . .

Sorry but that is not conditional formatting. If you tried this on a [blue]Continuous Form[/blue] you'd find t[blue]he entire field would change to the set colors![/blue] Conditional Formatting allows you to hilite/enable/disable controls of [blue]individual records.[/blue] On a continuous form for example, each record could be hilited a different color! . . . On a single view form the impact of conditional formatting is simply [blue]not so apparent.[/blue]

What you've posted will suffice for single view. [purple]Putting your code in a common routine[/purple] which can be called by the [blue]OnCurrent[/blue] event of the mainform & the [blue]popup[/blue] is whats needed. So to clean this up:
[ol][li]Remove all current code manipulation for conditional formatting (including the conditional formats themselves).[/li]
[li]In the [blue]code module[/blue] of the [blue]mainform[/blue], copy/paste the following routine:

[blue]Public Sub SetHiLite()
   Dim varCes As Boolean, Criteria As String
   
   Criteria = "[ID]= '" & [ID] & "'"
   varCes = Nz(DLookup("[Completed]", "tblCESLog", Criteria), 0)
   
   With Me!CESComplete
      If varCes = -1 Then
          .ForeColor = 16777215
          .BackColor = 4259584
          .Value = "Completed"
      Else
          .ForeColor = 16764057
          .BackColor = 16764057
          .Value = Null
      End If
   End With

End Sub[/blue]

[/li]
[li]In the [blue]OnCurrent[/blue] event of the [blue]mainform[/blue]:

[blue] Call [purple][b]SetHiLite[/b][/purple][/blue]

[/li]
[li]Finally for the PopUp:

[blue]Private Sub Close_Click()
   
   If Me.Dirty Then [green]'record in edit mode![/green]
      DoCmd.RunCommand acCmdSaveRecord
      DoEvents
      Forms!frmStudents.[purple][b]SetHiLite[/b][/purple]
   End If
   
   DoCmd.Close

End Sub
   
Private Sub Completed_AfterUpdate()

   If Me!Completed Then
       Me!LDate.SetFocus
   Else
       Me!LDate = Null
   End If

End Sub[/blue]

[/li][/ol]
[blue]Thats it! . . .[/blue]

See Ya! . . . . . .

 

[nq]

AceMan.

Excellent, it works. Thank you. The best part is - I have learnt something.
I understand now why my code is not conditional formatting.

Now that you have mentioned it, I have a need to do a similar thing on the subform of the same mainform. On each record of the subform (continuous form), a control button will open a related pop-up form. Data is entered and the user would like to see a red or green "light" on all records displayed in the subform. I did try this earlier and as you said, ALL controls changed color.

Could you give me some hints how to code conditional formatting on a continuous form so that I will be able to do this for myself in future.