picking from a specific list in a combo box

[leeandgracebest via AccessMonster.com]

I have a form that has a combo box called "contact" that refers to a table
called "Contacts". I would like to pick from a list of contacts for a
specific client without displaying all of the names in the contacts table,
just the ones that match the name of the client on the main form. I've tried
joining the ID's in the relationships but it doesn't seem to work for me. Any
help appreciated! Thanks Lee

 

[Maurice]

Try to set the controlsource of the combobox to a query. Set the contacts
field to a critera referring to the id-field from your main form.

Could look something like this:

forms!formname!idfield

where formname is the name of your form
where idfield is the name of your field which holds the contact id

Also remember to place the requery code in the form_current event to make
sure the combo is filtered by the proper contacts after you select another
record so:

private sub Form_current ()
me.combobox.requery '-> where combobox is the name of your combo
end sb

hth

 

[Jeanette Cunningham]

I would like to pick from a list of contacts for a
specific client without displaying all of the names in the contacts
table,
just the ones that match the name of the client on the main form. I've
tried

Would you post some details of your table set up for Contacts and Clients.
for each table - name of primary key
the relationship between the 2 tables
names of contact and client names fields

Jeanette Cunningham

 

[leeandgracebest via AccessMonster.com]

Hello Jeanett. Here are my tables with the main fields: (This is a tabbed
form for catering at a hotel)

tbl - ClientList: [A one to many relationship with contacts], [A one to many
relationship with Function Info]
Primary Key: "ID": (AutoNumber)
MainTel, EntryDate, Year, ClientName, Address, City, Prov, Postcode, USZip,
Country [ All are text except the date field]

tbl - Function Info:
"FunctionID" (Text - Duplicates Ok)
Field name: Contact (Text) - A Combo box field for Contact's name
Field name: FunctionNumber (for more than one function with the same client
the same day)
Relates to the ClientListID and the FunctionNumber.
tbl - Contacts:
ContactID (Text), EntryDate, Telephone #, etc.
tbl - Charges:
ChargsID: (Text - Duplicates Ok)
Relates to Client "ID" and FunctionNumber

I would like to pick from a list of contacts for a
specific client without displaying all of the names in the contacts
table,
just the ones that match the name of the client on the main form. I've
tried

Would you post some details of your table set up for Contacts and Clients.
for each table - name of primary key
the relationship between the 2 tables
names of contact and client names fields

Jeanette Cunningham

I have a form that has a combo box called "contact" that refers to a table
called "Contacts". I would like to pick from a list of contacts for a

[quoted text clipped - 5 lines]

Any
help appreciated! Thanks Lee

 

[Jeanette Cunningham]

create a new query in design view
--add tbl ClientList, tbl ContactID and tblFunctionInfo
--join the tblClientList to tblFunctionInfo on the field ClientListID
--join the tblClientList to tblContacts on the field ClientListID
--drag the fields ContactID, Contact and ClientListID on to the query grid
--in the criteria row for ClientListID type
Forms!FormName!ComboClient
--replace FormName with the name of your form with the combo with client
names
--replace ComboClient with name of your combo with client names

--save the query as
qryClientContacts

--back on your form, in the row source for the combo with contact names
--select qryClientContacts from the drop down

Test the form

Jeanette Cunningham

Hello Jeanett. Here are my tables with the main fields: (This is a tabbed
form for catering at a hotel)

tbl - ClientList: [A one to many relationship with contacts], [A one to
many
relationship with Function Info]
Primary Key: "ID": (AutoNumber)
MainTel, EntryDate, Year, ClientName, Address, City, Prov, Postcode,
USZip,
Country [ All are text except the date field]

tbl - Function Info:
"FunctionID" (Text - Duplicates Ok)
Field name: Contact (Text) - A Combo box field for Contact's name
Field name: FunctionNumber (for more than one function with the same
client
the same day)
Relates to the ClientListID and the FunctionNumber.
tbl - Contacts:
ContactID (Text), EntryDate, Telephone #, etc.
tbl - Charges:
ChargsID: (Text - Duplicates Ok)
Relates to Client "ID" and FunctionNumber

I would like to pick from a list of contacts for a
specific client without displaying all of the names in the contacts
table,
just the ones that match the name of the client on the main form. I've
tried

Would you post some details of your table set up for Contacts and Clients.
for each table - name of primary key
the relationship between the 2 tables
names of contact and client names fields

Jeanette Cunningham

I have a form that has a combo box called "contact" that refers to a
table
called "Contacts". I would like to pick from a list of contacts for a

[quoted text clipped - 5 lines]

Any
help appreciated! Thanks Lee

 

[Jeanette Cunningham]

Oops! Forgot to say how to set up the combos.
Combo with Clients has 2 columns with ClientID as the first column which is
hidden.
The bound column is column 1.

