More Access 2007 help requested

[Saint Nick]

Hello again everyone...

This time around, I'm trying to get a form work with a report. This database houses the information for many volunteers and keeps track of their hours.

I have created a form that allows you to select the volunteer from a combo box. At that point, you click on "Generate Report" and it will generate a report containing the dates they worked, how many hours, and what activities were performed. Right now, I can't get the report to load properly.

The report is based off of a query that prompts you to enter their ID number. The query will then return all of that information for that particular volunteer. Then, this information is placed into a report for formatting purposes.

Right now, the connection between the query and the form isn't happening. I click on my volunteer's name and click "Generate Report", and then it prompts me to enter the volunteer ID (not desired, supposed to be pulled from the combo box which contains their ID). Even if I enter a valid ID, it will generate an empty report.

Here is the SQL for the query:

SELECT Volunteer.[Volunteer-ID], Volunteer.[Volunteer-First-Name], Volunteer.[Volunteer-Last-Name], DateWorked.[Date-Worked-Date], DateWorked.Hours, DateWorked.Activity
FROM Volunteer INNER JOIN DateWorked ON Volunteer.[Volunteer-ID]=DateWorked.[Volunteer-ID]
WHERE Volunteer.[Volunteer-ID]=[Choice];

Here is the code after clicking "Generate Report" button:

DoCmd.OpenReport "Individual Volunteer Report", acViewPreview, , "Choice" = listVolunteer.Value, acWindowNormal

In the above "DoCmd..." code, listVolunteer.Value should return their volunteer ID, and has been doing so correctly. I confirmed this by using a breakpoint during debug.

Thanks...hope you guys can help this time around!

 

[KLin]

You don't need a where clause in the sql statement when you're using the filter on property of the OpenReport statement. The query doesn't know what [choice] is referring to so that's why it's prompting you for a volunteer id. Take out the where clause, and the filter on property will give you what you need. FYI, it needs to be formatted as "Choice = " & ListVolunteer.Value

 

[Saint Nick]

KLin:

I performed your recommended steps: 1) removed the WHERE statement from the query 2) removed the WHERE statement from OpenReport 3) added "Choice = " & listVolunteer.Value to the FilterOn property.

However, now I receive this error:

Run-time error '3011':
THe Microsoft Office Access database engine could not find the object 'Choice = 16'. Make sure the object exists and that you spell its name and the path name correctly.

What object is it looking for? Choice?

 

[JACKDRUID]

done this a long time ago..

if i remember correctly, access where/filter clause don't always work. so the work around was

SELECT Volunteer.[Volunteer-ID], Volunteer.[Volunteer-First-Name], Volunteer.[Volunteer-Last-Name], DateWorked.[Date-Worked-Date], DateWorked.Hours, DateWorked.Activity
FROM Volunteer INNER JOIN DateWorked ON Volunteer.[Volunteer-ID]=DateWorked.[Volunteer-ID]
WHERE Volunteer.[Volunteer-ID]=forms[formname]!controlname([Choice]);

 

[Saint Nick]

KLin:

Apparently the OnFilter argument is a string, namely, the name of an actual query in the database. I tried this, and got the results of the ENTIRE query posted into my report, but I want it based off of ID, and tried to put in the WHERE clause, but no luck.

JACKDRUID:

I ran your code as such...

For the "Generate Report" button, I did:

Private Sub btnLoad_Click()
DoCmd.OpenReport "Individual Volunteer Report", acViewPreview, , , acWindowNormal
End Sub

And for the query....

SELECT Volunteer.[Volunteer-ID], Volunteer.[Volunteer-First-Name], Volunteer.[Volunteer-Last-Name], DateWorked.[Date-Worked-Date], DateWorked.Hours, DateWorked.Activity
FROM Volunteer INNER JOIN DateWorked ON Volunteer.[Volunteer-ID]=DateWorked.[Volunteer-ID]
WHERE Volunteer.[Volunteer-ID]=Forms![Volunteer Search Form]!listVolunteer.Value;

And it worked like a charm 🙂

Thanks to both of you guys! MUCH APPRECIATED!

~Jared

 

[JACKDRUID]

glad to be of help. cheers.

 

[KLin]

You know, I was thinking of the "where" portion of the openreport statement. That's where it needed to be setup at. JACKDRUID's solution is fine too. 🙂

 

[JACKDRUID]

Originally posted by: KLin
You know, I was thinking of the "where" portion of the openreport statement. That's where it needed to be setup at. JACKDRUID's solution is fine too. 🙂

as always you are absolutely correct 😀, "where" clause would be the better way to go. However, I have a hunch the "where" clause was bugged in MS Access MDB (ADP works though)...