PC Review


Reply
Thread Tools Rate Thread

Combo box in table definition

 
 
=?Utf-8?B?UlN1bmRheQ==?=
Guest
Posts: n/a
 
      17th Oct 2007
I have a table where the values in a field can be picked from a list - but
this list depends on the value in another field in the same table.

Example: I have a field called "kids". I can select a kid-value from a list
of kids - but I only want to select among the kids in the "family" - i.e. I
have a field specifying the family. This selection should limit my list of
kids to only the reæevant ones.

Can I set up a combo box where this relation is implemented?

Alternatively I create a form with this logic - but at this stage it would
be easier to do without.
 
Reply With Quote
 
 
 
 
Jeff Boyce
Guest
Posts: n/a
 
      17th Oct 2007
Lookup FIELDS, bad ... lookup TABLES, good (to paraphrase a colleague...)

It sounds like you are working directly in the table(s). This is almost a
necessity in Excel, but Access is a relational database. Access tables
store data, Access forms (and reports) display it.

Do the "lookup" work in your forms, using a combo box THERE.

The major problem most folks encounter with trying to use lookup fields in
tables is that what is stored does not match what is displayed ... this
causes considerable confusion.

Good luck!

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP
http://mvp.support.microsoft.com/

Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

"RSunday" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> I have a table where the values in a field can be picked from a list - but
> this list depends on the value in another field in the same table.
>
> Example: I have a field called "kids". I can select a kid-value from a

list
> of kids - but I only want to select among the kids in the "family" - i.e.

I
> have a field specifying the family. This selection should limit my list of
> kids to only the reæevant ones.
>
> Can I set up a combo box where this relation is implemented?
>
> Alternatively I create a form with this logic - but at this stage it would
> be easier to do without.


 
Reply With Quote
 
 
 
 
=?Utf-8?B?UlN1bmRheQ==?=
Guest
Posts: n/a
 
      17th Oct 2007
You are right about the confusion when one value is shown an another is
actually displayed. But right now I am just prototyping a data structure -
and once I have it right I'll make all the forms for data entry and display
etc. - but right now I just want something that works.

But I found out that I can just add data in the table views - and use the
relations defined between the tables - i.e. open a child table by clicking
the "+" to expand the hiearchy. When I add records in a child-table, the
relations to the parent is automatically made.

Rsunday

"Jeff Boyce" wrote:

> Lookup FIELDS, bad ... lookup TABLES, good (to paraphrase a colleague...)
>
> It sounds like you are working directly in the table(s). This is almost a
> necessity in Excel, but Access is a relational database. Access tables
> store data, Access forms (and reports) display it.
>
> Do the "lookup" work in your forms, using a combo box THERE.
>
> The major problem most folks encounter with trying to use lookup fields in
> tables is that what is stored does not match what is displayed ... this
> causes considerable confusion.
>
> Good luck!
>
> --
> Regards
>
> Jeff Boyce
> www.InformationFutures.net
>
> Microsoft Office/Access MVP
> http://mvp.support.microsoft.com/
>
> Microsoft IT Academy Program Mentor
> http://microsoftitacademy.com/
>
> "RSunday" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > I have a table where the values in a field can be picked from a list - but
> > this list depends on the value in another field in the same table.
> >
> > Example: I have a field called "kids". I can select a kid-value from a

> list
> > of kids - but I only want to select among the kids in the "family" - i.e.

> I
> > have a field specifying the family. This selection should limit my list of
> > kids to only the reæevant ones.
> >
> > Can I set up a combo box where this relation is implemented?
> >
> > Alternatively I create a form with this logic - but at this stage it would
> > be easier to do without.

>
>

 
Reply With Quote
 
Jeff Boyce
Guest
Posts: n/a
 
      17th Oct 2007
I'll try once more ... do your work with the data in forms, not in tables.
The subdatasheet ("+") feature, like the lookup field, encourages 'sheet
thinking, and will only make it harder to make the transition over to Access
as a not-spreadsheet tool.