Combo with Contacts has 2 rows, ContactID as the first column which is
hidden, the second column is Contact
The bound column is column 1.
You can sort qryClientContacts to make it easier to select the contact you
want.

Jeanette Cunningham

create a new query in design view
--add tbl ClientList, tbl ContactID and tblFunctionInfo
--join the tblClientList to tblFunctionInfo on the field ClientListID
--join the tblClientList to tblContacts on the field ClientListID
--drag the fields ContactID, Contact and ClientListID on to the query grid
--in the criteria row for ClientListID type
Forms!FormName!ComboClient
--replace FormName with the name of your form with the combo with client
names
--replace ComboClient with name of your combo with client names

--save the query as
qryClientContacts

--back on your form, in the row source for the combo with contact names
--select qryClientContacts from the drop down

Test the form

Jeanette Cunningham

Hello Jeanett. Here are my tables with the main fields: (This is a tabbed
form for catering at a hotel)

tbl - ClientList: [A one to many relationship with contacts], [A one to
many
relationship with Function Info]
Primary Key: "ID": (AutoNumber)
MainTel, EntryDate, Year, ClientName, Address, City, Prov, Postcode,
USZip,
Country [ All are text except the date field]

tbl - Function Info:
"FunctionID" (Text - Duplicates Ok)
Field name: Contact (Text) - A Combo box field for Contact's name
Field name: FunctionNumber (for more than one function with the same
client
the same day)
Relates to the ClientListID and the FunctionNumber.
tbl - Contacts:
ContactID (Text), EntryDate, Telephone #, etc.
tbl - Charges:
ChargsID: (Text - Duplicates Ok)
Relates to Client "ID" and FunctionNumber

I would like to pick from a list of contacts for a
specific client without displaying all of the names in the contacts
table,
just the ones that match the name of the client on the main form. I've
tried

Would you post some details of your table set up for Contacts and
Clients.
for each table - name of primary key
the relationship between the 2 tables
names of contact and client names fields

Jeanette Cunningham

I have a form that has a combo box called "contact" that refers to a
table
called "Contacts". I would like to pick from a list of contacts for a
[quoted text clipped - 5 lines]
Any
help appreciated! Thanks Lee

 

[leeandgracebest via AccessMonster.com]

Hi Jeanette... thank you for your help. I've tried to follow your
instructions, but it doesn't work for me. I just need to see the contacts in
the combo box for a particular client and choose from the list. Is it
possible for me to email you my mdb file so you can have a look at it? I
would also like to have the form requerey when I go to the next record. Many
thanks, Lee

Oops! Forgot to say how to set up the combos.
Combo with Clients has 2 columns with ClientID as the first column which is
hidden.
The bound column is column 1.

Combo with Contacts has 2 rows, ContactID as the first column which is
hidden, the second column is Contact
The bound column is column 1.
You can sort qryClientContacts to make it easier to select the contact you
want.

Jeanette Cunningham

create a new query in design view
--add tbl ClientList, tbl ContactID and tblFunctionInfo

[quoted text clipped - 61 lines]

 

[Jeanette Cunningham]

Lee,
I would like to say yes to your request, but unfortunately I have deadlines
to meet for databases for clients.
From your previous post, I have the feeling that your table setup could be
making this difficult - I was not able to work out which fields which were
the foreign key fields in the relationships between the tables.
Perhaps you would post back showing which fields are the primary key and
which fields are the foreign key and specify which field is the joining
field between each of the tables. Do you have referential integrity set for
these tables? Do you have left or right join types?

Jeanette Cunningham

Hi Jeanette... thank you for your help. I've tried to follow your
instructions, but it doesn't work for me. I just need to see the contacts
in
the combo box for a particular client and choose from the list. Is it
possible for me to email you my mdb file so you can have a look at it? I
would also like to have the form requerey when I go to the next record.
Many
thanks, Lee

Oops! Forgot to say how to set up the combos.
Combo with Clients has 2 columns with ClientID as the first column which
is
hidden.
The bound column is column 1.

Combo with Contacts has 2 rows, ContactID as the first column which is
hidden, the second column is Contact
The bound column is column 1.
You can sort qryClientContacts to make it easier to select the contact you
want.

Jeanette Cunningham

create a new query in design view
--add tbl ClientList, tbl ContactID and tblFunctionInfo

[quoted text clipped - 61 lines]

Any
help appreciated! Thanks Lee

 

[leeandgracebest via AccessMonster.com]

Hi Jeanette... I'm finally getting back to this. Thanks for responding. I'll
try to outline what I'm working with and hopefully it will make some sense.

