Form with 2 combo Boxes (Criteria for query)

[WayneF]

Hi all,

I have made a form using too combo boxes (I am using the form to execute
a query and these combo boxes are used in the criteria.)

Box A has a Drop down list of Countries
Box B has a Drop down list of Cities

They way I would like it to work is this ...

User selects the Country, then I would like to have the cities limited
to only those that are in the country selected in the first dropdown list.

Will I need some code for this or is there a way to make this happen
using standard access features?

I am a newbie in the world of access but I have this Great big book from
Virginia Anderson that is helping me wade through this new territory

Thanks for any help you can give

Waynef

 

[Carl Rapson]

Hi all,

I have made a form using too combo boxes (I am using the form to
execute a query and these combo boxes are used in the criteria.)

Box A has a Drop down list of Countries
Box B has a Drop down list of Cities

They way I would like it to work is this ...

User selects the Country, then I would like to have the cities limited
to only those that are in the country selected in the first dropdown list.

Will I need some code for this or is there a way to make this happen
using standard access features?

I am a newbie in the world of access but I have this Great big book
from Virginia Anderson that is helping me wade through this new
territory

Thanks for any help you can give

Waynef

You will need to put some VBA code in the AfterUpdate event of the Country
combo box to modify the Cities combo box's RowSource property to incorporate
the value from the Country combo box:

Dim strSQL As String
If Not IsNull(cboCountries) Then
strSQL = "SELECT [City] FROM [tblCities] WHERE [Country] = '" &
cboCountries & "'"
cboCities.RowSource = strSQL
Else
' Do whatever you want here if the Country combo box is empty
End If

Of course, be sure to use your own table, field, and control names.

Carl Rapson

 

[Michel Walsh]

That is more a question about FORMS than about QUERIES, but I can answer
none the less


In the got focus event procedure of BoxB, type something like:



Dim str AS String

if 0=len(BoxA.Value & "" ) then
str="SELECT city FROM tableNameHere"
else
str="SELECT city FROM tableNameHere WHERE
country=FORMS!formName!BoxA"
end if

if Me.BoxB.RowSource<> str then
Me.BoxB.RowSource = str
end if




So, if BoxA has nothing in it, the variable str holds a command to select
all cities. If BoxA has a country (name?) selected, str holds a command to
select only cities of that country (name) will be listed. Next, if the
RowSource property, of BoxB, is different than what str hold, we requery the
drop down list of BoxB.

Note that I assume that the BoxA display an information compatible with the
field country. If you use a lookup, that is probably NOT the case. You
probably need a number, rather than a name. In that case, have the number in
a column of BoxA, even if invisible, that is ok. Say it is the first column.
then, use:

str="SELECT city FROM tableNameHere WHERE country=" &
FORMS!formName!BoxA.Column(0)

instead of

str="SELECT city FROM tableNameHere WHERE country=FORMS!formName!BoxA"




Hoping it may help,
Vanderghast, Access MVP

 

[WayneF]

Hi all,

Thanks for you inputs and sorry I didn't choose the right newsgroup but
I thought the problem would be query related,

When using the wizard to create the control box and I found out it
creates a query in the background for the combo box. I just edited that
query that is in the Combo box properties under "Row Source"and I was able
to use the SQL rather then the VB.

This seems to work fine the first time I run it but then the combo boxes
won't update and re-run the query.
I use a button to execute the query and in the event procedure for this
button I entered the following code ...

Private Sub Display_Company_Reports_LostFocus()
Me!Country.Requery
Me!City.Requery
End Sub

I have a feeling this is not the good way to do it, My guess is I am
going to need some kind of close If "query" is open then close query,
refresh combo boxes, execute query...

Listen thanks everyone for the help, I you don't have any ideas for this
I will re-post in the Forms newsgroup in a few hours.

Thanks again,

Waynef

 

[WayneF]

Ok, at the start of the code for my button i put this line ...

DoCmd.Close acQuery, "title", acSaveNo

Now when I press the button the first thing it does is close any query
already running and executes the new query )))

Thanks all