I'm not saying not to spend time in tables, but make it "quality time" <g>!
Use your table time to define structure.

Best of luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

"RSunday" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> You are right about the confusion when one value is shown an another is
> actually displayed. But right now I am just prototyping a data structure -
> and once I have it right I'll make all the forms for data entry and
> display
> etc. - but right now I just want something that works.
>
> But I found out that I can just add data in the table views - and use the
> relations defined between the tables - i.e. open a child table by clicking
> the "+" to expand the hiearchy. When I add records in a child-table, the
> relations to the parent is automatically made.
>
> Rsunday
>
> "Jeff Boyce" wrote:
>
>> Lookup FIELDS, bad ... lookup TABLES, good (to paraphrase a colleague...)
>>
>> It sounds like you are working directly in the table(s). This is almost
>> a
>> necessity in Excel, but Access is a relational database. Access tables
>> store data, Access forms (and reports) display it.
>>
>> Do the "lookup" work in your forms, using a combo box THERE.
>>
>> The major problem most folks encounter with trying to use lookup fields
>> in
>> tables is that what is stored does not match what is displayed ... this
>> causes considerable confusion.
>>
>> Good luck!
>>
>> --
>> Regards
>>
>> Jeff Boyce
>> www.InformationFutures.net
>>
>> Microsoft Office/Access MVP
>> http://mvp.support.microsoft.com/
>>
>> Microsoft IT Academy Program Mentor
>> http://microsoftitacademy.com/
>>
>> "RSunday" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > I have a table where the values in a field can be picked from a list -
>> > but
>> > this list depends on the value in another field in the same table.
>> >
>> > Example: I have a field called "kids". I can select a kid-value from a

>> list
>> > of kids - but I only want to select among the kids in the "family" -
>> > i.e.

>> I
>> > have a field specifying the family. This selection should limit my list
>> > of
>> > kids to only the reevant ones.
>> >
>> > Can I set up a combo box where this relation is implemented?
>> >
>> > Alternatively I create a form with this logic - but at this stage it
>> > would
>> > be easier to do without.

>>
>>



 
Reply With Quote
 
John W. Vinson
Guest
Posts: n/a
 
      17th Oct 2007
On Wed, 17 Oct 2007 05:42:02 -0700, RSunday
<(E-Mail Removed)> wrote:

>I have a table where the values in a field can be picked from a list - but
>this list depends on the value in another field in the same table.
>
>Example: I have a field called "kids". I can select a kid-value from a list
>of kids - but I only want to select among the kids in the "family" - i.e. I
>have a field specifying the family. This selection should limit my list of
>kids to only the reevant ones.
>
>Can I set up a combo box where this relation is implemented?
>
>Alternatively I create a form with this logic - but at this stage it would
>be easier to do without.


I'm with Jeff - DON'T use table datasheets for data interaction.

For one thing, it's very easy to have a dependent combo box on a Form; I do
not believe that it is even *possible* in a table. So if you want this
functionality you must use a Form (which you should be doing anyway!)

How to set this up on a form depends on your table structure. You can set the
RowSource of a combo box to a Query selecting only records from the desired
family, by using

=Forms!NameOfForm!NameOfControl

as a criterion where NameOfControl is the name of a control on the form
containing the FamilyID.

John W. Vinson [MVP]
 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
What is the definition for declaration respective a definition Tony Johansson Microsoft C# .NET 3 28th Jan 2010 02:14 AM
Run-Time Error 1004 Application-Definition or Object-Definition Error jparnold Microsoft Excel Programming 5 25th Dec 2009 05:33 PM
Definition Update for Windows Defender - KB915597 (Definition 1.3. Sheesheeb Windows XP Security 3 24th Nov 2009 07:21 PM
Definition Update for Windows Defender-KB91557 (Definition 1.49.52 Adrienne Spyware Announcements 10 17th Dec 2008 02:57 PM
Is these actually any kind of definition between a definition and declaration Tony Johansson Microsoft C# .NET 4 22nd Sep 2008 01:40 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 11:28 AM.