I'm using three of my tables in this query. The first one is my List of
Clients, "tblClientList". The primary key is an auto number field called
"ClientID" (no duplicates). I have joined it to two other tables on the
foreign ID keys. One is a table containing Contact names and information that
pertains to the client to which I have joined "ClientID" from the
tblClientList to "ContactID" which is a number field. The other is a table
containing information about catering functions and I have joined
"FunctionID" from the first table "tblClientList" containing the primary key
"ClientID". I have put them onto the query grid and pulled down "ContactID"
from the tblContacts in the first column, "Contact" from the tblContacts in
the second column and "ClientID" from the tblClientList in the third column.
In the Criteria for ClientID I have entered [Forms]![frmFunction Information]!
[Contact]. The "tblClientList" is the basis for my main form onto which I
have tabbed forms for Contacts, and Functions.

On the form "frmFunction Information", I have my combo box and a field called
"Contact". In the properties for the combo box, the Row Source is my
"qryClientContacts", Column count 3, Bound to Column 1. I want it to display
a pick list of contacts that I can choose from that pertain to the client .
When I click on the box in form mode, nothing is displayed and when I run the
query from the grid, I am asked to enter a parameter value for [Forms]!
[frmFunction Information]![Contact].

The relationships are one to many from the Client List to the Contacts to the
Function Information tables. i.e. "Only include rows where the joined fields
from both tables are equal."

When I finally get it working, I would like the combo box to requery each
time a new client is looked at in the main form.

Does this all make sense? I'ts a bit puzzling for me as I can't figure out
what is out of place or not there so that it works properly. I hope you are
able to help.
Many thanks!
Lee

Lee,
I would like to say yes to your request, but unfortunately I have deadlines
to meet for databases for clients.
From your previous post, I have the feeling that your table setup could be
making this difficult - I was not able to work out which fields which were
the foreign key fields in the relationships between the tables.
Perhaps you would post back showing which fields are the primary key and
which fields are the foreign key and specify which field is the joining
field between each of the tables. Do you have referential integrity set for
these tables? Do you have left or right join types?

Jeanette Cunningham

Hi Jeanette... thank you for your help. I've tried to follow your
instructions, but it doesn't work for me. I just need to see the contacts

[quoted text clipped - 24 lines]

 

[Jeanette Cunningham]

Lee,
the extra info is most helpful.
I will outline how I think the tables need to be set up to make best use of
Access' relational features.
To protect your existing data, make 2 backup copies of all 3 tables before
you make any changes to your tables.
While making changes to tables, always keep at least one backup that you
never destroy - then it is always there for reference if something goes
wrong.


tblClientList
ClientD >> Primary Key
Client Name
other client details

tblContacts
ContactID >> Primary Key
ClientID >> Foreign Key
other contact details

In the relationships window, drag the ClientID from tblClientList and drop
it on the ClientID in tblContacts.

tblFunctions
FunctionID >> Primary Key
other function details

tblAttendance
AttendanceID >> Primary Key
ContactID >> Foreign Key
FunctionID >> Foreign Key
other details about attendance


In the relationships window, drag the ContactID from tblContacts and drop it
on the ContactID in tblAttendance.
Drag the FunctionID from tblFunctions and drop it on the FunctionID in
tblAttendance.

tblAttendance allows multiple contacts to attend multiple functions.
There is a many to many relationship between Contacts and Functions

You will need to re-design your forms.
Here is a description of how to set up forms for a many to many
relationship.

Use a main form (single view) for one of the Many side tables. Within the
main form, use a continuous subform bound to the junction table with the
Link Master set to the main form's PK field. This subform has at least one
visible control that is a combo box bound to the second table's PK field.
This form's Current event and the combo's AfterUpdate event sets an
invisible text box on the main form to the value of the combo box.

Along side the continuous subform is another subform bound to the other many
side table with its Link Master property set to the invisible text box.

With this arrangement, you can navigate or search for the main form record.
The continuous subform displays the items assigned to the main form record.
Clicking on a record in the continuous subform automatically displays the
related
record in the other subform.

You'll find an example of a subform used in this way in the sample Northwind
database which comes with Access. In that the Orders Subform is based on
the
Order Details table (the subform actually uses a query, but that's
immaterial
to this issue) which models the many-to-many relationship between Orders and
Products. In the subform a combo box is bound to the ProductID column,
which
is the foreign key referencing the key of the Products table. The combo box
is set up in the way described above to show the name of the product,
hiding the ProductID which is its actual value. It also shows a
Discontinued
column in its list, but that's immaterial to the current issue.

Good luck
Jeanette Cunningham

Hi Jeanette... I'm finally getting back to this. Thanks for responding.
I'll
try to outline what I'm working with and hopefully it will make some
sense.

