Icon Visible if (2 Viewers)

[mtagliaferri]

I have a form with a text box SwapCheck if the value of this text box is grater than 0 I need an image IcoSwap to be visibile, I am trying to place the code in the control source of IcoSwap put struggling with the code to be placed.

I have another icon which becomes visibile with the below code in the Form Load, Is it possible to add a further If statement when SwapCheck value is greater than 0 to the code or is it better in the control source of the icon?

Private Sub Form_Load()
    If IsNull(Me.DutyNotes) Then
       Me.IcoNotes.Visible = False
  
    Else
       Me.IcoNotes.Visible = True
      
    End If
End Sub

 

[CJ_London]

Perhaps in the dutynotes after update event put

Me.IcoNotes.Visible =Dutynotes > 0

 

[mtagliaferri]

Perhaps in the dutynotes after update event put

Me.IcoNotes.Visible =Dutynotes > 0

They are two different icons and two different events
The icon notes works fine, I need to add the IconSwap to the Form Load with the criteria SwapCheck being greater than 0.

Hope this makes sense

 

[theDBguy]

They are two different icons and two different events
The icon notes works fine, I need to add the IconSwap to the Form Load with the criteria SwapCheck being greater than 0.

Hope this makes sense

The Load event only fires once. Are you sure that's the event you want? Just curious...

 

[mtagliaferri]

The Load event only fires once. Are you sure that's the event you want? Just curious...

Yes ad when the form is opened if the SwapCheck has a value greater than 0 then is when the icon becomes visible, there won’t be any changes in the opened form that may change the criteria

 

[theDBguy]

Yes ad when the form is opened if the SwapCheck has a value greater than 0 then is when the icon becomes visible, there won’t be any changes in the opened form that may change the criteria

In that case, have you tried?

Private Sub Form_Load()
    If IsNull(Me.DutyNotes) Then
       Me.IcoNotes.Visible = False
  
    Else
       Me.IcoNotes.Visible = True
      
    End If

    If Me.SwapCheck > 0 Then
        Me.OtherIconName.Visible = True
    Else
        Me.OtherIconName.Visible = False
    End If

End Sub

 

[Pat Hartman]

@theDBguy made a good point. Decisions that rely on data values do not belong in the on Load event. They belong in the Current event. Then, if you change how the form works, you don't need to change the code because it is already in the logically correct place. Strive to make your code always correct rather than accidentally correct.

 

[mtagliaferri]

In that case, have you tried?

Private Sub Form_Load()
    If IsNull(Me.DutyNotes) Then
       Me.IcoNotes.Visible = False
 
    Else
       Me.IcoNotes.Visible = True
     
    End If

    If Me.SwapCheck > 0 Then
        Me.OtherIconName.Visible = True
    Else
        Me.OtherIconName.Visible = False
    End If

End Sub

Thanks this seems to work.

However I have now an error when I am adding a new record.

The SwapCheck text box has the below code within the Control Source

=DCount("[IDDuty]","[tblSwaps]","[DutyDateNew] = #" & Format([DutyDate].[Value],"mm/dd/yyyy") & "#")

When the form is open as a new record there is no DutyDate entered yet therefore the above code returns a #Error as the code looks for numeric values.

As a solution I have placed a default value Now() on the DutyDate to stop the error occurring and it works. However having the today date proposed in the form hits my OCD badly ....

Is there a way in

   If Me.SwapCheck > 0 Then

to include also the #Error?

More importantly can you give more insight on difference between on Load and on Current?
Thanks

 

[theDBguy]

More importantly can you give more insight on difference between on Load and on Current?

The Load event fires once when you open a form after the record source is loaded. The Current event fires each time you navigate to a different record.

However I have now an error when I am adding a new record.

How exactly are you getting to the point of adding a new record? Do you have two separate buttons for opening the form: one for editing and one for adding a new record?

 

[Gasman]

You cannot expect to get a value from an empty control?
You could use the NZ() function for some date you prefer.
You could also test for New Record and do it all in code.

 

[LarryE]

Thanks this seems to work.

However I have now an error when I am adding a new record.

The SwapCheck text box has the below code within the Control Source

=DCount("[IDDuty]","[tblSwaps]","[DutyDateNew] = #" & Format([DutyDate].[Value],"mm/dd/yyyy") & "#")

