Dropdown cb Question

[Bob]

I have a qry attached to a combo box that selects records in a form, The
trouble is that I have OwnerID, HorseName, OwnerName. Now it works when I
have one Owner in a horse but say if I have 3 Owners it only shows the first
Owners Form Record the 2 below show anything but, is it something to do with
being able to show multiple names the same.....Thanks Bob

My SQL Code Expr1001=Horses Name,

SELECT qryOHAP.OwnerID, qryOHAP.Expr1001,
qryOHAP.qHorseNameSourceModifyMode.HorseID, qryOHAP.OwnerLastName,
qryOHAP.QryOwnerHorsePercent.HorseID, qryOHAP.OwnerPercent,
qryOHAP.OwnerTitle, qryOHAP.OwnerLastName, qryOHAP.OwnerAddress
FROM qryOHAP
ORDER BY qryOHAP.Expr1001;








..........Jenny Vance

 

[John Vinson]

I have a qry attached to a combo box that selects records in a form, The
trouble is that I have OwnerID, HorseName, OwnerName. Now it works when I
have one Owner in a horse but say if I have 3 Owners it only shows the first
Owners Form Record the 2 below show anything but, is it something to do with
being able to show multiple names the same.....Thanks Bob

I don't know what you're expecting to see. "multiple names the same"
is not clear to me!

Could you give an example, with the desired result?

John W. Vinson[MVP]

 

[Bob]

I don't know what you're expecting to see. "multiple names the same"
is not clear to me!

Could you give an example, with the desired result?

John W. Vinson[MVP]

Mr Wells - Gold Horse
Mr Wells - yellow horse
Mr Wells- Blue horse

If I select Blue Horse I get Gold horse come into the drop down window, if I
select yellow horse I get Gold horse , if I select Gold horse all is
correct, because Mr Wells has a share in all three horses

Mr Smith only has a share in one horse and I select that horse everything is
correct, it just seems to be when a owner has a share in another horse I get
the incorrect information..........Thanks for your HELP.Bob

 

[Bob]

Mr Wells - Gold Horse
Mr Wells - yellow horse
Mr Wells- Blue horse

If I select Blue Horse I get Gold horse come into the drop down window, if
I select yellow horse I get Gold horse , if I select Gold horse all is
correct, because Mr Wells has a share in all three horses

Mr Smith only has a share in one horse and I select that horse everything
is correct, it just seems to be when a owner has a share in another horse
I get the incorrect information..........Thanks for your HELP.Bob

Oops I realize that if Gold horse has 3 owners I am going to have to click
each horse name to find the owner I want with his address.....Bob

 

[John Vinson]

Oops I realize that if Gold horse has 3 owners I am going to have to click
each horse name to find the owner I want with his address.....Bob

Well, at the very least, you need to realize that each combo box has
only one value. You can't select a horse and see three addresses in
the combo box!

I'm still not clear just what you want. Each combo box will have only
one value. One possibility might be to use an unbound combo box to
filter the Form, to show only the information relevant to that
selection; another idea would be to have two combo boxes, with the
second dependent on the first. For instance, you could have a combo
based on the table of horses, and a second combo based on a query
using that combo as a criterion, to see only that horse's owners.

John W. Vinson[MVP]

 

[Bob]

Well, at the very least, you need to realize that each combo box has
only one value. You can't select a horse and see three addresses in
the combo box!

I'm still not clear just what you want. Each combo box will have only
one value. One possibility might be to use an unbound combo box to
filter the Form, to show only the information relevant to that
selection; another idea would be to have two combo boxes, with the
second dependent on the first. For instance, you could have a combo
based on the table of horses, and a second combo based on a query
using that combo as a criterion, to see only that horse's owners.

John W. Vinson[MVP]

I have created another combo box that shows the horse and the owner, what
sort of code do I need in AfterUpdate, because I am still getting the same
situation where I can only sort one horse if the owner has more than
one...Thanks Bob

 

[John Vinson]

I have created another combo box that shows the horse and the owner, what
sort of code do I need in AfterUpdate, because I am still getting the same
situation where I can only sort one horse if the owner has more than
one...Thanks Bob

Could you please post the Row Source and Control Source properties of
the two combo boxes? I have NO idea what you mean by "only sort one
horse". For that matter, what is your table structure? Can one horse
have multiple owners, one owner have multiple horses, or both?

