how to delete blank lines with invisible data in them?

G

Guest

I am working with a access report and I have varios blank lines in the report
that I would like to delete. I used a different macro to get rid of some
duplicate information. But it just made the information invisible. Now I
need to delete the line from the report. Is this possible?
 
S

Steve Schapel

Jessica,

I agree with Bob. In order to identify the specific details of where
the "blank lines" are coming from, it will be useful to look at the
datasheet view of the query that the report is based on.
 
G

Guest

What would the marco look like to do this?

Steve Schapel said:
Jessica,

I agree with Bob. In order to identify the specific details of where
the "blank lines" are coming from, it will be useful to look at the
datasheet view of the query that the report is based on.
Click to expand...
 
S

Steve Schapel

Jessica,

I don't see how a macro could be applicable to this situation. If you
can let us know the details of the macro you used originally, it might
help us to understand the kind of thing you are trying to do. But from
the little you have told us so far, it seems to me that you simply need
to apply appropriate criteria to the query that the report is based on,
in order to exclude the undesired records. No macro. So you need to
examine your data, and the data being returned by the query, and figure
out why you have these records that you don't want.
 
G

Guest

Ok lets see if this helps. I have a main database with contact information
of clients and each client has an ID code. With in this database I have 3
subforms of information (cars bought, mailng code and cars interested in).
Each client can have many mailing codes associated with their ID number. I
made a query for the mailing codes and then made a report with that query.
The report uses filters so that I choose with mailing codes I would like to
include in my list. Now here is where my problem started. If I put the
report in order by last name, the report gets confused and mixes the mailing
codes of different clients when I have the client and a list of all the
mailing codes with it. So I instead made the report give me all the client
information on each line that they have a mailing code. So if John Smith has
3 mailing codes, he appears 3 times on my list. Because of this I have
duplicates. I wrote a very simple marco to make the duplicates invisible on
the list because I could not get a the macro to simply delete the duplicates
to work. So this is where I am now. I need to either delete the duplicates
or delete the lines that have the information not visible.
 
S

Steve Schapel

Jessica,

Can you please go to the design view of the query that the report is
based on, select SQL from the View menu, and copy/paste the SQL of the
query back into your post here? Thanks.

Also, can you please give full details of the specific actions of the
macro that you have used to "make the duplicates invisible on the list"?
Thanks.
 
G

Guest

Here is the marco: (very simple if statments-just enough to do what was
originally needed)
Private Sub Detalle_Format(Cancel As Integer, FormatCount As Integer)

If DupeHeader = Me![ID] Then
Me![ID].Visible = False
Else
Me![ID].Visible = True
End If
If DupeHeader = Me![ID] Then
Me![Titúlo].Visible = False
Else
Me![Titúlo].Visible = True
End If
If DupeHeader = Me![ID] Then
Me![Apellido].Visible = False
Else
Me![Apellido].Visible = True
End If
If DupeHeader = Me![ID] Then
Me![Segundo Apellido].Visible = False
Else
Me![Segundo Apellido].Visible = True
End If
If DupeHeader = Me![ID] Then
Me![Nombre].Visible = False
Else
Me![Nombre].Visible = True
End If
If DupeHeader = Me![ID] Then
Me![Titúlo].Visible = False
Else
Me![Titúlo].Visible = True
End If
If DupeHeader = Me![ID] Then
Me![Dirección].Visible = False
Else
Me![Dirección].Visible = True
End If
If DupeHeader = Me![ID] Then
Me![Dirección 2].Visible = False
Else
Me![Dirección 2].Visible = True
End If
If DupeHeader = Me![ID] Then
Me![Ciudad].Visible = False
Else
Me![Ciudad].Visible = True
End If
If DupeHeader = Me![ID] Then
Me![Provincia].Visible = False
Else
Me![Provincia].Visible = True
End If
If DupeHeader = Me![ID] Then
Me![Compañía].Visible = False
Else
Me![Compañía].Visible = True
End If
If DupeHeader = Me![ID] Then
Me![Codigo Postal].Visible = False
Else
Me![Codigo Postal].Visible = True
End If
If DupeHeader = Me![ID] Then
Me![Mailing].Visible = False
Else
Me![Mailing].Visible = True
End If
DupeHeader = Me![ID]

