Report contains no data

  • Thread starter Thread starter Cassie
  • Start date Start date
C

Cassie

I've created a report based on a query. You enter the last
name of the person and their information should display on
the screen. However, after adding a new person, the report
does not display anything but a blank report. Older
records display fine.

Thanks for any help I can get.
 
Cassie,

Maybe the new record has not yet been saved to the table, at the time
that you are trying to preview the report for this person. Assuming you
have a VBA procedure on an event on the data form, try adding a line of
code...
DoCmd.RunCommand acCmdSaveRecord
in front of whatever you use to open the report.
 
The record appears in the table, so I don't think that's
it. Do you have any other suggestions?

Thanks
 
Cassie,

Please give some more information about what you are doing. In
particular, how is the report being generated?
 
Basically I created a query that you need to enter the
person's last name so you can get a report just on that
person.

I recreated the report because after entering a new
client's information and running the report, it came back
empty. Once I recreated the report the last client that
hadn't showed up before appeared. However, now the next
person I entered data for does not appear when I call the
report.

When I go into the table, that person is there. I believe
the report may not be recognizing the new record. If I
create a brand new report right now, it would see the new
record, but I don't want to create and reformat a new
report everytime I have a new client to add.

Does that help?
 
Cassie,

How are you opening the report? And where is the last name criteria
getting entered?
 
I can open the report by double-clicking it or from the
switchboard. I enter the Last Name criteria in the
parameter box.
 
Cassie,

Aha! The plot thickens. So, you are using a Parameter Query. What is
the RecordSource of the report? Can you post the SQL view of the query
that the report is based on?
 
Here you go!

