having combo box on subform reading many relationship

[icccapital]

I am new to subforms, but have been playing around with them recently.
Getting the subform to show a datasheet view fine. To add some better
functionality, what I am envisioning doing is having a subform that then has
a combo box that displays the "many" (one-to-many) part of the relationship
based on the "one" in the main form. Then when you choose from the combo box
in the subform some text boxes are filled with data. An example is we have a
client that has many contacts. So when you go to a client in the sub form
the combo box fills with contact names. When a contact name is chosen then
the address information is filled into the text boxes. I think this is
better to look at and easier to work with for users. Any possiblity of doing
it? thanks.

ps I can work in vb if need be.

 

[Linq Adams via AccessMonster.com]

What in the world do you need a combobox for? If you have the Child
Fields/Master Fields on your subform set up with the fields that connect the
two, your subform will do exactly as you describe, automatically! That's what
subforms are designed for! When you select a client on the main form, the
subform will show data on all the client's contacts.

BTW, while you can go into Design View for a form that's in Datasheet View,
and add controls (like a combobx) to your heart's delight, unless the control
is bound (which it wouldn't be, in your given scenario) the control
disappears when you run your form! Same goes for any unbound controls;
textboxes, buttons, labels, etc.

What you get in a Datasheet View form is the data from your table/query,
looking just like it does if you open the table or query independent of a
form; nothing more and nothing less! If you want to do "real" form things
like adding unbound controls, you have to use a Continuous View form and
format it to look like a datasheet view form.

Linq

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted via AccessMonster.com

 

[icccapital]

Ok, it appears that I am not understanding you or you are not understanding
me. Some of our clients have 15 contact that have addresses broken up into
seperate fields in a table. So in datasheet view I could select a client
"Orlando" and in datasheet view I would then see Joe,Smith,Address,City,State
(where commas represent field breaks) then see Mike,Moore,Address, etc...
This is not visual pleasurable to anybody but us IT guys, so the idea of the
combo box is: choose "Orlando" combo box in subform fills with Joe Smith and
Mike Moore. User selects Joe smith and in the each cooresponding text box
(address, city, etc) the information shows up to be edited. If the user
changes to Mike Moore then his data fills each text box. If this is not
possible so be it, I will move on.

 

[Arvin Meyer [MVP]]

Normally, this would be displayed like:

Mainform Client information, Subform, all 15 contacts.

The Clients table should have a related table ClientContacts, where the
ClientID is the foreign key link between the tables. Almost any other
configuration would be non-relational and a violation of Normalization
Rules.