Requery combo box in subform

[gaugust]

I am having a problem with requerying combo box controls on my subform. In
the after update event for the subform I have the following code to requery
one of the combo boxes:

Me![icudx2].Requery

What is happening is that the selection made in the combo boxes for record 1
is being cleared when I select the combo box for record two.

In Access 2003 I have a subform that is linked to the main form with a
linked field called studyid. The source for the main form is tblsection3 and
the source for the subform is tblDiagnosis. There is a one to many
relationship between the tables with referencial integrity not checked (also
have tried this with referencial integrity checked). On the subform I have
three combo boxes called idcux, idcux2 and idcux3. The source for combo box
idcux is a table called Diagnosis. The source for combo box idcux2 is a query
on table Diagnosis2 that is dependent on the value in combo box idcux. The
source for idcux3 is a query on table Diagnosis3 that is dependent on the
value in combo box idcux2.

I would expect that the after update event would only requery the combo
boxes for the current record and would not clear the prior record's
selections. How can I get Access to only requery the current record's combo
boxes?

 

[Jeanette Cunningham]

Hi,
sounds as if some or all of the combo boxes are bound to the table fields.
Every time you make a selection in the combo, Access edits the data in the
table. Unbind those combos.
If you clear the data source for each combo box (note the data source is the
table field that the combo box sends its data too, not the row source) they
will not be bound to the table.

Jeanette Cunningham

 

[Marshall Barton]

I am having a problem with requerying combo box controls on my subform. In
the after update event for the subform I have the following code to requery
one of the combo boxes:

Me![icudx2].Requery

What is happening is that the selection made in the combo boxes for record 1
is being cleared when I select the combo box for record two.

In Access 2003 I have a subform that is linked to the main form with a
linked field called studyid. The source for the main form is tblsection3 and
the source for the subform is tblDiagnosis. There is a one to many
relationship between the tables with referencial integrity not checked (also
have tried this with referencial integrity checked). On the subform I have
three combo boxes called idcux, idcux2 and idcux3. The source for combo box
idcux is a table called Diagnosis. The source for combo box idcux2 is a query
on table Diagnosis2 that is dependent on the value in combo box idcux. The
source for idcux3 is a query on table Diagnosis3 that is dependent on the
value in combo box idcux2.

I would expect that the after update event would only requery the combo
boxes for the current record and would not clear the prior record's
selections. How can I get Access to only requery the current record's combo
boxes?

You can't. There is only one (current) record in a
datasheet or continuous form. All the others are just
images until some other record becomes current (and some
control on that row gets the ficus).

To get the dependent combo boxes on all the rows to display
their value, you need to over;ay a text box over the text
protion of the combo box. The form's record source query
also needs to join to the combo box's row source table so
the display value field can be bound to the text box. A
small amount of code is needed to shift the focus from the
text box to the underlying combo box.

If you think that sounds messy, you're right. Are you sure
you really need to see the values in the other rows?

 

[gaugust]

It is not necessary to see all the records at once. The user can scroll one
record at a time. However what is happening is that the values in the combo
boxes for the prior record is being blanked out when the requery code is
executed in the after update event when the combo box on the current record
is updated. I need to keep the values that the user entered for all records.
Would you have an example or sample code of the scenerio you described?
Thanks for your reply Marsh.

I am having a problem with requerying combo box controls on my subform. In
the after update event for the subform I have the following code to requery
one of the combo boxes:

Me![icudx2].Requery

What is happening is that the selection made in the combo boxes for record 1
is being cleared when I select the combo box for record two.

In Access 2003 I have a subform that is linked to the main form with a
linked field called studyid. The source for the main form is tblsection3 and
the source for the subform is tblDiagnosis. There is a one to many
relationship between the tables with referencial integrity not checked (also
have tried this with referencial integrity checked). On the subform I have
three combo boxes called idcux, idcux2 and idcux3. The source for combo box
idcux is a table called Diagnosis. The source for combo box idcux2 is a query
on table Diagnosis2 that is dependent on the value in combo box idcux. The
source for idcux3 is a query on table Diagnosis3 that is dependent on the
value in combo box idcux2.

I would expect that the after update event would only requery the combo
boxes for the current record and would not clear the prior record's
selections. How can I get Access to only requery the current record's combo
boxes?

You can't. There is only one (current) record in a
datasheet or continuous form. All the others are just
images until some other record becomes current (and some
control on that row gets the ficus).

