Removing duplication at format time

[Cosette]

Hi all. I have two addresses. When they are the same, I want the second one not to show. So, in the 'On Format' Event of the report, I wrote the following code:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If [tblParent_1.Address] = [tblParent.Address] Then
[tblParent_1.Address] = ""
Else: [tblParent_1.Address] = [tblParent_1.Address]
End If
End Sub

The error I get is: Can't assign a value to this object. I have tried to change "" to null, same problem. I then tried to define a string variable, and still ran into the same issue. Any help is very welcomed.

Thanks

David

 

[Remou]

Hi
Have you looked at Conditional formatting? It may suit.

 

[PCLewis]

What you are trying to do is change the value of a field in a record at report time. From your description that is not your intention at all and Access stops you from doing this because queries used for reports are read-only snapshots.

You will need to either improve your query to not provide the duplicates if possible or think about hiding the field (rather than changing the value).

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
  Static last_address
  If [tblParent.Address] = last_address Then
    AddressTextBox.visible=False 
  Else
    AddressTextBox.visible=True
  End if
  last_address=[tblParent.Address]
End Sub

 

[Cosette]

PC,

Thank you. I had tried yesterday to play with the visible option but Access wouldn't let me. I will try your code later this am. Is Static an access key word?

David