Combo Box Help

[Commish]

Help me please. (Access 2007)

I have added a Combo Box to a form. The combo box appears to be
implemented properly; it shows the list of values, and I can select a
value.

But, when I choose a value, nothing else changes. Shouldn't the other
data from the record selected in the combo box then populate the
correct fields?

What do I need to do to get this linkage to work? Or is there a
completely different process that I should be using.

It seems like what I am trying to do shouldn't be that difficult, but
I cannot get past this hurdle.

 

[John W. Vinson]

Help me please. (Access 2007)

I have added a Combo Box to a form. The combo box appears to be
implemented properly; it shows the list of values, and I can select a
value.

But, when I choose a value, nothing else changes. Shouldn't the other
data from the record selected in the combo box then populate the
correct fields?

What do I need to do to get this linkage to work? Or is there a
completely different process that I should be using.

It seems like what I am trying to do shouldn't be that difficult, but
I cannot get past this hurdle.

A combo box can have several purposes - navigating to a chosen record is just
one of them; more common is to have it select a value to be stored in a table.

Delete this combo. When you put the it back on the form, be sure the "use
control wizards" icon is selected in the Design tools window, and let the
wizard walk you through; choose the option "Find a record on my form" in the
popup box.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Commish]

A combo box can have several purposes - navigating to a chosen record is just
one of them; more common is to have it select a value to be stored in a table.

Delete this combo. When you put the it back on the form, be sure the "use
control wizards" icon is selected in the Design tools window, and let the
wizard walk you through; choose the option "Find a record on my form" in the
popup box.
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

Hey - that works! Great Thanks.

Now, part 2 - the tutorials at Microsoft.com show a user dropping a
form onto an existing form and voila! the data is linked. So, when I
dropped my form onto the form with the combo box, why doesn't the
subform jump to the related record?

 

[John W. Vinson]

Now, part 2 - the tutorials at Microsoft.com show a user dropping a
form onto an existing form and voila! the data is linked. So, when I
dropped my form onto the form with the combo box, why doesn't the
subform jump to the related record?

Possibly because you do not have a Relationship (with referential integrity
enforced) defined between the main form's and the subform's tables. Hard for
me to say since I can't see your screen!
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Commish]

Possibly because you do not have a Relationship (with referential integrity
enforced) defined between the main form's and the subform's tables. Hard for
me to say since I can't see your screen!
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

I have the relationships... but not the referential integrity. I have
the correct, unique indexes on the table.

But the relationship diagram won't let me make the referential
integrity link. Because...

I am trying to relate a table (let's call it data), to 2 other tables
(lets call them Customers and Clients). The data table should have 1
record for each customer and 1 record for each client. But, the data
table records don't completely match either complete collection of
clients or customers....

 

[Commish]

I have the relationships... but not the referential integrity. I have
the correct, unique indexes on the table.

But the relationship diagram won't let me make the referential
integrity link. Because...

I am trying to relate a table (let's call it data), to 2 other tables
(lets call them Customers and Clients). The data table should have 1
record for each customer and 1 record for each client. But, the data
table records don't completely match either complete collection of
clients or customers....

Let me try this differently - with all cards on the table, because
clearly, as much as I've done in Access in the past, I am green as far
as forms and subforms...

I am attempting to create a tool to track an auction of baseball
players for a fantasy league. I have the following tables:

DraftTracker - tracks things like salary, round, pick number, team
assignment, etc.
2011_Hitters - has statistical projections for all position players
2011_pitchers - has statistical projections for all pitchers
GameByPosition - has 2010 season defensive data
2006_hitters - has historical hitting data from 2006-2010
2006_pitchers - has historical pitching data from 2006-2010

Relationships:
1-1: DraftTracker to 2011_Hitters
1-1: DraftTracker to 2011_Pitchers
(there's a 1-1 from the DraftTracker to those 2 tables, but not in the
reverse direction - the draftTracker should include a combination of
hitters and pitchers)

1-0 or 1: DraftTracker to GameByPosition - if the player didn't play
in 2010, he'll have no record
1-0 or 1 or Many: DraftTracker to 2006 Hitters - if the player played
in any season from 2006-2010, he'll have a record. he may have 0,1, or
5 records
1-0 or 1 or Many: DraftTracker to 2006 Pitchers - if the player
pitched in any season from 2006-2010, he'll have a record. he may have
0,1, or 5 records

Does this help any? Where have I gone wrong? I still have a few weeks
to get this right.

 

[John W. Vinson]

I have the relationships... but not the referential integrity. I have
the correct, unique indexes on the table.

Then try manually setting the Master Link Field and Child Link Field
properties of the subform control.

But the relationship diagram won't let me make the referential
integrity link. Because...

I am trying to relate a table (let's call it data), to 2 other tables
(lets call them Customers and Clients). The data table should have 1
record for each customer and 1 record for each client. But, the data
table records don't completely match either complete collection of
clients or customers....

Well... fix the data then. Does it make any sense to have data in your table
that you can't trust because it doesn't belong to either a client or a
customer?

I suspect your table design is questionable. I'd be inclined to have a master
table of People, with a field or fields (or possibly a one-to-one related
table for Subclassing) to indicate that the person is a customer, or a client,
or both.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Commish]

Then try manually setting the Master Link Field and Child Link Field
properties of the subform control.



Well... fix the data then. Does it make any sense to have data in your table
that you can't trust because it doesn't belong to either a client or a
customer?

I suspect your table design is questionable. I'd be inclined to have a master
table of People, with a field or fields (or possibly a one-to-one related
table for Subclassing) to indicate that the person is a customer, or a client,
or both.
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

The data is fine - it's either my design concept, or my explanation of
what I am doing.

What I was trying to do was keep my clients and customers separate
because they each have separate lists of attributes.

In the meantime, what I have done that works, Is that I have a table
each for the clients and the customers, the common data that I was
trying to keep in a single table, now is stored in 2 separate tables.
I can join that data via query when I need it in that format.

Thanks a lot for your help - the comment that directed me towards
referential integrity helped me a bunch. That and recommending that
the combo box wizard select a record in a table, I think I was staying
with the default which was to store a value (I think). So, for now,
it's doing what I want to.

For some reason, I am having trouble moving fields around in a form. I
started by arranging them tabular, but I want to have some stacked and
some tabular... I'll get that worked out.