I'm using three of my tables in this query. The first one is my List of
Clients, "tblClientList". The primary key is an auto number field called
"ClientID" (no duplicates). I have joined it to two other tables on the
foreign ID keys. One is a table containing Contact names and information
that
pertains to the client to which I have joined "ClientID" from the
tblClientList to "ContactID" which is a number field. The other is a table
containing information about catering functions and I have joined
"FunctionID" from the first table "tblClientList" containing the primary
key
"ClientID". I have put them onto the query grid and pulled down
"ContactID"
from the tblContacts in the first column, "Contact" from the tblContacts
in
the second column and "ClientID" from the tblClientList in the third
column.
In the Criteria for ClientID I have entered [Forms]![frmFunction
Information]!
[Contact]. The "tblClientList" is the basis for my main form onto which I
have tabbed forms for Contacts, and Functions.

On the form "frmFunction Information", I have my combo box and a field
called
"Contact". In the properties for the combo box, the Row Source is my
"qryClientContacts", Column count 3, Bound to Column 1. I want it to
display
a pick list of contacts that I can choose from that pertain to the client
.
When I click on the box in form mode, nothing is displayed and when I run
the
query from the grid, I am asked to enter a parameter value for [Forms]!
[frmFunction Information]![Contact].

The relationships are one to many from the Client List to the Contacts to
the
Function Information tables. i.e. "Only include rows where the joined
fields
from both tables are equal."

When I finally get it working, I would like the combo box to requery each
time a new client is looked at in the main form.

Does this all make sense? I'ts a bit puzzling for me as I can't figure out
what is out of place or not there so that it works properly. I hope you
are
able to help.
Many thanks!
Lee

Lee,
I would like to say yes to your request, but unfortunately I have
deadlines
to meet for databases for clients.
From your previous post, I have the feeling that your table setup could be
making this difficult - I was not able to work out which fields which were
the foreign key fields in the relationships between the tables.
Perhaps you would post back showing which fields are the primary key and
which fields are the foreign key and specify which field is the joining
field between each of the tables. Do you have referential integrity set
for
these tables? Do you have left or right join types?

Jeanette Cunningham

Hi Jeanette... thank you for your help. I've tried to follow your
instructions, but it doesn't work for me. I just need to see the
contacts

[quoted text clipped - 24 lines]

Any
help appreciated! Thanks Lee

 

[leeandgracebest via AccessMonster.com]

Jeanette, Thanks so much for this information! I'll work away at it and let
you know how I make out.
Regards,
Lee

Lee,
the extra info is most helpful.
I will outline how I think the tables need to be set up to make best use of
Access' relational features.
To protect your existing data, make 2 backup copies of all 3 tables before
you make any changes to your tables.
While making changes to tables, always keep at least one backup that you
never destroy - then it is always there for reference if something goes
wrong.

tblClientList
ClientD >> Primary Key
Client Name
other client details

tblContacts
ContactID >> Primary Key
ClientID >> Foreign Key
other contact details

In the relationships window, drag the ClientID from tblClientList and drop
it on the ClientID in tblContacts.

tblFunctions
FunctionID >> Primary Key
other function details

tblAttendance
AttendanceID >> Primary Key
ContactID >> Foreign Key
FunctionID >> Foreign Key
other details about attendance

In the relationships window, drag the ContactID from tblContacts and drop it
on the ContactID in tblAttendance.
Drag the FunctionID from tblFunctions and drop it on the FunctionID in
tblAttendance.

tblAttendance allows multiple contacts to attend multiple functions.
There is a many to many relationship between Contacts and Functions

You will need to re-design your forms.
Here is a description of how to set up forms for a many to many
relationship.

Use a main form (single view) for one of the Many side tables. Within the
main form, use a continuous subform bound to the junction table with the
Link Master set to the main form's PK field. This subform has at least one
visible control that is a combo box bound to the second table's PK field.
This form's Current event and the combo's AfterUpdate event sets an
invisible text box on the main form to the value of the combo box.

Along side the continuous subform is another subform bound to the other many
side table with its Link Master property set to the invisible text box.

With this arrangement, you can navigate or search for the main form record.
The continuous subform displays the items assigned to the main form record.
Clicking on a record in the continuous subform automatically displays the
related
record in the other subform.

You'll find an example of a subform used in this way in the sample Northwind
database which comes with Access. In that the Orders Subform is based on
the
Order Details table (the subform actually uses a query, but that's
immaterial
to this issue) which models the many-to-many relationship between Orders and
Products. In the subform a combo box is bound to the ProductID column,
which
is the foreign key referencing the key of the Products table. The combo box
is set up in the way described above to show the name of the product,
hiding the ProductID which is its actual value. It also shows a
Discontinued
column in its list, but that's immaterial to the current issue.

Good luck
Jeanette Cunningham

Hi Jeanette... I'm finally getting back to this. Thanks for responding.
I'll

[quoted text clipped - 71 lines]