John W. Vinson[MVP]

 

[Bob]

Could you please post the Row Source and Control Source properties of
the two combo boxes? I have NO idea what you mean by "only sort one
horse". For that matter, what is your table structure? Can one horse
have multiple owners, one owner have multiple horses, or both?

John W. Vinson[MVP]

Thanks John
Can one horse have multiple owners, one owner have multiple horses, or both?
(Yes, to Both)

 

[John Vinson]

Thanks John
Can one horse have multiple owners, one owner have multiple horses, or both?
(Yes, to Both)

Since I cannot see your table structures, nor the SQL of qryOHAP, I'm
still in the dark. Remember: *you* can see your computer; you know
your data structures - I don't!

Given the many to many relationship, you certainly *should* have three
tables: Owners, with the owner's name and other bio information;
Horses, with identification and descriptive information about the
animal; and Ownership, with links to both these tables and other
inforamation about *this* owner's interest in *this* animal (I'm
guessing: fractional ownership? does this owner have physical custody
of the animal? ???)

But I don't know what your table structures *are* so I cannot suggest
how to fix the query (which I also cannot see).

John W. Vinson[MVP]

 

[Bob]

Sorry, Yes I have the horses in my stable and are owned by other people,
this horse below is owned 30% by me and 70% by Mr John Wells
I do have a Query that has , all the information in it is called "qryOHAP"
These are the fields:


and the information looks like this:

-----------------------------------------

[OwnerID ]

9

[QryOwnerHorsePercent.]

13

[OwnerPercent]

70.00%

[Expr1001]

Tiara Sands

[qHorseNameSourceModifyMode.HorseID]

13

[OwnerTitle] [OwnerFirstName] [OwnerLastName]

Mr John Wells

[OwnerAddress]

P O Box 125-238 Howick New Zealand

--------------------------------------------------

SQL of qryOHAP

SELECT QryOwnerHorsePercent.OwnerID, QryOwnerHorsePercent.HorseID,
QryOwnerHorsePercent.OwnerPercent, qHorseNameSourceModifyMode.Expr1001,
qHorseNameSourceModifyMode.HorseID, tblOwnerInfwnerTitle,
tblOwnerInfwnerFirstName, tblOwnerInfwnerLastName,
tblOwnerInfwnerAddress
FROM (QryOwnerHorsePercent INNER JOIN qHorseNameSourceModifyMode ON
QryOwnerHorsePercent.HorseID = qHorseNameSourceModifyMode.HorseID) INNER
JOIN tblOwnerInfo ON QryOwnerHorsePercent.OwnerID = tblOwnerInfwnerID
ORDER BY qHorseNameSourceModifyMode.Expr1001 DESC;


Thanks for your help Bob

 

[John Vinson]

SELECT QryOwnerHorsePercent.OwnerID, QryOwnerHorsePercent.HorseID,
QryOwnerHorsePercent.OwnerPercent, qHorseNameSourceModifyMode.Expr1001,
qHorseNameSourceModifyMode.HorseID, tblOwnerInfwnerTitle,
tblOwnerInfwnerFirstName, tblOwnerInfwnerLastName,
tblOwnerInfwnerAddress
FROM (QryOwnerHorsePercent INNER JOIN qHorseNameSourceModifyMode ON
QryOwnerHorsePercent.HorseID = qHorseNameSourceModifyMode.HorseID) INNER
JOIN tblOwnerInfo ON QryOwnerHorsePercent.OwnerID = tblOwnerInfwnerID
ORDER BY qHorseNameSourceModifyMode.Expr1001 DESC;

I'm sorry, Bob, but it's like pulling teeth to get the information I
need to help.

What's qryOwnerHorsePercent's SQL (and the SQL of any queries that it
contains)? What's qHorseNameSourceModifyMode?

I really wonder if this complex nested query is either necessary or
appropriate for use in your combo box! If you just want dependent
combo boxes, showing all the owners for a horse in a second combo box
after selecting the horse in the first, you don't NEED all this stuff;
if that isn't what you want... please explain what you DO want.

John W. Vinson[MVP]

 

[Bob]

I'm sorry, Bob, but it's like pulling teeth to get the information I
need to help.

What's qryOwnerHorsePercent's SQL (and the SQL of any queries that it
contains)? What's qHorseNameSourceModifyMode?

