Updating table

[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

 

[Tim Ferguson]

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.

Well, I was with you up as far as this

I am assuming that (a) the combo boxes are unbound and (b) their rowsources
are something like:-

select distinct State
from places
order by State

select distinct County
from places
where state = forms!myform!cboStatepicker
order by County

select distinct City
from places
where state = forms!myform!cboStatepicker
and county = forms!myform!cboCountypicker
order by City

so it should not matter very much what changes happen in the table as long
as they are requeried every now and then.

Perhaps what you are getting at is that the joined tables make a non-
updatable query, in which case if the combos are bound then you will not be
able to change them. What happens if you try to edit the query records in a
datasheet?

HTH


Tim F

 

[Tom]

Tim:

Again, if I separated the "Country, State, City" query
from the "other table" I can make update in the query.

You suggested that I may have joined them that in an "non-

 

[Tim Ferguson]

You suggested that I may have joined them that in an "non-
updatable" query. How can I find out if that's the case?

Make it into a normal QueryDef and open it in datasheet view; try to edit
one of the fieds and either you will be able to, or the status bar will
warn you that it's not editable.

Often you can make a query editable by including the PK of joined tables
rather than the FK.

B Wishes


Tim F