I'm beginning to think I'm going to have to just give up!
I have used your code, and when that didn't work I did some
experimenting
again, but eventually went back to your code. The procedure works, but
again, I get all records.
my OrgID is an autonumber field so that the user doesn't have to add
that.
Organization Name is none my primary key in case there are two
organizations
with the same name. I'm not sure that would happen, but you never
know...
What could I be doing wrong? The code below is not based on any
queries
(except the query in my choosecom combo box code (and the combo box
works
fine - the query includes Locations.community and community.county
which
is
filtered to the previous Choose County combo box), so it can't be a
problem
with any underlying queries.
Sorry for all this. I've never had so much trouble making something in
Access work before!
:
You need to concatenate the value into the string.
Probably something like this:
strWhere = "EXISTS (SELECT Community FROM Locations " & _
"WHERE ((Locations.OrgID = " & [choosecom] & ") " & _
"AND (Locations.Community = ChooseOrganization.choosecom)))"
If OrgID is a Text field (not a Number field, you will need extra
quotes:
"WHERE ((Locations.OrgID = """ & [choosecom] & """) " & _
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
Thanks Allen
The information you provided was really great, however it must be a
bit
over
my head because I worked on it on and off all day today and just
wasn't
able
to make it work.
I even did a bit more reseach as I knew now
what
to
look for. I understand the concept and how it should work, but just
can
make
it work, after trying several combinations and experiments.
The actual subquery in my OnClick properties currently looks like
this:
Dim strWhere As String
strWhere = "EXISTS (SELECT Community FROM Locations " & _
"WHERE ((Locations.OrgID = CommunityInventory.OrgID) " & _
"AND (Locations.Community = ChooseOrganization.choosecom)))"
DoCmd.OpenReport "CommunityInventory", acViewPreview, strWhere
So this is all pretty much the same as you had EXCEPT where you had
"new
york" I've put this ChooseOrganization.choosecom which is a combo
box
in
my
dialouge box where the user chooses the community from a list
instead
of
typing it (to stop typos and also so the user can see what
communities
are
available. I think this is probably where my problem is, but not
sure.
The code works in the sense that it is opening the right report, but
it
is
showing all of the organizations in the database, not just the ones
that I
selected in my dialouge box (which is still open through the
procedure
of
course)
I tried another method where I created a query with jus the
Locations
table
info in it (with, among other fields, the Community, and the only
similar
field - OrgID), with a filter in Community field pointing to the
dialouge
box. The query works. I added it to my code in various places,
including
having the query open at the start of the procedure and close after
opening
the report so the information could be pulled from the query. The
processes
all worked, but it still did not filter the report.
finally, I noticed you had two commas next to each other in the
OpenReport
command. I played around with this - adding it, adding a filter
there
(just
adding the ChooseOrganization.choosecom or the name of the query
that I
created), leaving it out and the procedure did not work at all
unless I
left
it out.
Sorry for being such a bother, but I'd appreciate anyhelp you can
give
me
in
figureing out what I did wrong!
I love access and everything you can do, but just get stuck on some
things
once in a while.
:
If I understand you correctly, you have an Organization table. One
organization can be in many locations, so you also have an
OrganziationLocation table, with a many to one relation to
Organization.
Then you have a main report bound to the Organization table, with a
subreport bound ot the OrganziationLocation table. You want to
filter
the
main report based on a location in the OrganziationLocation table.
If that's the idea, you can use a subquery in the WhereCondition of
OpenReport. The code for the Click event procedure of your button
would
look
something like this:
Dim strWhere As String
strWhere = "EXISTS (SELECT LocationID FROM OrganziationLocation " &
_
"WHERE ((OrganziationLocation.OrganizationID =
Organization.OrganizationID)
" & _
"AND (OrganziationLocation.Location = ""New York"")))"
DoCmd.OpenReport "Report1", acViewPreview, , strWhere
If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066
Okay, here is what I have going:
It is a database with information on various organizations.
The main report has "Organization Name" and "County" fields.
Because each organization may have several offices, there is a
subform/report for Location. Included is a Community field. To
show
which
area of the county the office/location is in.
Next, to make this way user friendly, I have created a dialouge
box
where
the user chooses from a list of counties which populates the
community
list.
This is working fine. The user then clicks a button to view all
the
organizations in the chosen community.
To save myself from create several extra reports (which are quite
complex
with several subforms) I have linked the button to a macro which
runs
the
Report with a filter. Other such buttons I have set up (based on
the
main
report) have worked fine, but of course, this is a bit tricker as
it's
a
subform. But surely there is a way?
My LinkMaster/Child properties seem to be correct.
I have most recently tried a query based solely on the subreport
which
works
- I brings up the organizations in a given community, but I just
can't
make
that final step to get the full form.
I am inexperiences in coding etc., so detailed instructions would
be
most
welcome. Let me know if I should include more informaton!
