Reports not pulling data correctly

[yes sir]

I have a form in which I select a name from a ComboBox, then hit a Command Button to view a report with data based on the specific name.

The Report works correctly for some names, but other names all of the fields show as blank. I'm confident I have referenced and coded everything correctly.



I went and compared two records in my main table field by field, and even put all the data in the record that works into the record that doesn't, and still get the same results.

What are some common issues when this happens?

[ajetrumpet]

do the ones yielding blanks have spaces in them?? I mean, spaces in the string that's being used as criteria??

what's the code behind the button??

[yes sir]

No spaces. Before I answer your other question here is some more info.

The report that isn't working correctly has a query as its record source. When I look at the records in the query, its only displays 71 (there should be 200+), and most of these 71 comprise of duplicates of records. So in total there may only be about 15 records in the query, with some of those having 3, 4, or even 5 instances of that record in the query.

For now I have reverted this report back to an older version, but if you can think of anything of the top of your head what the issue may be then feel free to share. Until then I'm going to work this older version back to the new version, piece by piece, and see if I can find out what I screwed up.

[ajetrumpet]

bud,

for me, reading the button code can give me more info than this thread. usually that's the case.

the query is certainly useful, but without the button code, it's really irrelevant to comment on. as I see it anyway.

the query sql may help too, but post the code first.

[yes sir]

bud,

for me, reading the button code can give me more info than this thread. usually that's the case.

the query is certainly useful, but without the button code, it's really irrelevant to comment on. as I see it anyway.

the query sql may help too, but post the code first.

Let me screw around with it a little bit more and if I get stuck then I will be back. I'm going to take small steps trying to fix the older version to meet up with the new version of the report I made, then compare the SQLs of the two queries.

I think I might learn a thing or two. Hopefully.

[ajetrumpet]

No problem. oh and hey, if you truly don't care for my overly-assertive responses, please do say so. sometimes I get that feeling from the tentativeness when I respond to your posts.

Just an FYI. If it applies!

[yes sir]

Upon further review and many attempts at figuring out what is going on, I am back where I started.

Here is the scenario: I have a report called Master Report that I want to open via a combobox selection that can be made in either the Open form or the Prospect Form. However, upon picking a name and then clicking the command button I get a Enter Parameter value message and it says "Prospects," with the white box underneath.

I have a table called Prospects.

Here is the code for the Command Buttons. Both are Macros.
Command279 on Prospect Form
Action Arguments
RunCommand Save Record
OpenReport Master Report, Print Preview, , [Prospects.ID]=[Forms]![Prospect Form]![ID], Normal

MasterReport on Open form
Action Arguments
OpenReport Master Report, Print Preview, , [Prospects.ID]=[Forms]![Open]![Player], Normal

SQL for query being used as record source for Master Report report:

SELECT Prospects.*, [prospects.first name] & " " &  [prospects.last name] AS [Prospect Name], [College Coaches].*, [College  Coaches.first name] & " " & [College Coaches.last name] AS  [Coach Name], Colleges.*, Agents.*, [Agents.first name] & " " &  [Agents.last name] AS [Agent Name], Prospects.ID
FROM Agents INNER JOIN ((Colleges INNER JOIN [College Coaches] ON  Colleges.ID = [College Coaches].School) INNER JOIN Prospects ON  Colleges.ID = Prospects.College) ON Agents.ID = Prospects.Agent;

Where College Coaches, Agents, Prospects, and Colleges are each is a table in my database.

[yes sir]

Also, once the parameter value message pops up, I can push 'OK' and then it takes me to the correct report based on the selection I made in a combobox on either the Open or Prospect Forms. I've seen these messages before and I have been able to figure them out, but this one has been a pain.

[ajetrumpet]

I get a Enter Parameter value message and it says "Prospects," with the white box underneath.

my guess is that this:

OpenReport Master Report, Print Preview, , [Prospects.ID]=[Forms]![Prospect Form]![ID], Normal

is causing it. The popup is an effect of the parser cutting off:

[Prospects.ID]

because it's not enclose in quotes (I think). But, I'm posting this first suggestion because I immediately thought this was the only issue. Try:

docmd.OpenReport "Master Report", acViewPreview, , "[ID]= " & Forms![Prospect Form]!ID, acWindowNormal

see if that works first.

[yes sir]

Adam,

I tried what you recommended. Same result. However, in this specific query I have multiple fields with the name ID (just in different tables). This specific one that I'm working on should be Prospects.ID.

So whenever I try as you recommended I get the message "Run time error '3079': the specified field 'ID' could refer to more than one table listed in the FROM clause of your SQL statement."

