Link on form to a match record

[E9-Tech]

I am trying to keep track of duties that have been exchanged for other duties via a tblSwaps and a relative form which will have information about the old duty.
The main reference is DutyDateNew within the tblSwaps, which will be a match as same date in the tblDuties
The main frmDuties has an image "IcoSwap" which I would like to be set to not visible unless the duty has a history of changes, if visible I would like to be able to dblclick on the "IcoSwap" to open the frmSwap on the relevant record from the tblSwaps

I was thinking that somehow the icon should be linked to the query which extracts those duties that are related to swaps.

Thanks for the help

 

[Gasman]

Use Dcount() to see if any changes and use that with the Visible property.
Perhaps use a double click to open the form with criteria in the OpenForm command.

 

[E9-Tech]

@Gasman
I added a txt box in the frmDuties and used:

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

But not getting anywhere

 

[Gasman]

You do not have a tblSwaps?

 

[mike60smart]

@Gasman
I added a txt box in the frmDuties and used:

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

But not getting anywhere

Hi
I think it should be as follows:-

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

 

[E9-Tech]

Thanks I got the DCount expression to work with the suggestion.

I have the dblClick event on the icon working correclty and opening the frmSwaps to the relevant record.

I am trying to make the icon visible when the DCount finds a match and returns the value 1.

I used the after update event with the below code within the DCount field

Private Sub Text133_AfterUpdate()
    If Me.Text133 = "0" Then
    Me.IcoSwap.Visible = False
Else
    Me.IcoSwap.Visible = True
End Sub

But not luck!!!

How do I make the IcoSwap visible only when the DCount box shows "0" ?

 

[Gasman]

For starters, give your controls meaningful names even if they are not visible. Text133 is not going to mean much to anyone a few months down the line.
A result from a Dcount is always going to be numeric, not a string! Understand the difference.
I would just use

Me.IcoSwap.Visible = Me.Dcount > 0

I would probably put it in the Current event so it changes on each record. The DCount() should likely be there as well.
You also need to make sure you are interrogating the correct table for that result.

 

[E9-Tech]

For starters, give your controls meaningful names even if they are not visible. Text133 is not going to mean much to anyone a few months down the line.
A result from a Dcount is always going to be numeric, not a string! Understand the difference.
I would just use

Me.IcoSwap.Visible = Me.Dcount > 0

I would probably put it in the Current event so it changes on each record. The DCount() should likely be there as well.
You also need to make sure you are interrogating the correct table for that result.

Thanks for the help this has taken care of the issue!!

Yes I do give proper names, is just I am working on a dummy DB once I am satisfied I just apply all to the proper DB, I have learned from my own errors and this was one!

 

[E9-Tech]

@Gasman
How would In go to have the DCount in the Current event as you suggest?

 

[Gasman]

You just place that Dcount in the current event. Hide Text133

 

[Pat Hartman]

Yes I do give proper names, is just I am working on a dummy DB once I am satisfied I just apply all to the proper DB, I have learned from my own errors and this was one!

You learn better when you don't go out of your way to introduce confusion. No task is too trivial to do correctly and the sooner you learn the discipline, the easier it becomes.

Your schema is incorrect. There are no relationships and tblSwaps isn't even included. the same field needs to be duplicated. Your column names are abbreviations that don't bring any real world words to mind.

We have no idea what your business rules are. You should probably start by creating some. For example - you seem to be hanging your hat on date for some reason. Does that mean that duties are exchanged only for a single day. They never cross to a different date? Or, you can't swap two duties for one?

I have no idea what "run" means in this context.