SELECT Customers.CompanyName, Customers.ContactFirstName,
Customers.ContactLastName, Customers.BillingAddress,
Customers.City, Customers.StateOrProvince,
Customers.PostalCode, Customers.[Follow Up], Customers.
[Appt Date], Customers.ContactTitle, Customers.Status,
Customers.[1st Contact], Customers.Operator,
Customers.PhoneNumber, Customers.[Work Phone], Customers.
[Alternate Phone], Customers.[Property Type],
Customers.Intersection, Customers.[Sub-Division],
Customers.[e-mail], Customers.Directions, Customers.
[Special Promotion], Customers.Motivation, Customers.[Lead
Source], Customers.[Referred By], Customers.[Frame
Complete], Customers.[High Ladder], Customers.Verticals,
Customers.Mini, Customers.[Cellular Shades], Customers.
[Wood/Faux Wood], Customers.[Luminette/Stardust],
Customers.[Woven Wood], Customers.[Roman Shades],
Customers.[Rollar Shade], Customers.[Silhouette/Shangra-
la], Customers.[Need Ideas], Customers.[# of Rooms],
Customers.[#of Windows], Customers.[Sales Person],
Customers.[Decision Maker], Customers.[Appt Time],
Customers.[Instlalled Date], Customers.Notes,
Customers.FaxNumber, Notes.Date, Notes.[Activity Type],
Notes.Note
FROM Customers INNER JOIN Notes ON Customers.CustomerID =
Notes.CustomerID
WHERE (((Customers.ContactLastName)=[Last Name]));
 
Cassie,

Is there a Notes record saved for the new customer at the time you are
trying to print the report?
 
Steve,

I'm a partial genius. I figured out the problem, but still
need help from you. Basically, it's only pulling up
customers who have a note in the note subform (its a query
that links the customer table to the notes table which
both share the same form). Could you help me adjust the
SQL coding so it pulls the customer even if they don't
have a note.

Thanks!!
-----Original Message-----
Here you go!

SELECT Customers.CompanyName, Customers.ContactFirstName,
Customers.ContactLastName, Customers.BillingAddress,
Customers.City, Customers.StateOrProvince,
Customers.PostalCode, Customers.[Follow Up], Customers.
[Appt Date], Customers.ContactTitle, Customers.Status,
Customers.[1st Contact], Customers.Operator,
Customers.PhoneNumber, Customers.[Work Phone], Customers.
[Alternate Phone], Customers.[Property Type],
Customers.Intersection, Customers.[Sub-Division],
Customers.[e-mail], Customers.Directions, Customers.
[Special Promotion], Customers.Motivation, Customers. [Lead
Source], Customers.[Referred By], Customers.[Frame
Complete], Customers.[High Ladder], Customers.Verticals,
Customers.Mini, Customers.[Cellular Shades], Customers.
[Wood/Faux Wood], Customers.[Luminette/Stardust],
Customers.[Woven Wood], Customers.[Roman Shades],
Customers.[Rollar Shade], Customers.[Silhouette/Shangra-
la], Customers.[Need Ideas], Customers.[# of Rooms],
Customers.[#of Windows], Customers.[Sales Person],
Customers.[Decision Maker], Customers.[Appt Time],
Customers.[Instlalled Date], Customers.Notes,
Customers.FaxNumber, Notes.Date, Notes.[Activity Type],
Notes.Note
FROM Customers INNER JOIN Notes ON Customers.CustomerID =
Notes.CustomerID
WHERE (((Customers.ContactLastName)=[Last Name]));
-----Original Message-----
Cassie,

Aha! The plot thickens. So, you are using a Parameter Query. What is
the RecordSource of the report? Can you post the SQL view of the query
that the report is based on?

--
Steve Schapel, Microsoft Access MVP


.
Click to expand...
.
Click to expand...
 
Steve,

I don't believe so. I don't know how to do that. That
sounds like it would fix the problem. Can you tell me how
to do that?

Cassie
-----Original Message-----
Cassie,

Is there a Notes record saved for the new customer at the time you are
trying to print the report?

--
Steve Schapel, Microsoft Access MVP

Here you go!

SELECT Customers.CompanyName, Customers.ContactFirstName,
Customers.ContactLastName, Customers.BillingAddress,
Customers.City, Customers.StateOrProvince,
Customers.PostalCode, Customers.[Follow Up], Customers.
[Appt Date], Customers.ContactTitle, Customers.Status,
Customers.[1st Contact], Customers.Operator,
Customers.PhoneNumber, Customers.[Work Phone], Customers.
[Alternate Phone], Customers.[Property Type],
Customers.Intersection, Customers.[Sub-Division],
Customers.[e-mail], Customers.Directions, Customers.
[Special Promotion], Customers.Motivation, Customers. [Lead
Source], Customers.[Referred By], Customers.[Frame
Complete], Customers.[High Ladder], Customers.Verticals,
Customers.Mini, Customers.[Cellular Shades], Customers.
[Wood/Faux Wood], Customers.[Luminette/Stardust],
Customers.[Woven Wood], Customers.[Roman Shades],
Customers.[Rollar Shade], Customers.[Silhouette/Shangra-
la], Customers.[Need Ideas], Customers.[# of Rooms],
Customers.[#of Windows], Customers.[Sales Person],
Customers.[Decision Maker], Customers.[Appt Time],
Customers.[Instlalled Date], Customers.Notes,
Customers.FaxNumber, Notes.Date, Notes.[Activity Type],
Notes.Note
FROM Customers INNER JOIN Notes ON Customers.CustomerID =
Notes.CustomerID
WHERE (((Customers.ContactLastName)=[Last Name]));
Click to expand...


.
Click to expand...
 
Cassie,

Congratulations! Change the clause...
FROM Customers INNER JOIN Notes ON ...
to
FROM Customers LEFT JOIN Notes ON ...
 
Steve,

How do I do this (below)?

Cassie
-----Original Message-----
Cassie,

Is there a Notes record saved for the new customer at the time you are
trying to print the report?

--
Steve Schapel, Microsoft Access MVP

Here you go!

SELECT Customers.CompanyName, Customers.ContactFirstName,
Customers.ContactLastName, Customers.BillingAddress,
Customers.City, Customers.StateOrProvince,
Customers.PostalCode, Customers.[Follow Up], Customers.
[Appt Date], Customers.ContactTitle, Customers.Status,
Customers.[1st Contact], Customers.Operator,
Customers.PhoneNumber, Customers.[Work Phone], Customers.
[Alternate Phone], Customers.[Property Type],
Customers.Intersection, Customers.[Sub-Division],
Customers.[e-mail], Customers.Directions, Customers.
[Special Promotion], Customers.Motivation, Customers. [Lead
Source], Customers.[Referred By], Customers.[Frame
Complete], Customers.[High Ladder], Customers.Verticals,
Customers.Mini, Customers.[Cellular Shades], Customers.
[Wood/Faux Wood], Customers.[Luminette/Stardust],
Customers.[Woven Wood], Customers.[Roman Shades],
Customers.[Rollar Shade], Customers.[Silhouette/Shangra-
la], Customers.[Need Ideas], Customers.[# of Rooms],
Customers.[#of Windows], Customers.[Sales Person],
Customers.[Decision Maker], Customers.[Appt Time],
Customers.[Instlalled Date], Customers.Notes,
Customers.FaxNumber, Notes.Date, Notes.[Activity Type],
Notes.Note
FROM Customers INNER JOIN Notes ON Customers.CustomerID =
Notes.CustomerID
WHERE (((Customers.ContactLastName)=[Last Name]));
Click to expand...


.
Click to expand...
 
Cassie,

Do you mean how do you enter a related Notes record? I don't really
know without more information from you. How do the Notes normally get
entered? There must be a way, because apparently most of your existing
customers have Notes data there. Unless it is automatically assigned
via a macro or code. But it certainly looks like you will either have
to ensure that all customers have at least one Notes entered, or else
change the join in your query to a Left Join, so the records will be
included in the report even if there is no Note.
 
Hi Steve,

Last question. When I have 2 records with the same last
name, only one can be viewed from my report. How do I tell
Access to pull all records with that last name?

Thanks for all of your help.

Cassie
 
Cassie,

I would have expected that, with the way you are doing it now, all
records with the same last name would already be included in your
report. If this is not happening, at the moment I can't see why.
 
Back
Top