Updating tables

[Tom]

I am relatively familiar with Access database desing;
however, I now have a quite fundamental question.

Instead of using a parameter value query, I use a form
that contains combo boxes for selecting values from
tables.

Additinally, there are relationships between the combo
boxes that ensure the selection of the desired subdata.

More specifically, I use strings such as
"[Forms]![frmMoveBilletsAcrossBranches]![Branch]" to
create the relationships. Confused???

Here's some example data:

COUNTRY STATE/PROVINCE CITY
======= ============== ====
USA VA Richmond
USA VA Norfolk
USA VA Alexandria
USA TX Austin
CANADA BC Vancouver
CANADA ON Toronto


On the form, I have 3 combo boxes (country, state, city).

If I were to select
COUNTRY = USA
then STATE = VA or TX

If I then select STATE = VA
then CITY = Richmond, Norfolk, Alexandria


In other words, I simply don't want to show all states,
cities in the drop-down menus.

Are you with me so far???


******* Okay, here's the problem now?

Currently, all data is being pulled from one table. Once
I however join the table (containing country, state, and
city) with another table, I cannot change the values in
my
combo boxes anymore in order to update the "country,
state, city" table any longer.

Why is that? [It works fine as long as all info is in a
single table]


Any feedback is appreciated!


Tom

 

[Michel Walsh]

Hi,


We generally change the list of a combo box by changing its rowSource property. I do not see why
your VBA code cannot change the Combo Box RowSource property...


Me.ComboBoxControlName.RowSource = "SELECT ... WHERE ... "


and I suggest you place it in the GotFocus event.



Hoping it may help,
Vanderghast, Access MVP

 

[Tom]

Michel:

Thanks for your feedback... I will definitely try to
implement your suggestions once back at work on Monday.

I will let you know (via this forum) if it worked or not.

Thanks again,
Tom

-----Original Message-----
Hi,


If you do not use the same table as the one in the

design mode, you have to change the RowSource

property of the combo box. The RowSource property hold

an SQL statement: SELECT something FROM

someTable WHERE ...

So, I suggest:

You debug stop at the line Echo True ( or better, with VBA6, add a line of code:
Debug.Assert False
, just after the echo True, and you can remove the

Debug.Assert,or comment it, when done with

the debugging process).

You type, in the immediate debug window

? Level2A.RowSource

You select the output (Ctrl-C), you open the query

designer, in SQL view, and you paste the

statement, Ctrl-V. That statement should then work and

deliver the data in data view (from the

querydesigner). If not, you may work in the query

designer as long as it finally works, cut and

paste back the SQL statement in the RowSource. Same for

the third combo box, if required.

Hoping it may help,
Vanderghast, Access MVP

Michel:

This is the OnChange [EventProcedure] as I currently have
it in the VBA code:


Private Sub Level1A_Change()
Dim lev1a As ComboBox, lev2a As ComboBox, lev3a As
ComboBox
Set lev1a = Forms![frmUpdateInfo].[Level1A]
Set lev2a = Forms![frmUpdateInfo].[Level2A]
Set lev3a = Forms![frmUpdateInfo].[Level3A]
Echo False

lev2a.Requery
Me.Level2A = Me.Level2A.ItemData(0)

lev3a.Requery
Me.Level3A = Me.Level3A.ItemData(0)

Echo True
End Sub
Private Sub Level2A_Change()
Dim lev2a As ComboBox, lev3a As ComboBox
Set lev2a = Forms![frmUpdateInfo].[Level2A]
Set lev3a = Forms![frmUpdateInfo].[Level3A]
Echo False

lev3a.Requery
Me.Level3A = Me.Level3A.ItemData(0)

Echo True
End Sub


... while Level1A is the equivalent to "Country"
and Level2A = State
and Level3A = City

in the data example.


Again, it works fine as long as these 3 data elements are
in one and the same table (which they are). However, if
I use another field (let's say NAME) that is stored in a
different table, I cannot select/change the value in
the "Country, State, City" table any longer.

Let me know if it helps to forward an example database.
Thanks, I appreciate your help on this.

Tom

-----Original Message-----
Hi,


We generally change the list of a combo box by

changing
its rowSource property. I do not see why

your VBA code cannot change the Combo Box RowSource property...


Me.ComboBoxControlName.RowSource = "SELECT ... WHERE ... "


and I suggest you place it in the GotFocus event.



Hoping it may help,
Vanderghast, Access MVP


I am relatively familiar with Access database desing;
however, I now have a quite fundamental question.

Instead of using a parameter value query, I use a form
that contains combo boxes for selecting values from
tables.

Additinally, there are relationships between the combo
boxes that ensure the selection of the desired subdata.

More specifically, I use strings such as
"[Forms]![frmMoveBilletsAcrossBranches]![Branch]" to
create the relationships. Confused???

Here's some example data:

COUNTRY STATE/PROVINCE CITY
======= ============== ====
USA VA Richmond
USA VA Norfolk
USA VA Alexandria
USA TX Austin
CANADA BC Vancouver
CANADA ON Toronto


On the form, I have 3 combo boxes (country, state, city).

If I were to select
COUNTRY = USA
then STATE = VA or TX

If I then select STATE = VA
then CITY = Richmond, Norfolk, Alexandria


In other words, I simply don't want to show all states,
cities in the drop-down menus.

Are you with me so far???


******* Okay, here's the problem now?

Currently, all data is being pulled from one table. Once
I however join the table (containing country, state, and
city) with another table, I cannot change the values in
my
combo boxes anymore in order to update the "country,
state, city" table any longer.

Why is that? [It works fine as long as all info is

in
a

single table]


Any feedback is appreciated!


Tom



.

.