Form filter value to Report not working

[edg]

I am using a form to select a filter value to open a report. The report data source is a query listing rooms and their uses (office, storage, etc.). The usage table key is “RmUseID” and the literal is “RmUse” and is part of the query that builds the report.
The form uses a combo box based on a query using “RmUseID” and “RmUse” – the bound column is “RmUseID”.
The form button click event has the following code:



Private Sub RmUseSelect_Click()

If IsNull(RmUseID) Then
MsgBox "Please Select a Room Use"
Else
Dim filter
filter = "[RmUseID] ='" & RmUseID & "'"
DoCmd.OpenReport "mhc filtrmr", acViewPreview, filter

End If
End Sub

The query in the report has in the ‘criteria’ row under the “RmUseID” - [forms]![mainswitchbdf]![rmuseid].

When I click the form button, I get a ‘parameter needed’ pop-up – if I put a RmUseID in, the report opens showing the filtered data. If I scroll over ‘filter’ in the docmd in the form when debugging, I see the correct RmUseID is there. But it is not being passed to the report or is not being recognized by the report.

And if I open up the report alone, I get the parameter popup – entering a valid RmUseID gives me a correct filtered report. I’ve checked field names, spelling, syntax – I’m at a loss.

[June7]

Is RmUseID a number type field? If it is, remove the apostrophe delimiters. Those are for text type field parameters. Actually, would not expect the popup, would expect error message.

If you want to provide db for analysis, follow instructions at bottom of my post.

[edg]

Yes it is a number field - will try it.

[edg]

I just removed the ' apostrophes - code is now:

filter = "[rmuseid]=" & RmUseID & "" - still didn't work - getting parameter popup.

[June7]

Just hit me, you have dynamic parameter in query and VBA code building filter. Don't do both. I never use dynamic parameters in query, I use VBA.

Don't need the second ""

Try:
filter = "[rmuseid]=" & Me.RmUseID

or

DoCmd.OpenReport "mhc filtrmr", acViewPreview, "[rmuseid]=" & Me.RmUseID

[edg]

It didn't work. I am leaving work shortly but will compact db and forward tomorrow. Thanks!

[June7]

The filter is in wrong argument. Use WhereCondition argument. I have never used the FilterName argument. Missing a comma:

DoCmd.OpenReport "mhc filtrmr", acViewPreview, , filter

[edg]

MHC.accdb OK - back to work - here is my db.

[June7]

You have not incorporated the suggestion to eliminate the parameter from query (post 5). BTW, the parameter has spelling error anyway. ([orms]![mainswitchbdf]![rmuseid])

Your code does not incorporate the last suggested correction (post 7).

[edg]

Sorry about that - I packed the db after I got your first suggestions, not the later ones. I do have some work to do and will update you. I appreciate your time.

[edg]

MHC1.accdbI apologize for the errors on the last posting and my response delay - I'm responsible for a 100 bed retirement facility and we had a state inspection for the past day (unscheduled). I made the changes as you suggested but the parameter pop-up still comes up.

[June7]

The report RecordSource sql still has the dynamic parameter.

[edg]

Please help me here - dynamic parameter - are you saying drop the forms! in the criteria under rmuseid?

[June7]

Yes. Remove the dynamic criteria from the RecordSource sql. It is not necessary. The VBA code is applying this filter criteria.

[edg]

It worked! Thank you so much for your, time, patience and expertise. I did all kinds of things with Access around 2001, but haven't touched any programing since! Boy you really forget stuff if you don't use it. Again, many thanks! Not sure - does this thread need to be 'closed out' - who does it?