So I tried "[Prospects.ID ...... and I tried with "[Prospects_ID......and ended up with the same result where I get the parameter value message.

[ajetrumpet]

can you upload the file??

I'm sure I can find it that way. I just feel as though this thread has gotten long enough already. And pretty soon you'll probably end up frustrated anyway. More than likely it's a very small issue, but one that's not easily recognized by a beginner like yourself.

deal?

btw, has the money started coming in for this?? is this the second phase for the project now?? I keep hopin and prayin!

[yes sir]

Adam, I reverted the report back to an older version. In the process I noticed the query for the report that was screwing up was a tad different than the older one I am going with. I'll post both SQL's and maybe you can tell what is going on. If not, then I don't want to worry about it.

This is the SQL to the query that kept setting off the Parameter Value message. This query can be accessed on the Navigation Pane on the left side.

SELECT [Prospects.First Name] & " " & [Prospects.Last Name] AS Name, Prospects.*, Agents.*, Colleges.*, [Agents.First Name] & " " & [Agents.Last Name] AS [Agent Name], [College Coaches].*
FROM Agents INNER JOIN (Colleges INNER JOIN (Prospects INNER JOIN [College Coaches] ON Prospects.[College Coach] = [College Coaches].ID) ON (Colleges.ID = Prospects.College) AND (Colleges.ID = [College Coaches].School)) ON Agents.ID = Prospects.Agent;

This SQL isn't on the Navigation Pane. And I really don't remember how I even went about putting this together. I may have been in the Report's design view and just adding existing fields from the field list.

It is a tad long.

SELECT Prospects.[Last Name], Prospects.Attachment, Prospects.[First Name], Prospects.Ht, Prospects.Wt, Prospects.College, Prospects.DoB, Prospects.Class, [First Name_Agents] & " " & [Last Name_Agents] AS [Agent Name], Agents.[First Name] AS [First Name_Agents], Agents.[Last Name] AS [Last Name_Agents], Agents.[Mobile Phone], Agents.[Business Phone], Agents.[E-mail Address], Prospects.[Primary Pos], Prospects.[2nd Pos], Prospects.Rehabilitation, Prospects.[Injury Date], Prospects.Injury, Prospects.Prognosis, [College Coaches].[Last Name] AS [Last Name_College Coaches], [College Coaches].[E-mail Address] AS [E-mail Address_College Coaches], [College Coaches].[Business Phone] AS [Business Phone_College Coaches], [College Coaches].[Mobile Phone] AS [Mobile Phone_College Coaches], [College Coaches].[First Name] AS [First Name_College Coaches], Colleges.Conference, Prospects.City, Prospects.State, Prospects.Country, Agents.Company, [College Coaches].School, Prospects.[AAU Team], Prospects.[AAU Coach], Prospects.[AAU Coach Cell], Prospects.[AAU Coach Office], Prospects.[AAU Coach Email], Prospects.[High School], Prospects.[High School Coach], Prospects.[HS Coach Cell], Prospects.[HS Coach Office], Prospects.[HS Coach Email], Prospects.[Prep School], Prospects.[Prep Coach], Prospects.[Prep Coach Cell], Prospects.[Prep Coach Office], Prospects.[Prep Coach Email], Prospects.JUCO, Prospects.[JUCO Coach], Prospects.[JUCO Coach Cell], Prospects.[JUCO Coach Office], Prospects.[JUCO Coach Email], Prospects.[International Club], Prospects.[International Coach], Prospects.[Intl Coach Mobile], Prospects.[Intl Coach Office], Prospects.[Intl Coach Email], Prospects.[Transfer College], Prospects.[Transfer Coach], Prospects.[Transfer Coach Cell], Prospects.[Transfer Coach Office Phone], Prospects.[Transfer Coach Email], Prospects.[Other Injuries], Prospects.[Position Rank], Prospects.[Overall Rank], Prospects.[Strength 1], Prospects.[Weaknesses 1], Prospects.Background, Prospects.Medical, Prospects.[Scouting Notes], Prospects.ID
FROM Agents INNER JOIN (Colleges INNER JOIN ([College Coaches] INNER JOIN Prospects ON [College Coaches].ID = Prospects.[College Coach]) ON Colleges.ID = Prospects.College) ON Agents.ID = Prospects.Agent
ORDER BY Prospects.[Last Name], Prospects.[First Name];

[ajetrumpet]

so uploading the file is not an option I take it...

[yes sir]

so uploading the file is not an option I take it...

Pretty sure it is wayyy too big. And I forgot to tell you, I did get paid. Yay!