OPen a report with info supplied from a drop down

[BSJoJaMAx4]

Hi - I have a drop down on a main menu that I want to use the selected entry to open a report and only show records based on what was selected on the main menu.

The drop down is a combo box (named Combo3A) using the following

SELECT DISTINCT [Booking Master Data].[CSQ Officer] FROM [Booking Master Data] WHERE NOT [Booking Master Data].[CSQ Officer] IS NULL ORDER BY [Booking Master Data].[CSQ Officer];

It returns the single record of all CSQ Officers. I then have a report which shows all the officers records.

In after update for Combo3A i have the following

Private Sub Combo3a_AfterUpdate()DoCmd.OpenReport "Facilities bookings", acViewPreview, "[CSQ Officier]= & Nz([Combo3A], 0)"
End Sub

It works and opens the report but does not display the records for the officer (it just displays all records). Any help greatly appreciated.

[nhorton79]

May be something else an would have thought you would have received an error but...
Would have thought your criteria argument for the openreport would have been:
"[CSQ Officer] =" & Nz([Combo3A],0)

I.e keeping the " before the &

Maybe even try getting rid of the NZ function as you've already excluded NULLs in your Combo and referencing the actual report field plus me and column attributes. I.e.

"Reports![Facilities booking]![CSQ Officer] =" & Me!Combo3A.Column(0)






Sent from my iPhone using Tapatalk

[Micron]

First thing wrong is that you're trying to apply the WHERE condition in the spot meant for a defined filter. You need another comma after the view parameter. See https://msdn.microsoft.com/en-us/lib.../ff192676.aspx

Second, what we have not seen is the query behind the report. If it's not right, the selection of the combo may have no effect at all. We can only see that the combo is set to not return Nulls. To ensure the report query is correct, open it and temporarily enter a valid value in the field and see what you get. [CSQ Officer] =" & Me!Combo3A.Column(0) would be more correct for the filter, but you may require a column reference other than 0 if you have more than one column in your combo (which you haven't stated).

Third, the idea to get the WHERE value from a control is correct, but the place to get if from is the form, not the report. You can't access or set the value of a report control in the docmd.openreport method - the report is not open when you're trying to use it.

[BSJoJaMAx4]

Hi nhorton - yes sorry. I had the the " in the wrong spot.

[BSJoJaMAx4]

Hi Micron - I thought that it may be the case that I am not sending the value to the query. How do I do that? Sorry but my knowledge level for access is low.

[Micron]

I am not sending the value to the query

That's not really how the application of a WHERE condition works (you don't pass criteria to a query when you open a report). You either define a filter (give it a name and a construct) and reference it in the report opening, or you filter the report using the WHERE condition of the Docmd.OpenReport method. You would use one approach or the other, and whichever one you use must be positioned in the proper place of the report opening method. In the latter case, which I almost always use, the report is based on the query and the WHERE clause causes the report to show only the records that satisfy the restrictions of the WHERE criteria. Use the link in my last post to learn more about the difference. If you still don't understand after reviewing that, post back with your questions on the part that's stumping you. NOTE: I believe there are a few things wrong all at the same time.
- As I already stated, your application of the WHERE condition is in the wrong place. The bold red comma below illustrates this.
- Second, as nhorton79 stated, your double quote is in the wrong place.
- Third, and just noticed, you have two different spellings for Officer which you cannot have.
It is probably as simple as

DoCmd.OpenReport "Facilities bookings", acViewPreview,, "[CSQ Officer]= " & Me.Combo3A

However, if you simply copy that and it works, you won't have learned much if you don't follow my suggestion to review the link. I also agree that you probably don't need the NZ function you were using since your approach should result in a combo list with no Null values - unless you are allowing Nulls in the table field, which would not be so good.