Access Query

[FontanaS]

I have a command button in a main menu that when clicked opens up a report. That report is based on a query.

The problem is that the particular query is used for two reports (it is queried by a different variable - open or closed). I want to write code in the command button for the report that is for only opened grants (and then i'll do it for the closed grant report) that says:

Status = "O"

In the query I have a where clause (where code = [Status])that goes by the value of code (can be O or C). right now it keeps asking the user tp tyoe it in but I don't want that to happen becasue if the user clicked the open grant report button, then I know that the status should be "O" (for open).

Any suggestions?

Right now I have:

Dim Status As String

Status = "O"
stDocName = "Rpt_Open_Grant_Reports"
DoCmd.OpenReport stDocName, acPreview

And when I run it , it still asks me for the status.

THANKS!

 

[SimonDavis]

I think there are two problems there.

Firstly you have declared and defined the variable OK, but haven't done anything with it.

Secondly, I assume the field name on the form is 'status'. Using a variable with the same name as the field might confuse.

Actually, I suspect you don't need a variable at all. Assuming the filed name is 'status', try the following:

stDocName = "Rpt_Open_Grant_Reports"
DoCmd.OpenReport stDocName, acPreview
DoCmd.ApplyFilter , "status = 'O'"

If the field is called something else, just change the code as appropriate.

 

[FontanaS]

I tried it and it states that a filter can be used only on a open event procedure or oopen macro procedure.

This is for a report.

I have tried putting the code in the open procedure of the report but it doesn't work.

 

[SimonDavis]

Sorry, I was indeed thinking of a form, not a report.

It should still work though. This code should be on the form (not the button):

Private Sub Report_Open(Cancel As Integer)
DoCmd.ApplyFilter , "code = 'O'"
End Sub

Having read your post again, I notice the field is code, not status.

If it still doesn't work, I would suspect the field name might be a problem - 'code' could mean a lot of different things to VB. It might be worth renaming the field (You'll have to go throught the table, and the query as well).

 

[SimonDavis]

Of course when I wrote 'form' I meant 'report'.

(Shuffles off mumbling)

 

[dneufarth]

You might put a list box or combo box with the values "Open" and "Closed" (default of "Open&quot next to form's menu choice for running the report. One button then does either report depending upon list box selection.

Then change criteria of "STATUS" field in query to
=Left([Forms]![YourMenuFormName]![YourListBoxName],1)


Dave