End Sub

Unfortunately an error arrises when I try to view the SQL, so I can not
attach that to this email at this time. I will keep trying to get it.

jessica
 
S

Steve Schapel

Jessica,

Thank you. That helps to understand what you are doing. And it
clarifies one of the mysteries that I was grappling with, as this is not
a macro, it is a VBA procedure, so now we understand each other.

Seeing the SQL of the query will help me a lot to see what we are
dealing with here. This is very strange about the error - what is the
wording of the error message, and exactly when does it arise?
 
G

Guest

Here is the SQL.. the error was asking me to put in one of the cds from
microsoft.

SELECT contacts.ID, contacts.Titúlo, contacts.Nombre, contacts.Apellido,
contacts.[Segundo Apellido], contacts.Compañía, contacts.Dirección,
contacts.[Dirección 2], contacts.[Codigo Postal], contacts.Ciudad,
contacts.Provincia, Mailings.Mailing
FROM contacts INNER JOIN Mailings ON contacts.ID=Mailings.[Code Contact];

Some of the titles are in spanish.

Jessica_E said:
Here is the marco: (very simple if statments-just enough to do what was
originally needed)
Private Sub Detalle_Format(Cancel As Integer, FormatCount As Integer)

If DupeHeader = Me![ID] Then
Me![ID].Visible = False
Else
Me![ID].Visible = True
End If
If DupeHeader = Me![ID] Then
Me![Titúlo].Visible = False
Else
Me![Titúlo].Visible = True
End If
If DupeHeader = Me![ID] Then
Me![Apellido].Visible = False
Else
Me![Apellido].Visible = True
End If
If DupeHeader = Me![ID] Then
Me![Segundo Apellido].Visible = False
Else
Me![Segundo Apellido].Visible = True
End If
If DupeHeader = Me![ID] Then
Me![Nombre].Visible = False
Else
Me![Nombre].Visible = True
End If
If DupeHeader = Me![ID] Then
Me![Titúlo].Visible = False
Else
Me![Titúlo].Visible = True
End If
If DupeHeader = Me![ID] Then
Me![Dirección].Visible = False
Else
Me![Dirección].Visible = True
End If
If DupeHeader = Me![ID] Then
Me![Dirección 2].Visible = False
Else
Me![Dirección 2].Visible = True
End If
If DupeHeader = Me![ID] Then
Me![Ciudad].Visible = False
Else
Me![Ciudad].Visible = True
End If
If DupeHeader = Me![ID] Then
Me![Provincia].Visible = False
Else
Me![Provincia].Visible = True
End If
If DupeHeader = Me![ID] Then
Me![Compañía].Visible = False
Else
Me![Compañía].Visible = True
End If
If DupeHeader = Me![ID] Then
Me![Codigo Postal].Visible = False
Else
Me![Codigo Postal].Visible = True
End If
If DupeHeader = Me![ID] Then
Me![Mailing].Visible = False
Else
Me![Mailing].Visible = True
End If
DupeHeader = Me![ID]

End Sub

Unfortunately an error arrises when I try to view the SQL, so I can not
attach that to this email at this time. I will keep trying to get it.

jessica

Steve Schapel said:
Jessica,

Can you please go to the design view of the query that the report is
based on, select SQL from the View menu, and copy/paste the SQL of the
query back into your post here? Thanks.

Also, can you please give full details of the specific actions of the
macro that you have used to "make the duplicates invisible on the list"?
Thanks.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Word How to delete line in Word 3
Using a Sub to make Items Invisible 3
Visible Property in Group Header 5
Access How to prevent subReport from breaking across pages? 1
counting records 0
Hide Sub Report(s) when records are null 6
Deleting blank rows 4
Adding Blank Lines to a Report 0

Top