Stupid DboCmd.OpenReport question from stupid programmer 1

[jacktripper]

I know I have successfully done this before, and for the life of me I have no clue what I'm doing wrong. Too much time in the office, maybe.

Have report which uses a separate query for pulling data. The query uses an ID parameter which I am trying to pass from my form via VBA code. Yet - the report ALWAYS asks me for the ID when, obviously, I don't want it to...


VBA code in Form:
Dim ThisID
ThisID = Me.ID
DoCmd.OpenReport "Retail Invoice", acViewPreview, , "ID=" & ThisID


Retail Invoice report has qryRetailInvoice as its data. The ID variable above is being populated correclty.

qryRetailInvoice looks like:
PARAMETERS ID Long;
SELECT [Print Shop Allocation].*, [Print Shop Retail Details].*, [Print Shop Allocation].ID
FROM [Print Shop Allocation] INNER JOIN [Print Shop Retail Details] ON [Print Shop Allocation].ID = [Print Shop Retail Details].AllocationID
WHERE ((([Print Shop Allocation].ID)=[ID]));


And yet, every time I run it... I am always prompted for the ID value. Ideas?

 

[dhookom]

I expect your record source ends up with too many fields named [ID]. Try change the record source query to:

SELECT [Print Shop Allocation].*, [Print Shop Retail Details].*
FROM [Print Shop Allocation] INNER JOIN [Print Shop Retail Details] ON [Print Shop Allocation].ID = [Print Shop Retail Details].AllocationID;

Duane
Hook'D on Access
MS Access MVP

 

[MazeWorX]

remember there arnt stupid questions only stupid answers.

HTH << MaZeWorX >> Remember amateurs built the ark - professionals built the Titanic [flush]

 

[jacktripper]

Thanks for the reply.

I changed the query, but still have the same problem with the prompt popping up. I also changed it to use AllocationID as the parameter, but that didn't work either.

I know I've done this before, and thats the frustrating part - is that I can't find where. Ugh.

 

[jacktripper]

Just as a test, I created a Macro to do the same thing - and I am still getting the parameter popup... So, definitely something funky with either my report or query...

 

[dhookom]

Can you confirm there is a numeric field/column in your reprot's record source named [ID]?

Can you confirm there is a field/control in your form named ID?

Duane
Hook'D on Access
MS Access MVP

 

[Maqu1na]

Hello everyone. I am having exactly the same problem as jacktripper, working on Access '97. Should i start a new thread?

 

[Maqu1na]

I just solved it, or at least, it works for me now.

My report uses a query as source. So i finished it with the statement:

WHERE (((Base.Id)=[forms]![MyForm]![Id]));

No more prompting for Id's. Hope it works for you too, jack.

 

[jacktripper]

I have yet had the chance to try it... but will do.

Thanks for the post.

 

[jacktripper]

Yup - that did it. Thanks!