populating conditional combo boxes ?

  • Thread starter Thread starter steve
  • Start date Start date
S

steve

Is it possible to have a drop down box within a form which will show values
from 1 table which is dependant on the value of a field in the table it
controls being the same ?

eg: Table 1 = contacts, Table 2 = companies
if contacts.Town = companies.Town
show companies.name companies.Town in combo box

I've looked everywhere within the properties of my combo box, but cannot see
anywhere I can place the SQL for something like this.

My only option is to refer to a query, which currently returns all entries
as it's multi row.

Any advise greatly appreciated.



Steve.
 
Create a combo allowing selection of first parameter. eg.car manufacturer.
Create second combo allowing selection parameter based upon the first one, eg. models made by the manufacturer in 1st combo.
In the Row Source of the second combo, use a query to select teh data, includinh the manufacturer, then in the manufacturer column, in the criteria use the 'build' feature (right click and select Build - to get the syntax right), select Forms>Load forms> the name of the form that you are on, and the manufacturer field. That's it EXCEPT that you need to instruct the second combo to update as a result of the first, so, on the After Update event of the FIRST combo write VBA in the form

me.combo2.requery

That should fix it.
 
This is called synchronized combo boxes. The trick is to base the second
and subsequent combo boxes on a query or a SQL statement where the criteria
is linked to the previous combo box.

Your form is called frmForm1
ComboBox1 is linked to tblContacts
ComboBox2 is linked to a query of tblCompanies where tblCompanies.Town =
Forms!frmForm1.ComboBox1
ComboBox3 is linked to a query of tblEmployees where tblEmployees.CompanyID
= Forms!frmForm1.ComboBox2

Kelvin
 
Is it possible to have a drop down box within a form which will show values
from 1 table which is dependant on the value of a field in the table it
controls being the same ?

eg: Table 1 = contacts, Table 2 = companies
if contacts.Town = companies.Town
show companies.name companies.Town in combo box

I've looked everywhere within the properties of my combo box, but cannot see
anywhere I can place the SQL for something like this.

My only option is to refer to a query, which currently returns all entries
as it's multi row.
Click to expand...

The RowSource property of the combo should be a query retrieving the
records that you want displayed. What's the Rowsource of the combo
now? What's the Recordsource of the form - contacts, or companies?
Just what do you mean by "if contacts.Town = companies.Town" - it
sounds like this limits the combo to one town!
 
Phil,

You'll have to excuse me, I'm new to VBA - but willing to learn.

OK, you got my drift, apart from the fact I don't particularly want to use a
combo box on the first option as that already may exist or will be typed in
on the form as it's a town [would be a huge list if it were national].

The idea of the form recognizing the entry or value and then displaying the
result of a comparison query in the combo box2 is precisely what I'm after.

So, do you feel I need to specify to the form that either the value of form
text box 'Town' is equal to field 'Town' in table2 then display all matches
in table2 in combo box.

Would I have to build that declaration as an expression, macro or code ? And
dependant on which could you advise as to how [if you can spare the time].

Thaks,


Steve.





PhilM said:
Create a combo allowing selection of first parameter. eg.car manufacturer.
Create second combo allowing selection parameter based upon the first one,
Click to expand...
eg. models made by the manufacturer in 1st combo.
In the Row Source of the second combo, use a query to select teh data,
Click to expand...
includinh the manufacturer, then in the manufacturer column, in the criteria
use the 'build' feature (right click and select Build - to get the syntax
right), select Forms>Load forms> the name of the form that you are on, and
the manufacturer field. That's it EXCEPT that you need to instruct the
second combo to update as a result of the first, so, on the After Update
event of the FIRST combo write VBA in the form
 
John,

I am looking to retrieve all entries in companies table which match
contacts.Town

Record source meaning the form's bound table, would be contacts.

I was just thinking that as this is a national db, that if I can query the
companies table by what Town is entered within the contacts form, I can
display all the companies which exist in that town as a combo box option.

Does that make sense?

Thanks again for your interest + help.


Steve.
 
I was just thinking that as this is a national db, that if I can query the
companies table by what Town is entered within the contacts form, I can
display all the companies which exist in that town as a combo box option.
Click to expand...

Well... that's not going to be reliable, at all! A company might have
its headquarters in Houston and a contact working for that company
might be based in Maine; or in Katy TX (adjacent to Houston). I just
can't see the town name as being a reliable link to much of anything!
 
John,

please try to expand your mind just a little in this instance -

The db requires that an entry for a firm specific to the town in which a
client lives be entered. Even if there were more than one branch per town,
they would all appear in the combo list and one could be selected.

The scenario is relatively simple - finding a solution was in fact the
question.


Steve.
 
John,

please try to expand your mind just a little in this instance -

The db requires that an entry for a firm specific to the town in which a
client lives be entered. Even if there were more than one branch per town,
they would all appear in the combo list and one could be selected.

The scenario is relatively simple - finding a solution was in fact the
question.
Click to expand...

My apologies. Shouldn't question your business logic without more
information!

Try the code at http://www.mvps.org/access/forms/frm0028.htm and adapt
it; this will dynamically set the Rowsource for the companies' combo
box in the AfterUpdate event of the control (which need not be a combo
box, though misspelled towns will cause problems!) containing the
contact's town name.

How do you intend to deal with nonunique town names? IIRC every state
in the US has a town named Springfield; might you include the state as
well as the town in the criteria?
 
Back
Top