continous form combobox

[Ivano via AccessMonster.com]

I have a continuos form with my address: State, city, street. I have used two
comboboxes for state and city. If in the row source of city I put the
condition ID_state = forms!address!ID_state it only shows the cities of the
first state I have in my address table and not the one of the record. can
someone help?

 

[Marshall Barton]

I have a continuos form with my address: State, city, street. I have used two
comboboxes for state and city. If in the row source of city I put the
condition ID_state = forms!address!ID_state it only shows the cities of the
first state I have in my address table and not the one of the record. can
someone help?

The first thing you need to do is requery the city combo box
in both the state combo box's AfterUpdate event AND in the
form's Current event.

After you get this working for any record you are editing,
the display of the city combo box on other records will
probably be your next question. This can get tricky,
involving adding a text box on top of the combo box and
joining the cities table in the form's record source query.
If you need help with this post back with details about the
tables use as the form's record source and the city combo
box's row source.

 

[Ivano via AccessMonster.com]

Forms record source is the table "tbl_address" which contains "ID_State" and
"ID_city". Row source for combobox State is the table "tbl_State", control
name or origin is ID_state. same for cities.

 

[Marshall Barton]

Forms record source is the table "tbl_address" which contains "ID_State" and
"ID_city". Row source for combobox State is the table "tbl_State", control
name or origin is ID_state. same for cities.

That's not enough to go on, but the general idea is like
this. Change the form's record source to a query that joins
the address table to the cities table on the id_city field
and include the city name field. Bind the text box text box
(that's on top of the text portion of the combo box) to the
city name field.

Add a line of code to this text box's GotFocus event:

Me.[city combobox].SetFocus

 

[Ivano via AccessMonster.com]

I already did this, but the point that I do not understand is why the
condition ID_city = me!ID_city refers only to the first record in the table
and not to each single record.

Forms record source is the table "tbl_address" which contains "ID_State" and
"ID_city". Row source for combobox State is the table "tbl_State", control
name or origin is ID_state. same for cities.

That's not enough to go on, but the general idea is like
this. Change the form's record source to a query that joins
the address table to the cities table on the id_city field
and include the city name field. Bind the text box text box
(that's on top of the text portion of the combo box) to the
city name field.

Add a line of code to this text box's GotFocus event:

Me.[city combobox].SetFocus

 

[Marshall Barton]

The key point is that VBA code can not reference an
individual row on a continuous or datasheet form without
**navigating** to the row. When you refer to a control's
value in the detail section, you are refering to the value
of the control IN THE CURRENT RECORD. This means that your
VBA statement is getting the value of ID_City from whatever
record is the current record (the first record when the form
first opens).

Remember that the current record is the one that the user
(or your code) has navigated to. Note that all the rows
except the current record are only screen images, they do
not exist as real controls.

The "extra" text box uses a control source expression that
is based on record source fields. This works because Access
evaluates the control source expressions for each row when
the image of the control is drawn on the screen.
--
Marsh
MVP [MS Access]

I already did this, but the point that I do not understand is why the
condition ID_city = me!ID_city refers only to the first record in the table
and not to each single record.

Forms record source is the table "tbl_address" which contains "ID_State" and
"ID_city". Row source for combobox State is the table "tbl_State", control
name or origin is ID_state. same for cities.

That's not enough to go on, but the general idea is like
this. Change the form's record source to a query that joins
the address table to the cities table on the id_city field
and include the city name field. Bind the text box text box
(that's on top of the text portion of the combo box) to the
city name field.

Add a line of code to this text box's GotFocus event:

Me.[city combobox].SetFocus

 

[Ivano via AccessMonster.com]

thank you, is there anyway to go around this problem?

The key point is that VBA code can not reference an
individual row on a continuous or datasheet form without
**navigating** to the row. When you refer to a control's
value in the detail section, you are refering to the value
of the control IN THE CURRENT RECORD. This means that your
VBA statement is getting the value of ID_City from whatever
record is the current record (the first record when the form
first opens).

Remember that the current record is the one that the user
(or your code) has navigated to. Note that all the rows
except the current record are only screen images, they do
not exist as real controls.

The "extra" text box uses a control source expression that
is based on record source fields. This works because Access
evaluates the control source expressions for each row when
the image of the control is drawn on the screen.

I already did this, but the point that I do not understand is why the
condition ID_city = me!ID_city refers only to the first record in the table

[quoted text clipped - 14 lines]

Me.[city combobox].SetFocus

 

[Marshall Barton]

I don't consider it a problem, it's just the way it is.

Review my first reply for a way to make the form display the
desired values.
--
Marsh
MVP [MS Access]

thank you, is there anyway to go around this problem?

The key point is that VBA code can not reference an
individual row on a continuous or datasheet form without
**navigating** to the row. When you refer to a control's
value in the detail section, you are refering to the value
of the control IN THE CURRENT RECORD. This means that your
VBA statement is getting the value of ID_City from whatever
record is the current record (the first record when the form
first opens).

Remember that the current record is the one that the user
(or your code) has navigated to. Note that all the rows
except the current record are only screen images, they do
not exist as real controls.

The "extra" text box uses a control source expression that
is based on record source fields. This works because Access
evaluates the control source expressions for each row when
the image of the control is drawn on the screen.

I already did this, but the point that I do not understand is why the
condition ID_city = me!ID_city refers only to the first record in the table

[quoted text clipped - 14 lines]

Me.[city combobox].SetFocus

 

[Ivano via AccessMonster.com]

ok that's fine. I'll do what you suggest.
Thank you again it was very helpful.

I don't consider it a problem, it's just the way it is.

Review my first reply for a way to make the form display the
desired values.

thank you, is there anyway to go around this problem?

[quoted text clipped - 21 lines]

Me.[city combobox].SetFocus