Field Values

  • Thread starter Thread starter Karen
  • Start date Start date
K

Karen

I have a form for our client companies and would like to
specify a particular group of company divisions populate a
list box when the primary company name is used. My table
is set up (in part) like:

CompanyName
Division

I don't want all of company a's divisions to be part of
the choice when company b is typed in.

Is this clear as mud and can anyone tell me how?
 
Set the listbox RecordSource to:
"Select Division from MyTable where CompanyName = " &
Forms!myForm!CompanyName
 
Thanks John! Okay, so now here is where I sound like a
real dummy...
I don't understand how I specify it for each individual
company. Do I need to make a table for each company's
divisions?
 
Karen,

There are a couple of typos in John's reply which may be contributing
to the confusion.

And the answer to your question is No, you don't have separate tables.
However, I presume you have a Companies table, with all the core
company-related information, and also a Divisions table which has a
record for each division of each company? In this type of scenario,
it is more usual to have some sort of ID number, being the primary key
field of the Companies table, as the basis of the link between the two
tables. So the fields in the Divisions table look something like...
DivisionID
CompanyID
DivisionName

OK, so now you have a form where you enter the Company, and you want
the listbox to show all Divisions for that company. You need to make
the RowSource of the listbox to be a query based on the Divisions
table. This is the idea that John was driving at. It depends how the
Company is being entered on the form. One option would be to use a
combobox, whose RowSource is the Companies table. You can set the
combobox up so that the first column (i.e. the CompanyID) is the bound
column, but the second column (i.e. the Company Name) is what is
displayed in the combobox. If that's the case, make a query based on
the Divisions table, with the DivisionName field and the CompanyID
field, and then in the criteria of the CompanyID field, refer to the
Company combobox on the form, using syntax such as...
[Forms]![NameOfYourForm]![NameOfCompanyCombobox]. Then, you use this
query as the RowSource of the listbox. John's suggestion involved
entering the SQL of this type of query in to the listbox's rowsource,
instead of the name of the query, so using my example you could put:
SELECT Division FROM DivisionsTable WHERE CompanyID =
[Forms]![NameOfYourForm]![NameOfCompanyCombobox]

One more step... you should put code something like this, on the
AfterUpdate event of the Company combobox...
Me.NameOfListbox.Requery

Now, if your setup differs, obviously you will need to adapt, but hope
that might help to move you forward.

- Steve Schapel, Microsoft Access MVP
 
Thanks Steve! I do understand it quite a bit better now
and appreciate your time. Hope your holidays were bright
and merry!

Karen
-----Original Message-----
Karen,

There are a couple of typos in John's reply which may be contributing
to the confusion.

And the answer to your question is No, you don't have separate tables.
However, I presume you have a Companies table, with all the core
company-related information, and also a Divisions table which has a
record for each division of each company? In this type of scenario,
it is more usual to have some sort of ID number, being the primary key
field of the Companies table, as the basis of the link between the two
tables. So the fields in the Divisions table look something like...
DivisionID
CompanyID
DivisionName

OK, so now you have a form where you enter the Company, and you want
the listbox to show all Divisions for that company. You need to make
the RowSource of the listbox to be a query based on the Divisions
table. This is the idea that John was driving at. It depends how the
Company is being entered on the form. One option would be to use a
combobox, whose RowSource is the Companies table. You can set the
combobox up so that the first column (i.e. the CompanyID) is the bound
column, but the second column (i.e. the Company Name) is what is
displayed in the combobox. If that's the case, make a query based on
the Divisions table, with the DivisionName field and the CompanyID
field, and then in the criteria of the CompanyID field, refer to the
Company combobox on the form, using syntax such as...
[Forms]![NameOfYourForm]![NameOfCompanyCombobox]. Then, you use this
query as the RowSource of the listbox. John's suggestion involved
entering the SQL of this type of query in to the listbox's rowsource,
instead of the name of the query, so using my example you could put:
SELECT Division FROM DivisionsTable WHERE CompanyID =
[Forms]![NameOfYourForm]![NameOfCompanyCombobox]

One more step... you should put code something like this, on the
AfterUpdate event of the Company combobox...
Me.NameOfListbox.Requery

Now, if your setup differs, obviously you will need to adapt, but hope
that might help to move you forward.

- Steve Schapel, Microsoft Access MVP


Thanks John! Okay, so now here is where I sound like a
real dummy...
I don't understand how I specify it for each individual
company. Do I need to make a table for each company's
divisions?
Click to expand...

.
Click to expand...
 
Back
Top