showing specific names with certain dogs in query/form

I am trying to setup a form which shows the clients info & only thier dogs
info. My goal is to be able to us a drop down combo box that will bring up
each their dog's info.

I am trying to set up a query for this (to insert into a form later) I have
the relationships correct (one to many one cleint to many dogs)

Well here's the problem. I can get a query that shows the the owners & dogs
that go with the owners

jane doe sparky
jane doe sam
beth miller jake
etc...

Well when I put this in form & switch to combo box ALL the dogs are
displayed whether they belong to jane doe or not.

I am thinking I need a query that shows one owner with multiple dogs
underneath it (with a + sign)

This way when I put it into the form it will only show the dogs that belong
to Jane doe. Any ideas

"HLarkin" wrote in message
...
I am trying to setup a form which shows the clients info & only thier dogs
info. My goal is to be able to us a drop down combo box that will bring
up
each their dog's info.

I am trying to set up a query for this (to insert into a form later) I
have
the relationships correct (one to many one cleint to many dogs)

Well here's the problem. I can get a query that shows the the owners &
dogs
that go with the owners

jane doe sparky
jane doe sam
beth miller jake
etc...

Well when I put this in form & switch to combo box ALL the dogs are
displayed whether they belong to jane doe or not.

I am thinking I need a query that shows one owner with multiple dogs
underneath it (with a + sign)

This way when I put it into the form it will only show the dogs that
belong
to Jane doe. Any ideas

SELECT FirstName, LastName, DogName FROM Client INNER JOIN ClientDog WHERE
Client.ClientID = Forms![TheForm].ClientID

HTH;

Amy

Can you elaborate a little more on the "WHERE Client.ClientID =
Forms![TheForm].ClientID"

On my Query I have the following:

SELECT tblDOG.DogName, tblOWNER.OwnerFirst, tblOWNER.OwnerLast
FROM tblOWNER INNER JOIN tblDOG ON tblOWNER.OwnerID = tblDOG.OwnerID;

There's an tblOWNER & tblDOG table.

I am still learning SQL

Thanks

"Amy Blankenship" wrote:



SELECT FirstName, LastName, DogName FROM Client INNER JOIN ClientDog WHERE
Client.ClientID = Forms![TheForm].ClientID

HTH;

Amy

"HLarkin" wrote in message
...
Can you elaborate a little more on the "WHERE Client.ClientID =
Forms![TheForm].ClientID"

On my Query I have the following:

SELECT tblDOG.DogName, tblOWNER.OwnerFirst, tblOWNER.OwnerLast
FROM tblOWNER INNER JOIN tblDOG ON tblOWNER.OwnerID = tblDOG.OwnerID;

There's an tblOWNER & tblDOG table.

"I am trying to setup a form which shows the clients info & only their dogs
info."

Your form = Forms![TheForm] so if you named your form TheForm, that should
work as is. Otherwise, you'd need to change TheForm to whatever you named
your form.

So, in a Combobox on TheForm, you'd click on the data tab and go into the
row source query.

Use the Query builder to select tblDog and tblOwner and if the relationship
is not showing drag OwnerId from tblOwner to tblDog. Drag OwnerFirst,
OwnerLast, and OwnerID from Owner. Drag DogName from tblDog. I suspect
that the goal of the combobox is to select DogID, so you should actually
drag that to the front of the grid so it appears before OwnerFirst.

There should be a check box below all the fields. Uncheck the one below
OwnerID, and in the Where Row beneath the box you just unchecked, put =
Forms![Your Form Name].ClientID. Replace "Your Form Name" with the actual
name you used for the form. This all assumes your form is a top level form
and is not a subform. Close the query builder and save the change.

Now, in the combobox's Format properties, set number of columns to 4. Set
Column widths to 0;1;1;1.

Question: Why do you want to show the owner's details in the Combobox, since
presumably the form already has the owner selected? Wouldn't it make more
sense to show it there, and just show the list of applicable dogs' names?

-Amy

I was thinking about thatlast night. You know a list with the owner's dogs
would be better.

My goal is to have the top part of the form have the owner's info on it.
Then have either with a sub form (but would rather have tabs) have the dog's
info

tab1: dog's info
tab2: dog's medical
etc.

I started thinking I needed a combo box that shows the dogs names & would
bring up the corisponding info in the tabs, but a list box would be better.
This way I can click on sam & see sam's info below it, then click on rover
and see rover's info. Do you see what I mean.

I am trying to figure out how to set up a criteria that will show the dog's
that just belong to the corrisponding owner. Is there a criteria to do this?

