showing specific names with certain dogs in query/form

[HLarkin]

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

 

[Amy Blankenship]

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

 

[HLarkin]

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

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

HTH;

Amy

 

[Amy Blankenship]

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

 

[HLarkin]

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.

 

[John W. Vinson]

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]

 

[Amy Blankenship]

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

 

[HLarkin]

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.....

 

[John W. Vinson]

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]

 

[HLarkin]

- 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.

 

[John W. Vinson]

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.

I'm sorry, I don't understand this. You choose a dog and display "the
corresponding dog"???

What dog corresponds to what other dog?

John W. Vinson [MVP]

 

[HLarkin]

I'm sorry, I don't understand this. You choose a dog and display "the
corresponding dog"???

What dog corresponds to what other dog?

I guess your right, once I read it I was confused.

I want the field to show just the dogs that the owner owns. The problem I
am finding is that either all the dogs in the database are being shown or
some other weird configuration, from nothing appearing to getting an error.

If I change to subform to combo box or list. The intial control is dogname.
Nothing is shown below it. If I change anything then I start working with a
query & haven't figured out how the do this either. I've tried many
different configurations.

Thanks
Helena

 

[Amy Blankenship]

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.

The LinkMaster and LinkChild fields are properties you'll see in the Data
tab when you have selected the Subform control in design mode. To select
the Dogs Subform control, click into the Owners form Detail section. Then
click the ruler for the Dogs subform. Don't click anything else, then open
the properties panel. If you click the Data tab, you should see the
LinkMaster/LinkChild fields. If you do not, click the square in the upper
right of the Dog subform only _once_.

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.....

Sounds like the forms are not linked correctly.

The way I would design it is to have a subform that allows you to go back
and forth between the different dogs' records, then have tabs that allow you
to view the different information for the one dog you are looking at at the
moment (embedded in the subform). Subforms do not have to be visually
obvious. But if I were you, I'd rather use the control that provides the
functionality I need, rather than deciding what control I'd rather use even
if it won't work.

 

[Amy Blankenship]

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


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.

Try this. Make sure you've set up the relationship between tblOwner and
tblDogs in the Relationships window.

Delete the existing subform you have from your Owner form. Turn on the
wizard button that will help you set up your new subform. Click on the
subform control, then click where you want the upper left corner of your new
subform to be. The wizard should automatically link the two forms on
OwnerID. Call your subform Dogs_Subform and finish.