When the form is open as a new record there is no DutyDate entered yet therefore the above code returns a #Error as the code looks for numeric values.

As a solution I have placed a default value Now() on the DutyDate to stop the error occurring and it works. However having the today date proposed in the form hits my OCD badly ....

Is there a way in

   If Me.SwapCheck > 0 Then

to include also the #Error?

More importantly can you give more insight on difference between on Load and on Current?
Thanks

You can use if-then-else in a control text box if you wish:
=IIf([SwapCheck]>0,DCount("[IDDuty]","[tblSwaps]","[DutyDateNew] = #" & Format([DutyDate].[Value],"mm/dd/yyyy") & "#")
,0)

 

[mtagliaferri]

Thanks @theDBguy @Pat Hartman @Gasman @LarryE

Thanks for the explanation and it makes sense to use on Current
The code for adding a record is:

Private Sub CmdAdd_Click()
    DoCmd.OpenForm "frmDuties", acNormal, "", "", acAdd
    Forms!frmDuties!CmdEdit.Visible = False
    Forms!frmDuties!DutyDate.SetFocus
    Forms!frmDuties!sfrmRuns.Form.AllowEdits = True
    Forms!frmDuties!sfrmRuns.Form.AllowAdditions = True
    Forms!frmDuties!sfrmRuns!sfrmNotes.Form.AllowEdits = True
    Forms!frmDuties!sfrmRuns!sfrmNotes.Form.AllowAdditions = True
End Sub

And to open the form it is via a date field with the code

Private Sub DutyDate_DblClick(Cancel As Integer)
    On Error GoTo DutyDate_DblClick_Err
        DoCmd.OpenForm "frmDuties", acNormal, "", "[DutyDate]=#" & Format(DutyDate, "mm\/dd\/yyyy") & "#", , acDialog
DutyDate_DblClick_Exit:
    Exit Sub

DutyDate_DblClick_Err:
    MsgBox Error$
    Resume DutyDate_DblClick_Exit
End Sub

@LarryE I tried your solution

and I get an error

 

[Gasman]

You do not have &amp
That is html for &

 

[mtagliaferri]

You do not have &amp
That is html for &

I corrected the code

=IIf([SwapCheck]>0,DCount("[IDDuty]","[tblSwaps]","[DutyDateNew] = #" & Format([DutyDate].[Value],"mm/dd/yyyy") & "#"),0)

and getting the error #Type!

 

[Gasman]

Break it apart and test each in the immediate window, as looks OK to me.
Again dutydate must exist.

 

[LarryE]

I corrected the code

=IIf([SwapCheck]>0,DCount("[IDDuty]","[tblSwaps]","[DutyDateNew] = #" & Format([DutyDate].[Value],"mm/dd/yyyy") & "#"),0)

and getting the error #Type!

The Table criteria should not have brackets:
It should be "tblSwaps" because there is no table [tblSwaps].

 

[Gasman]

The Table criteria should not have brackets:
It should be "tblSwaps" because there is no table [tblSwaps].

Does that actually matter?
I never use them as I do not have spaces in my object names.
I would also never use the .Value property.

 

[LarryE]

Does that actually matter?
I never use them as I do not have spaces in my object names.
I would also never use the .Value property.

In the built-in functions yes, it matters. The functions use whatever is between the quotation marks as the table name. In this case, it's tblSwaps that ACCESS looks for, not [tblSwaps]. Also just FYI, ACCESS can use table names with spaces. He could have named the table tbl Swaps and ACCESS would accept that without brackets. But you can't do that with field names.

 

[Gasman]

Works for me?

? dcount("[DailyID]","[tblDaily]","[DailyDate]=#" & "01/01/2025" & "#")
 8


? dcount("[DailyID]","[tblDaily]","[DailyDate]=#" & format(Date(),"mm/dd/yyyy") & "#")
 4

 

[LarryE]

Works for me?

? dcount("[DailyID]","[tblDaily]","[DailyDate]=#" & "01/01/2025" & "#")
 8


? dcount("[DailyID]","[tblDaily]","[DailyDate]=#" & format(Date(),"mm/dd/yyyy") & "#")
 4

Hmmm...I'll try it I guess. I have never used brackets in the table criteria so...