Restricting a drop down list to a customers pets not all pets.

[TravelingHT]

I have a Table Customer.

Linked to Table Cusomer in a 1 to many relationship is Table Pets.

Also linked to Table Cutomer in a one to many relationship is Table Visits.

There is a linking table between Table Pets and Table Visits called "Pets At
Visit" wich is on the one end of a one to may relationship with both tables
Pets and Visits.

I am placing the a subform Pets At Visit(Based on the Table Pets At Visit)
in the Visits Form (Which is based on the Table Visits) and I will have a
drop down list to select which customers pets will be at the visit.

How do I restrict the pets displayed in the drop down list to only the pets
from that customer. How do I tell access that I only want to see pets from
this customer and not all pets in the Table Pets.

Do I need a query?

Do I need to create an expression?

Do you know of any example online I can examin to see this problem solved?

Thanks in advance.

 

[Beetle]

Presumably, you have a combo box (or something) on your Visits form
for selecting the Customer associated with that visit. For arguments sake,
let's assume that combo box is named cboCustomer and that the Visits
form is named frmVisits. Also, I'll assume that the bound column of
cboCustomers is CustomerID.

The Row Source of your Pets combo box (the one on the subform) would
look something like;

Select PetID, CustomerID, PetName From tblPets Where tblPets.CustomerID
= Forms!frmVisits!cboCustomer

Then you would hide the first two columns in your Pets combo box. You will
also need to requery the Pets combo box in the Current event of frmVisits
and the After Update event of cboCustomers.

 

[TravelingHT]

Dear Beetle:

You may live under a rotten tree but you sure know your access. Thanks for
the help I am reading Access 2003 Inside out, all about queries. I became
confused as to weather I should be using them to select only the data I need
or if I needed to do it your way, and your way sounds the best.

I am still confused on the use of queries as a basis for forms. but I guess
I will get over it with time. I will tell you how this fix goes.

Thanks again.

 

[Beetle]

You may live under a rotten tree but you sure know your access.

Actually, I live at Camp Swampy and am constantly taking abuse from
Sgt. Snorkel

I am still confused on the use of queries as a basis for forms.

Personally, I nearly always base forms and reports on queries, not directly
on the tables. Queries have several advantages over tables;

* You can sort the records by any criteria, in whatever order you choose.

* You can add calculated fields to your query that can be used in your
forms/reports (calculated fields do not belong in tables).

* In most cases, especially with tables that store a large amount of data,
you don't need (or want) to return the entire recordset every time the
form/report is opened. With a query, you can limit the number of records
returned using criteria.