What you see when you run the form should be an owner form with a Dogs form
datasheet that only shows the dogs belogning to that owner. If that is
_not_ what you see, you've got data issues. Now, if you change the
datasheet to single form, you should then be able to navigate back and forth
between the dogs belonging to that owner with the inner set of record
selectors (the bottom set will be for navigating between owners, the one
above that is the one you'll use). If you then want to arrange the
information for that dog on tabs, you can cut the dog_Subform controls, add
a tab control, and paste the controls back onto the various tabs.

HTH;

Amy

 

[John W. Vinson]

I guess your right, once I read it I was confused.

I want the field to show just the dogs that the owner owns. The problem I
am finding is that either all the dogs in the database are being shown or
some other weird configuration, from nothing appearing to getting an error.

If I change to subform to combo box or list. The intial control is dogname.
Nothing is shown below it. If I change anything then I start working with a
query & haven't figured out how the do this either. I've tried many
different configurations.

All I can suggest is that you carefully read and follow Amy's advice, it's
right on the money.

Your Form should be based on the Owner table.

Your Subform should be based on the Dog table.

The subform can be a datasheet as Access initially offers, or a Single form
(you'll need to use the navigation buttons to move from one dog to the next),
or - my preference - a Continuous form so you can see all the dogs on the same
screen.

The Master Link Field and Child Link Field properties of the Subform control
should be OwnerID.


John W. Vinson [MVP]

 

[HLarkin]

The way I would design it is to have a subform that allows you to go back

and forth between the different dogs' records, then have tabs that allow you
to view the different information for the one dog you are looking at at the
moment (embedded in the subform). Subforms do not have to be visually
obvious. But if I were you, I'd rather use the control that provides the
functionality I need, rather than deciding what control I'd rather use even
if it won't work.


Amy,

I was able to do what you said (even before)...Basically what I have
gathered by reading your post as well as John's that I will NOT be able to
show a list of the dogs that each owner owns. (At least now without doing
some more SQL programming)

Thank you for all your help.

 

[John W. Vinson]

I was able to do what you said (even before)...Basically what I have
gathered by reading your post as well as John's that I will NOT be able to
show a list of the dogs that each owner owns. (At least now without doing
some more SQL programming)

<major frustration>

That is Not what Amy said. It is Not what I said.

If you have an Owners table with an OwnerID primary key...

and if you have a Dogs table with a DogID primary key, and an OwnerID foreign
key, linked in the Relationships window to the OwnerID...

and if you create a Form based on Owners, with a Subform based on Dogs...

and if you use OwnerID as the Master/Child Link Field...

then you will see one Owner on a form, and all of that Owner's dogs listed on
the Subform.

It might be a datasheet, with one dog per row.
It might be a Single subform, with one dog shown, and navigation buttons to
let you view each dog in turn.
Or it might be a Continuous form, with all of the information about the dog
laid out so that it's readable, but still showing repeated sets of controls so
you can see multiple dogs.

If you are seeing all of the dogs in the database in your subform, then
either:

- it's a popup form based on the Dogs table, and not a Subform in a Subform
control on the main form
- your relationships are incorrect
- you don't have the Master/Child Link Field set

It *IS* possible to do what you want, with no code and no SQL programming.
There's something you're not hearing, or that we're not hearing!

John W. Vinson [MVP]

 

[HLarkin]

It might be a datasheet, with one dog per row.
It might be a Single subform, with one dog shown, and navigation buttons to
let you view each dog in turn.
Or it might be a Continuous form, with all of the information about the dog
laid out so that it's readable, but still showing repeated sets of controls so
you can see multiple dogs.

It *IS* possible to do what you want, with no code and no SQL programming.
There's something you're not hearing, or that we're not hearing!

John,

I am very sorry it my last reply came off offensive, I did not mean it in
that way at all. I don't mind taking on a challange I just wanted to make
sure there wasn't something I overlooked.

You (and Amy) have help me tramendously!!! I was able to see the form in
all three methods you have stated above.

I guess what I would like to see if a way I can use: "It might be a Single
subform, with one dog shown, and navigation buttons to let you view each dog
in turn." I wanted a list box or combo box that will show the dogs a owner
owns & with a click I can go to that dog without using the navigation buttons
to "scroll" through each dog until I find the right one needed.

I was an office manager for a veterinary clinic for over 5 years & we used a
program called avimark. I am looking to make a small program to help our
training facility keep up with the records of the dogs better. (shots, etc.
I use Quickbooks for record keeping, so I don't need that)
http://www.avimark.com/Images/Program/tour/large/cid.jpg

If you break down this program you have 2 sections the owner & dog. I
wanted to create a combo/list box in the owner section that shows the dogs
that the owner owns. This way If I click on a particular dog then the dog
will show below.

I can use contiunous forms, but you can see how big the screen would have to
be if owners own 3 or more dogs.

I guess what I am trying to do is the same as the navigations buttons do in
the single form, but instead show a list/combo box to "scroll" to the dog I
want.

Again Sorry & thank you for your help.

 

[Amy Blankenship]

It might be a datasheet, with one dog per row.
It might be a Single subform, with one dog shown, and navigation buttons
to
let you view each dog in turn.
Or it might be a Continuous form, with all of the information about the
dog
laid out so that it's readable, but still showing repeated sets of
controls so
you can see multiple dogs.
John,

I am very sorry it my last reply came off offensive, I did not mean it in
that way at all. I don't mind taking on a challange I just wanted to make
sure there wasn't something I overlooked.

You (and Amy) have help me tramendously!!! I was able to see the form in
all three methods you have stated above.

I guess what I would like to see if a way I can use: "It might be a Single
subform, with one dog shown, and navigation buttons to let you view each
dog
in turn." I wanted a list box or combo box that will show the dogs a
owner
owns & with a click I can go to that dog without using the navigation
buttons
to "scroll" through each dog until I find the right one needed.

I was an office manager for a veterinary clinic for over 5 years & we used
a
program called avimark. I am looking to make a small program to help our
training facility keep up with the records of the dogs better. (shots,
etc.
I use Quickbooks for record keeping, so I don't need that)
http://www.avimark.com/Images/Program/tour/large/cid.jpg

If you break down this program you have 2 sections the owner & dog. I
wanted to create a combo/list box in the owner section that shows the dogs
that the owner owns. This way If I click on a particular dog then the dog
will show below.

I can use contiunous forms, but you can see how big the screen would have
to
be if owners own 3 or more dogs.

I guess what I am trying to do is the same as the navigations buttons do
in
the single form, but instead show a list/combo box to "scroll" to the dog
I
want.

I started out telling you how to make a combobox that would show the dogs.
You'd have to use an onchange event to requery the subform or navigate to
the appropriate record.

However, let's back up. If an owner has 3 dogs, you're talking about a
maximum of 2 clicks to navigate to the record you want (you'll always be on
one dog, so the dog you want is either that one or one of the other two).
What's the big deal? I have 5 dogs, and though I've worked for breeders who
might have as many as 30 dogs, they're unlikely to be your clients because
they are either going to be able to train for themselves or they're not
going to have all 30 in for training at the same time, because there
wouldn't be enough hours in the day. So you're looking at a practical limit
per client of at most 10 dogs, and that would be over several years, not
likely all at the same time. More than likely, you'd be better off
deactivating dogs that are dead, can't be trained for health reasons, or
their owners are unlikely to bring them back. That would bring the number
of dogs for any one client back into the 3-4 dog range. A drop-down list is
a minimum of 2 clicks, your record selectors are a maximum of 3 clicks for
the vast majority of owners.

Good luck!

-Amy

 

[John W. Vinson]

I guess what I would like to see if a way I can use: "It might be a Single
subform, with one dog shown, and navigation buttons to let you view each dog
in turn." I wanted a list box or combo box that will show the dogs a owner
owns & with a click I can go to that dog without using the navigation buttons
to "scroll" through each dog until I find the right one needed.

You can do this, with Amy's original solution.

It will require a little bit of VBA code but it's certainly doable. You could
create a Listbox on the main form selecting the dogs for that OwnerID; it
would be unbound - nothing in its Control Source - and have some pretty easy
code in its AfterUpdate event to find that dog's record. The listbox's
RowSource would be a query

SELECT DogID, DogName FROM Dogs WHERE ClientID = Forms!mainform!ClientID
ORDER BY DogName;

and its AfterUpdate event something like

Private Sub lstFindDog_AfterUpdate
Dim rs As Recordsource
Set rs = Me.subfrmDogs.RecordsetClone
rs.FindFirst "DogID = " & Me!lstFindDog
If rs.NoMatch Then
Msgbox "Error: DogID not found!"
Else
Me.subfrmDogs.Form.Bookmark = rs.Bookmark
End If
End Sub

I was an office manager for a veterinary clinic for over 5 years & we used a
program called avimark. I am looking to make a small program to help our
training facility keep up with the records of the dogs better. (shots, etc.
I use Quickbooks for record keeping, so I don't need that)
http://www.avimark.com/Images/Program/tour/large/cid.jpg

Well... if you want to exactly reproduce the appearance and functionality of
Avimark, you risk violating their copyright. That's breaking the law.

At the risk of tooting my own horn and stretching the rules here... you may
want to look at PawTrainer, an Access-based animal training business database
that my partner and I wrote. Send me an email if you would like a demo copy.


John W. Vinson [MVP]