retrieving data

[Joanne]

I have a table with 2 columns, CoName & CoLocation
CoName allows dupes, CoLocation does not.

On my form I will create a drop box to choose the CoName.
I then want to use a drop box to choose the CoLocation while seeing
only the locations that are relevant to the particular CoName I chose.

I'm not sure how to do this. Does each CoName need it's own
CoLocation table? Seems like it would be a heck of a lot of tables
going on.

Or perhaps I should have a record for each CoName and CoLocation, and
then choose the appropriate one (which is why I was thinking to design
CoName with dupes, CoLocation without dupes)?

If I put the CoName and CoLocation in the same record, can I then
cause the CoName to drop on the form in one place, and the CoLocation
in another place - what I mean is can I make the form's CoName field
fill from a column in the table, and the CoLocation field fill from
the second column in the table?

Also, I want the user to actually create the info in the database as
the app is used. I will not be filling the tables before using the
app.

All advise will sure be appreciated
Joanne

 

[Steve Schapel]

Joanne,

What is the purpose of this table? Do I understand correctly that each
CoName could have more than one CoLocation associated with it? But any
given CoLocation will only allow one associated CoName? Just who is
getting duped here? Maybe you could post back with some specific
examples of the data you are working with? In any case, it seems to me
that you will need a table that lists all the possible CoName and
CoLocation combinations, before you will be able to use the combobox (I
assume that's what you mean by "drop box"?) approach.

 

[Joanne]

Steve
I sure hope no one feels they are getting duped as a result of my
questions - I don't mean for that to be the case, I am quite serious
in my attempt to make this work.

The purpose of this table is to populate a combobox with choices for
the user when filling out a form.

You are correct - each CoName could have more than one CoLocation but
each CoLocation can have only one CoName.

Example
JohnDoeHardware Minneapolis
JohnDoeHardware Chicago
JohnDoeHardware Los Angeles
JohnDoeHardware Miami

Yes, I do mean combobox when I use drop box as a reference.

Perhaps the best approach would be to populate the table with a record
for each company name and it's location.

At any rate, thanks for your kind efforts

 

[J. Goddard]

It's not difficult. In the After Update event of the CoName combo box,
you populate the items in the second combo box, something like this:

me![combobox2].rowsource = "select [Colocation] from yourtable where
[CoName] = ' & me![combobox1] & "'"

Note the required use of the quotation marks.

Having said that, your description of the table does not make sense.
Not allowing Dups for the CoLocation means the only ONE company can be
in any one location. Using your example, only JohnDoeHardware could be
in Miami - no other companies could have a location in Miami.

What you mean (I think) is that there can be no duplicate combinations
of CoName - CoLocation. The easiest way to enforce that is to make
CoName and CoLocation the primary key of the table.

If users are going to be filling in the table, make sure the "limit to
List" property of the combo boxes is set to "No"

John

 

[Steve Schapel]

Joanne,

Yes, you will need to "manually" enter the data into this table. It is
not possible to select from a combobox when entering the data onto this
table, as the combobox needs to get its list of items from "somewhere",
so it can't look up itself.

Once you have this table, with data just as in the example you gave,
then you can certainly use *that* table as the Row Source of a combobox,
for the entry of data to *another* table... Is that what you mean?

If so, the standard way to do it would be to set the Row Source of the
CoName combobox to the equivalent of...
SELECT DISTINCT CoName FROM YourCoTable

And then the Row Source of the CoLocation combobox as the equivalent of...
SELECT CoLocation FROM YourCoTable WHERE CoName =
[Forms]![YourForm]![CoName]

The only other thing you need to do to make it work, then is to put code
on the After Update event property of the CoName combobox like this...
Me.CoLocation.Requery