Sub Form!

[Bob]

--
What I am trying to do is create a form that has 10 Client combo boxes. So
as When I am on the Horses from open, I can click a command that will take
me to another Form that has his owners listed on it i.e.: If I ma on the
form of "Big Red" I can click a command button that will take me to see who
is Owners are, I will create 10 fields to store the owners in tblHorseInfo
Any Help would be nice......Bob






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

 

[p]

ok you should probably re-read your question before posting because it
does not come across as clear. However if I guessed right this is what
you want to do. This is an example only, derive what information you
deem useful.

You have 2 tables

- Horses
- Owners

tbl_Horses
OwnerID, HorseID, HorseName

tbl_Owners
OwnerID, OwnerName, OwnerAddress

On a form you have a combo box called "HorseComboBox with the data
source:
SELECT * FROM tbl_Horses

On a 2nd form create a recordset SELECT * FROM tbl_Owners called
"frm_Owners"

On a button next to the combo box you have an OnClick command like the
following:

*********
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frm_Owners"

stLinkCriteria = "[OwnerID]=" & Me![HorseComboBox]
DoCmd.OpenForm stDocName, , , stLinkCriteria

**********

 

[Ken Sheridan]

Creating ten columns (fields) for owners in tblHorseInfo is the wrong way to
go. What you need are three tables, tblHorseInfo, tblOwners and
tblHorseOwnership. The last one models the many-to-many relationship between
tblHorseInfo and tblHorses. It does this by having two foreign key columns
HorseID and OwnerID which reference the primary keys of tblHorseInfo and
tblOwners. These two columns formm the composite primary key of the table.
So if a horse has 3 owners there are 3 rows in tblHorseOwnership with the
same HorseID and different owberID values. Similarly if a person has an
ownership intesest in 2 horses there would be two rows in tblHorseOwnership
with the same OwnerID and different HorseID values.

To see the owners for "Big Red" while on his record in a form based on
tblHorseInfo you can open another form based on a query such as:

SELECT HorseName, OwnerName
FROM tblOwners INNER JOIN
(tblHorseInfo INNER JOIN tblHorseOwnership
ON tblHorseInfo.HorseID = tblHorseOwnership.HorseID)
ON tblOwners.OwnerID = tblHorseOwnership.OwnerID
WHERE tblHorseInfo.HorseID = Forms!frmHorseInfo!HorseID
ORDER BY OwnerName;

where frmHorseInfo is the name of the form based on the tblHorseInfo table.
The code for a button on this form to open the other form would be:

Dim strCriteria As String

strCriteria = "HorseID = " & Me.HorseID
DoCmd. OpenForm "frmHorseOwnership", WhereCondition:=strCriteria

This assumes HorseID is a number data type. If it were text you'd wrap the
value in quotes:

strCriteria = "HorseID = """ & Me.HorseID & """"

An alternative ( and I'd say a better one) would be to have a subform in
frmHorseInfo for the owners. This would be based on the tblHorseOwnership
table, be in continuous form or datasheet view and linked to the parent form
on HorseID. It would have just one control, a combo box bound to the OwnerID
column with a RowSource of:

SELECT OwnerID,OwnerName
FROM tblOwners
ORDER BY OwnerName;

Its BoundColumn property would be 1, its ColumnCount property 2 and its
ColumnWidths property 0cm;8cm (or rough equivalent in inches but the first
dimension must be zero to hide the OwnerID column and show the names only).
With this arrangement you simply add a new row in the subform for each owner
by selecting a name from the list.

You can of course have other columns in tblHorseOwnership if you wish for
such things as the date the person acquired ownership etc.

Ken Sheridan
Stafford, England

 

[Ken Sheridan]

Oops, I duplicated the means of restricting the second form. As the query on
which the second form is based includes a parameter referencing the HorseID
control on the first form toy would not need to include the WhereCondition of
the OpenForm method, so just opening the form is enough:

DoCmd. OpenForm "frmHorseOwnership"

I'd still prefer the form/subform solution, however.

Ken Sheridan
Stafford, England