Re: Report on a selected field value

Tech-Archive recommends: Fix windows errors by optimizing your registry


Marshall Barton wrote:
When you post code with a question, please use Copy/Paste so
we don't waste time chasing typos.

I DID use copy/paste. I just modified the code that was posted because
it wasn't working for me. Sorry for being new at programming in Visual
Basic/SQL, but I believe I already indicated that I was new to this in
my original post.

So please don't assume I'm trying to make this harder for you. I WANT
your help.

I'll try this below. What was posted before wasn't working, but perhaps
with the extra explanation you provide below, I can sort it out.

Thank you. I'll let you know if it works!

-Aaron



You have some errors in the posted code. First, the quotes
are wrong. If EII_Acronym is a text field, it should be:
strWhere = "[EII_Acronym] = '" & EIIList & "' "
or if EII_Acronym might possible contain an apostrophe, use:
strWhere = "[EII_Acronym] = """ & EIIList & """ "

If EII_Acronym is a mumeric type field, use:
strWhere = "[EII_Acronym] = " & EIIList

The other error I spotted is the wrong number of commas in
the OpenReport line:
DoCmd.OpenReport "EII_Report", acViewPreview, , strWhere
--
Marsh
MVP [MS Access]


Aaron wrote:
Ok, I combined the code that both of you were writing to get me
something that works...sort of:

Private Sub Command2_Click()
Dim strWhere As String
If Not IsNull(EIIList) Then
strWhere = "[EII_Acronym] = " ' & EIIList & '
End If
DoCmd.OpenReport "EII_Report", acViewPreview, strWhere
End Sub

Command2 is the button name, as best I can tell.

EIIList is the name of the combo box, as best I can tell (I think I
renamed it correctly)

EII_Report is the Report I'm trying to call.

EII_Acronym is the field I want the report based on.


I'm still getting the entire report, instead of just the part of the
report for that EII_Acronym. Why?

Note: I also made it an "On Change" procedure for the combo box itself.
Same result.


Aaron wrote:
I've been learning access by the seat-of-my-pants at work
here, and I've come across a couple of stumbling blocks. I hope you all
can help. This reports question is actually the simplest of my issues
(I think), but my other issues are with forms and queries/tables, so
I'll post elsewhere for them.

Basically, let's say I have a report on Wal-Mart sales. Each
sale in the Wal-Mart table is linked to a department, and to a location
(separate tables with data about the departments and locations are
joined). I currently have a report ordered by Location (header and
footer), department (header and footer), and sale item (with various
other data about it)--no header and footer on that one as it's part of
the tabular section of the report.

Now, each Wal-Mart location is inside one large report,
called up by the switchboard item "Location Report." There is also a
"Department Report," but I'm assuming the same answer will work for
both...

My boss wants each location to be in a separate report. I'm
hoping I can make the switchboard button cause a query (or whatever the
right function is) that makes you CHOOSE a location (or "all") from a
drop-down list or some other list. Can you tell me how to make this
happen?

Second option is to have 9 (# of locations) different
"Location Reports," but that means 8 more switchboard items and 8 more
reports in the database, and that's just for the location reports. Not
ideal. Plus, I don't even know how to do this.


Marshall Barton wrote:
Your "second option" is not an option, but a reseipe for a
maintenance disaster ;-)

The usual way to select data for a report is to create an
unbound form with a combo box where users can select the
location and a button that opens the report. The button's
Click event procedure would then specify the report's where
condition in the combo box:

Dim strWhere As String
If Not IsNull(cboLocation) Then
strWhere = "[location field name] = " + cboLocation
End If
DoCmd.OpenReport "nameofreport", acViewPreview, , strWhere

.

Quantcast