(I wanted to have a tab for each dog, but soon found out that that might be
too much of a jump.)

Thanks for all your help.



Question: Why do you want to show the owner's details in the Combobox, since
presumably the form already has the owner selected? Wouldn't it make more
sense to show it there, and just show the list of applicable dogs' names?

-Amy

On Tue, 1 May 2007 12:57:02 -0700, HLarkin
wrote:

I was thinking about thatlast night. You know a list with the owner's dogs
would be better.

My goal is to have the top part of the form have the owner's info on it.
Then have either with a sub form (but would rather have tabs) have the dog's
info

You can of course put a Subform on a Tab Control.

tab1: dog's info
tab2: dog's medical
etc.

I started thinking I needed a combo box that shows the dogs names & would
bring up the corisponding info in the tabs, but a list box would be better.
This way I can click on sam & see sam's info below it, then click on rover
and see rover's info. Do you see what I mean.

Use the Listbox as the "Master Link Field" for your Subform. You'll have to
type in the name of the listbox control, the wizard won't offer that option,
but it will work.

I am trying to figure out how to set up a criteria that will show the dog's
that just belong to the corrisponding owner. Is there a criteria to do this?

You certainly need an OwnerID field as the primary key of the Owners table and
as a foreign key for the Dogs table.

(I wanted to have a tab for each dog, but soon found out that that might be
too much of a jump.)

Don't let *FORMS* drive your Table structure. Tables first - forms later!

John W. Vinson [MVP]

"HLarkin" wrote in message
...
I was thinking about thatlast night. You know a list with the owner's dogs
would be better.

My goal is to have the top part of the form have the owner's info on it.
Then have either with a sub form (but would rather have tabs) have the
dog's
info

Sounds like you don't really need to do anything special then. If you just
put the subform into the main form, as long as the LinkMaster and LinkChild
fields are set up properly, the dogs that show up in the subform will, by
default, be only the dogs that belong to the owner. Then you can navigate
back and forth between the dogs belonging to that owner using the normal
record navigation.

Once you have the "dog" subform in place, you can add a tab control that
shows the different aspects of that dog that you want to keep separate. You
can even put a subform onto a tab if that is what you need to do.

HTH;

Amy

I guess that's my biggest question How do I relate the 2 forms where the sub
form only shows the owner's dogs? The linkmaster & linkchild is in the
query. Do I need to make a query & set the linkmaster/linkchild? Then
choose this query for the subform.

I've tried several ways, but keep getting funky results. Either shows all
the dogs, or changes the dogs names. I'm close I can feel it. Very
furstrating. Theory it's simple, but reality.....

My goal is to have the top part of the form have the owner's info on it.
Then have either with a sub form (but would rather have tabs) have the
dog's
info

Sounds like you don't really need to do anything special then. If you just
put the subform into the main form, as long as the LinkMaster and LinkChild
fields are set up properly, the dogs that show up in the subform will, by
default, be only the dogs that belong to the owner. Then you can navigate
back and forth between the dogs belonging to that owner using the normal
record navigation.

Once you have the "dog" subform in place, you can add a tab control that
shows the different aspects of that dog that you want to keep separate. You
can even put a subform onto a tab if that is what you need to do.

On Thu, 3 May 2007 14:46:00 -0700, HLarkin
wrote:

I guess that's my biggest question How do I relate the 2 forms where the sub
form only shows the owner's dogs? The linkmaster & linkchild is in the
query. Do I need to make a query & set the linkmaster/linkchild? Then
choose this query for the subform.

Two questions:

- Does the table of Dogs contain a field identifying that dog's Owner?

- Is that field listed in the Subform's Child Link Field property (the Subform
Control property on the main form; you won't find it on the form within that
control)?

John W. Vinson [MVP]

- Does the table of Dogs contain a field identifying that dog's Owner?

Yes, I created a new database just to figure out this problem.

2 tables -- 1 to many join (1 owner, many dogs) enforced integrity.

TBLOWNER
ownerid PK
firstname
lastname

TBLDOGS
dogid PK
dogname
ownerid

- Is that field listed in the Subform's Child Link Field property (the Subform
Control property on the main form; you won't find it on the form within that
control)?

I have set up a form with owner's info for the main form & a subform with
the dogname. ownerid is listed as the child.

When I first make the form I see a datasheet with the owner's dog in it.
Works fine. But I want to take the form one more step

The problem happens when I try to change the datasheet to a single form. My
goal is to make a field in which I can choose a dog & the information below
the field will be the corrosponding dog. I want the field to be set up as a
combo or list.

Thank you for your help.