I really wonder if this complex nested query is either necessary or
appropriate for use in your combo box! If you just want dependent
combo boxes, showing all the owners for a horse in a second combo box
after selecting the horse in the first, you don't NEED all this stuff;
if that isn't what you want... please explain what you DO want.

John W. Vinson[MVP]

qHorseNameSourceModifyMode =(Just if the horse does not have a name it will
name it by its breeding age sex )

e.g.: (Zabeel Queen 3yo Filly) Father, Mother, Age, Sex



SELECT tblHorseInfo.HorseID, IIf(isnull(HorseName) Or
HorseName='',FatherName & ' ' & MotherName & ' ' &
funCalcAge(Format('01-Aug-' &
DateOfBirth,'dd/mmm/yyyy'),Format(Now(),'dd/mmm/yyyy'),1) & ' ' &
Sex,HorseName)
FROM tblHorseInfo;

-----------------------------------------------------------------

qryOwnerHorsePercent's (Links OwnerID, HorseID with percentage owned by
owner)

SELECT tblHorseDetails.OwnerID, tblHorseDetails.HorseID,
tblHorseDetails.OwnerPercent
FROM tblHorseDetails;

---------------------------------------------------------------



What I am trying to achieve is to have a form that I can select horse/owner
then print/email a report eg:



Mr John Wells

P O Box 125-238
St Helliers
Auckland



Tiara Sands 75%

 

[John Vinson]

What I am trying to achieve is to have a form that I can select horse/owner
then print/email a report eg:



Mr John Wells

P O Box 125-238
St Helliers
Auckland

Can you not use an unbound combo box based on the table of horses, and
an unbound combo based on the table of owners, as criteria in a Query
to select this information?

John W. Vinson[MVP]

 

[Bob]

Can you not use an unbound combo box based on the table of horses, and
an unbound combo based on the table of owners, as criteria in a Query
to select this information?

John W. Vinson[MVP]

I have tried but can only get 1 horse even if the owner has 10 horses
I select 1 of the other nine horses of his and I still just get that same 1
horse
if the owner only has 1 horse in the db, no problem.....Thanks Bob

 

[Bob]

I am using this code in After Update in my Combo Box:
Private Sub cbOwner_AfterUpdate()

If cbOwner.Text = "" Or IsNull(cbOwner.Text) = True Then
Exit Sub
End If
DoCmd.FindRecord val(cbOwner.value), , True, , True, acAll, True
Me.Filter = "OwnerID=" & val(cbOwner.value)
Me.FilterOn = True
'DoCmd.GoToRecord acDataForm, Me.Name, acGoTo, cbHorse.value

End Sub

Thanks.......Bob

 

[John Vinson]

I have tried but can only get 1 horse even if the owner has 10 horses
I select 1 of the other nine horses of his and I still just get that same 1
horse
if the owner only has 1 horse in the db, no problem.....Thanks Bob

Ok... let me see if I understand.

You want to select one horse from a combo box of horses.

You want to print a report showing that horse's owners' names - even
if that horse has multiple owners - and all other horses owned by all
those other owners?


John W. Vinson[MVP]

 

[Bob]

Ok... let me see if I understand.

You want to select one horse from a combo box of horses.

You want to print a report showing that horse's owners' names - even
if that horse has multiple owners - and all other horses owned by all
those other owners?


John W. Vinson[MVP]

Not Quite, Select 1 Horse/that particular Owner

So in my Combo box, I would have say:

Wells John Mr / Tiara Sands
Vance Bob Mr / Tiara Sands

So I select: Wells John Mr / Tiara Sands
and in the report I would get:
--------------------------------------
Mr John Wells
P O Box 125-238
St Helliers
Auckland

Tiara Sands 75%

 

[Bob]

Not Quite, Select 1 Horse/that particular Owner

So in my Combo box, I would have say:

Wells John Mr / Tiara Sands
Vance Bob Mr / Tiara Sands

So I select: Wells John Mr / Tiara Sands
and in the report I would get:
--------------------------------------
Mr John Wells
P O Box 125-238
St Helliers
Auckland

Tiara Sands 75%

Ooops Sorry, Horse Name/Owner Name
Combo List:

Tiara Sands / Wells John Mr
Tiara Sands / Vance Bob Mr

Thanx Bob