Microsoft Access Forms

[Guest]

I am trying to find an expression that will return multiple values to a drop
down box based on data entered in a previous field of the same form.

Specifically I have a table that lists all counties for a state and each
county is grouped into one of 6 district. When someone types in Northwest I
would like the drop down box to list all counties in the Northwest district.

I tried the DLookUp function but that returns all county values, although it
does start the list with the counties in the specified district.

Thanks!

 

[Jeff Boyce]

Lance

Take a look in Access HELP, at mvps.org/access or via Google.com for
"cascading combo boxes".

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[OfficeDev18 via AccessMonster.com]

In the combobox's row source, make sure there's a WHERE clause that takes the
district into consideration. In the combobox's OnClick event, add the code

Me.ComboBoxName.Requery

so it will refresh the combobox's data and display it properly.

Also, if I were you, I would set the .Enable property of the combobox to No
as long as the district is blank. In the OnCurrent event of your form add the
following code:

Me.ComboBoxName.Enabled = False

In the AfterUpdate event of the district textbox (in other words, the
district is non-blank), add the code

Me.ComboBoxName.Enabled = True

The reason for this is so the combobox will always return meaningful
information.

Hope this helps,

Sam

 

[fredg]

I am trying to find an expression that will return multiple values to a drop
down box based on data entered in a previous field of the same form.

Specifically I have a table that lists all counties for a state and each
county is grouped into one of 6 district. When someone types in Northwest I
would like the drop down box to list all counties in the Northwest district.

I tried the DLookUp function but that returns all county values, although it
does start the list with the counties in the specified district.

Thanks!

Several ways to fill a combo box with the values associated with a
different text box. Here's one:
Leave the row source property to the combo box blank.
Code the AfterUpdate event of the text control:

Me![ComboName].Rowsource = "Select TableName.Counties from TableName
Where Tablename.State = '" & Me![StateControlName] & "' and
TableName.Area = '" & Me![AreaControlName] & "' Order By [Counties];"

The above assumes the name of a field in the table is [Area] and
another is [State] and that they are both Text datatype fields. The
above code should all be on one line.

 

[Graham Mandeno]

Hi Lance

These are called "cascading combo boxes", where the rowsource of a "child"
in the hierarchy is filtered according to the "parent" value.

I expect that your "Counties" table would have (at least) three fields:
State, District, and CountyName.

If you want a list of CountyNames where the State and District match the
state and district already entered on the form, then the combo's rowsource
query needs to look something like this:

SELECT CountyName from Counties
where State=Forms![YourForm]![StateField]
and District=Forms![YourForm]![DistrictField]

When either the state or the district is updated on the form, the combo
needs to be requeried so the list is updated to match the new criteria. To
do this, put this line of code:
[YourComboboxName].Requery
in the AfterUpdate event for both the "parent" controls.

 

[Guest]

I posted a little demo file for this at:

http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23626&webtag=ws-msdevapps

Ken Sheridan
Stafford, England