Hi

Access 2002
Win XPPro

Here's an interesting problem for you chaps out there (and girls too, of course!)

I build an Invoice using a report. On it, I have placed a very simple subreport. The subreport has only one textbox in it namely, "Address_Line". The subreport is bound to a "holding" table, "Temp_Risk_Address_Table". This is a very simple table with just two fields: "autonumber" and "Risk_Address_Line".

In the Activate Event of the main form, I run an SQL query to extract the address details to which the Invoice is to be sent. I write these details into the holding table. I empty the table each time by calling "Delete * from etc" before I re-populate it. All works very nicely!

The bit I cannot figure out is this.....

When I request the main report, it dutifully clears down the holding table and adds the correct address details into it perfectly. If I fire up the subreport on it's own, it displays each address line perfectly..... BUT when viewing the Main report, the first row of the subreport appears twice! This confuses me since I have not had a beer!.

If the holding table is populated correctly and the subreport looks fine on it's own, why on earth is the first row of the subreport appearing twice on the main report.... Is it my code? Or maybe the wrong event?

Here's how I populate the holding table in the main report Activate event (it's a little laborious, I know, but I can understand it that way :) ) :-

-------------------------------------------------------------------------
rsWrite.Open "Temp_Risk_Address_Table", CurrentProject.AccessConnection, adOpenDynamic, adLockOptimistic

If Me.Risk_Contact_No_TextBox <> "" Then
rsRead.Open "Contacts", CurrentProject.AccessConnection, adOpenForwardOnly, adLockOptimistic
rsRead.Find "[Contact_No] = '" & Me.Risk_Contact_No_TextBox & "'"

If Not rsRead.EOF Then
rsWrite.AddNew
rsWrite!Risk_Address_Line = rsRead!Contact_Full_Name
rsWrite.Update
End If
rsRead.Close
End If


'Grab the values of the various address lines

If Me.Risk_Address_Unique_No_TextBox <> "" Then
rsRead.Open "Contact_Addresses", CurrentProject.AccessConnection, adOpenForwardOnly, adLockOptimistic
rsRead.Find "[Address_Unique_No] = '" & Me.Risk_Address_Unique_No_TextBox & "'"

If Not rsRead.EOF Then
If Not rsRead!Contact_Address_Line1 = "" Then
rsWrite.AddNew
rsWrite!Risk_Address_Line = rsRead!Contact_Address_Line1
rsWrite.Update
End If

If Not rsRead!Contact_Address_Line2 = "" Then
rsWrite.AddNew
rsWrite!Risk_Address_Line = rsRead!Contact_Address_Line2
rsWrite.Update
End If

If Not rsRead!Contact_Address_Line3 = "" Then
rsWrite.AddNew
rsWrite!Risk_Address_Line = rsRead!Contact_Address_Line3
rsWrite.Update
End If

If Not rsRead!Contact_Address_Line4 = "" Then
rsWrite.AddNew
rsWrite!Risk_Address_Line = rsRead!Contact_Address_Line4
rsWrite.Update
End If

If Not rsRead!Contact_Address_Line5 = "" Then
rsWrite.AddNew
rsWrite!Risk_Address_Line = rsRead!Contact_Address_Line5
rsWrite.Update
End If

If Not rsRead!Contact_Post_Code = "" Then
rsWrite.AddNew
rsWrite!Risk_Address_Line = rsRead!Contact_Post_Code
rsWrite.Update
End If

End If

rsRead.Close
End If

rsWrite.Close
-------------------------------------------------------------------

Your help is, as always, much appreciated

BUT when viewing the Main report, the first row of the subreport appears twice!

why on earth is the first row of the subreport appearing twice on the main report....

-------------------------------------------------------------------------
rsWrite.Open "Temp_Risk_Address_Table", CurrentProject.AccessConnection, adOpenDynamic, adLockOptimistic

If Me.Risk_Contact_No_TextBox <> "" Then
rsRead.Open "Contacts", CurrentProject.AccessConnection, adOpenForwardOnly, adLockOptimistic
rsRead.Find "[Contact_No] = '" & Me.Risk_Contact_No_TextBox & "'"

If Not rsRead.EOF Then
rsWrite.AddNew
rsWrite!Risk_Address_Line = rsRead!Contact_Full_Name
rsWrite.Update
End If
rsRead.Close
End If


'Grab the values of the various address lines

If Me.Risk_Address_Unique_No_TextBox <> "" Then
rsRead.Open "Contact_Addresses", CurrentProject.AccessConnection, adOpenForwardOnly, adLockOptimistic
rsRead.Find "[Address_Unique_No] = '" & Me.Risk_Address_Unique_No_TextBox & "'"

If Not rsRead.EOF Then
If Not rsRead!Contact_Address_Line1 = "" Then
rsWrite.AddNew
rsWrite!Risk_Address_Line = rsRead!Contact_Address_Line1
rsWrite.Update
End IfWhat do you mean the first row of the subreport appears twice? I thought you were populating the subreport's table with values from the main report's table. Is this what doesn't make sense?

Do you have a screenshot of the report layout to look at?

Is this all the code you have, or can you post all of it?

Hi AJE

Thanks for taking an interest in this.

No, the subreport is populated from a "holding" table that the Main report's code first empties and then re-populates.

It gets more bizarre by the minute... at first I thought I was seeing things, then I thought maybe the table wasn't being emptied properly before being re-populated.... but.....then..... attached is a screen scrape (a "preview" of the report) clearly showing the first row repeated.... I went to print it to a pdf maker so I could post it on here and hey presto! the offending row disappears.... please see atached.... Mr Gates gremlins maybe?

Yeah, I see it. Honestly, I can't think of anything it could be. I bet you can't provide this, but I would sure like to look at the code you have running in the database. :)