To get the dependent combo boxes on all the rows to display
their value, you need to over;ay a text box over the text
protion of the combo box. The form's record source query
also needs to join to the combo box's row source table so
the display value field can be bound to the text box. A
small amount of code is needed to shift the focus from the
text box to the underlying combo box.

If you think that sounds messy, you're right. Are you sure
you really need to see the values in the other rows?

 

[Marshall Barton]

If the subform is displayed in Single Form view, then it is
a simple issue. You just need to requery the dependent
combo boxes in the subform's Current event.
--
Marsh
MVP [MS Access]

It is not necessary to see all the records at once. The user can scroll one
record at a time. However what is happening is that the values in the combo
boxes for the prior record is being blanked out when the requery code is
executed in the after update event when the combo box on the current record
is updated. I need to keep the values that the user entered for all records.
Would you have an example or sample code of the scenerio you described?

I am having a problem with requerying combo box controls on my subform. In
the after update event for the subform I have the following code to requery
one of the combo boxes:

Me![icudx2].Requery

What is happening is that the selection made in the combo boxes for record 1
is being cleared when I select the combo box for record two.

In Access 2003 I have a subform that is linked to the main form with a
linked field called studyid. The source for the main form is tblsection3 and
the source for the subform is tblDiagnosis. There is a one to many
relationship between the tables with referencial integrity not checked (also
have tried this with referencial integrity checked). On the subform I have
three combo boxes called idcux, idcux2 and idcux3. The source for combo box
idcux is a table called Diagnosis. The source for combo box idcux2 is a query
on table Diagnosis2 that is dependent on the value in combo box idcux. The
source for idcux3 is a query on table Diagnosis3 that is dependent on the
value in combo box idcux2.

I would expect that the after update event would only requery the combo
boxes for the current record and would not clear the prior record's
selections. How can I get Access to only requery the current record's combo
boxes?

You can't. There is only one (current) record in a
datasheet or continuous form. All the others are just
images until some other record becomes current (and some
control on that row gets the ficus).

To get the dependent combo boxes on all the rows to display
their value, you need to over;ay a text box over the text
protion of the combo box. The form's record source query
also needs to join to the combo box's row source table so
the display value field can be bound to the text box. A
small amount of code is needed to shift the focus from the
text box to the underlying combo box.

If you think that sounds messy, you're right. Are you sure
you really need to see the values in the other rows?

 

[gaugust]

That worked, Thanks

If the subform is displayed in Single Form view, then it is
a simple issue. You just need to requery the dependent
combo boxes in the subform's Current event.
--
Marsh
MVP [MS Access]

It is not necessary to see all the records at once. The user can scroll one
record at a time. However what is happening is that the values in the combo
boxes for the prior record is being blanked out when the requery code is
executed in the after update event when the combo box on the current record
is updated. I need to keep the values that the user entered for all records.
Would you have an example or sample code of the scenerio you described?

gaugust wrote:

I am having a problem with requerying combo box controls on my subform. In
the after update event for the subform I have the following code to requery
one of the combo boxes:

Me![icudx2].Requery

What is happening is that the selection made in the combo boxes for record 1
is being cleared when I select the combo box for record two.

In Access 2003 I have a subform that is linked to the main form with a
linked field called studyid. The source for the main form is tblsection3 and
the source for the subform is tblDiagnosis. There is a one to many
relationship between the tables with referencial integrity not checked (also
have tried this with referencial integrity checked). On the subform I have
three combo boxes called idcux, idcux2 and idcux3. The source for combo box
idcux is a table called Diagnosis. The source for combo box idcux2 is a query
on table Diagnosis2 that is dependent on the value in combo box idcux. The
source for idcux3 is a query on table Diagnosis3 that is dependent on the
value in combo box idcux2.

I would expect that the after update event would only requery the combo
boxes for the current record and would not clear the prior record's
selections. How can I get Access to only requery the current record's combo
boxes?


You can't. There is only one (current) record in a
datasheet or continuous form. All the others are just
images until some other record becomes current (and some
control on that row gets the ficus).

To get the dependent combo boxes on all the rows to display
their value, you need to over;ay a text box over the text
protion of the combo box. The form's record source query
also needs to join to the combo box's row source table so
the display value field can be bound to the text box. A
small amount of code is needed to shift the focus from the
text box to the underlying combo box.

If you think that sounds messy, you're right. Are you sure
you really need to see the values in the other rows?