Report keeps requesting parameter

[soulice]

Have a report based on a query. This is how I am opeing the report from a buttonclick:
DoCmd.OpenReport "IndividualDonation", acViewNormal, , "[IndividualDonation].[ID]= " & Me.ID

I have a msgbox prior to this to check Me.ID and it is correct.

The report prompts for IndividualDonation.ID every time. If I provide it manually it opens with the correct result. Why is it not taking the profived where clause in the DoCmd?

 

[Adam Caramon]

Instead of using a message box, have you tried having a field on your form serve as the parameter?

DoCmd.OpenReport "IndividualDonation", acViewNormal, , [IndividualDonation].[ID]= me.txtParameter

Where txtParameter is an unbound textbox on your form where you're supplying the parameter.

 

[boblarson]

Try changing to this:

DoCmd.OpenReport "IndividualDonation", acViewNormal, , "[ID]= " & Me.ID


And if ID is not numeric but is text in the table:

DoCmd.OpenReport "IndividualDonation", acViewNormal, , "[ID]= " & Chr(34) & Me.ID & Chr(34)

 

[boblarson]

The main thing is that you don't need, nor do you want, the [IndividualDonation]. part there. The only time you would need to use any kind of table designator is if there are more than one table in the report's record source which has the exact same field name.

 

[soulice]

The main thing is that you don't need, nor do you want, the [IndividualDonation]. part there. The only time you would need to use any kind of table designator is if there are more than one table in the report's record source which has the exact same field name.

It is a number field and the query behind the report is a join and has numerous tables with ID (the default autonumber field) in it, all refereneced by TableName.ID.

If I manually fill in the prompt with the id I saw in the MsgBox, it works. Not sure why I am getting prompted.

 

[boblarson]

It is a number field and the query behind the report is a join and has numerous tables with ID (the default autonumber field) in it, all refereneced by TableName.ID.

A good reason to always use a unique name per table (don't let Access defaults trip you up). So for Employees I would have EmployeeID and for Departments, DepartmentID. Not ID for both of those.

If I manually fill in the prompt with the id I saw in the MsgBox, it works. Not sure why I am getting prompted.

It may be something completely unrelated too. You might have that being used somewhere in the report but it shouldn't be there. Hard to say without seeing the actual database (although after telling me that all of your ID fields are named ID, I'm a bit scared to take a look).

 

[soulice]

Ok, cleaned up the ID fields to be TableNameID (as they should have been in the first place and I know better (slaps hand))
Anyway, still get prompted for the parameter...when launching report from DoCmd. If I open the query or report, it runs fine (showing all records). I pass a where clause to show a single record.

 

[boblarson]

Ok, cleaned up the ID fields to be TableNameID (as they should have been in the first place and I know better (slaps hand))
Anyway, still get prompted for the parameter...when launching report from DoCmd. If I open the query or report, it runs fine (showing all records). I pass a where clause to show a single record.

Any chance you can post a copy of your database (with bogus data)? If so, make sure to run Compact and Repair first and then zip it up (you can right click on the file and select SEND TO > COMPRESSED FOLDER).

 

[soulice]

here you go. Launch the Individual form and then try to add a new donation..then click the report button (square, no text) after adding data (dont need to click save and close) and see what happens.

 

[boblarson]

here you go. Launch the Individual form and then try to add a new donation..then click the report button (square, no text) after adding data (dont need to click save and close) and see what happens.

Will do. I'll be leaving work shortly and should get home within an hour or so. I'll try to take a look as soon as possible when I get home.

 

[boblarson]

Okay, simple fix. Change your code for opening the report to this:

Private Sub cmdOpenIndividualReport_Click()
    MsgBox Me.IndividualDonationID
   [B][COLOR="Red"]If Me.Dirty = True Then Me.Dirty = False[/COLOR][/B]
    DoCmd.OpenReport "IndividualDonation", acViewPreview, , "[IndividualDonation[B][COLOR="red"]_ID[/COLOR][/B]]= " & Me.IndividualDonationID
End Sub

So, the first part (Me.Dirty) - this saves the record which needs to be saved before it will show up on the report that is being opened.

The second part - your query underlying the report actually renames the field from IndividualDonationID to IndividualDonation_ID (not sure why you did that).

But if you replace your code for that click event with what I posted it will work.

 

[soulice]

Thanks a bunch! As for the odd named field in teh query, access did that as I used the query wizard to create it. Will keep you posted on the results.

 

[soulice]

worked like a charm.