Requery on bound combo box causes data to disappear on continuous form

[Bill]

Problem:

Combo box data disappears from view when a requery is done

See "Background" below for details on tables, forms & controls

On a form, I want to use the setting of bound combo box C1 to limit
what is displayed in bound combo box C2. When I display Tbl-A records
on a continuous form, and navigate away from record 1 to record 2, the
previously entered data displayed in C2 for record 1 disappears from
view on the form***. Once I'm on record 2, if I pick an item from C1
that matches the item picked for C1 on record 1, then the data in C2 on
record 1 reappears. If, on record 2, I instead pick an item from C1
that does not match the item picked for C1 on record 1, then the data
in C2 on record 1 remains invisible.

***Note: Once I shut down the form and examine the table, I can see
that the data is still present in the table itself, it just disappears
from view on the C2 control on the form as described above.

Background:

1) I'm building a plain-vanilla MDB database (i.e., not a project nor
an access page)

2) Table "Tbl-B" has two fields: B-ID, Autonumber (Primary Key); B-f1,
Text

3) Table "Tbl-C" has three fields: C-ID, Autonumber (Primary Key);
C-f1, Text; B-ID, Number

4) Tbl-B.B-ID is joined to Tbl-C.B-ID in a one-to-many relationship,
i.e., each record in Tbl-B will represent a category (i.e., grouping)
for the records in Tbl-C.

5) Table "Tbl-A" has (among others) three fields: A-ID, f1 and f2.

6) A-ID is an Autonumber and is the primary key

7) Tbl-A.f1 is defined as a "lookup". Tbl-B is the lookup table,.
Tbl-B.B-ID and Tbl-B.B-f1 are retrieved, but Tbl-B.B-ID is hidden.

8) Tbl-A.f2 is defined as a "lookup". Tbl-C is the lookup table,.
Tbl-C.C-ID, Tbl-C.f1, and Tbl.C-B-ID are retrieved, but Tbl-C.C-ID and
Tbl-C.B-ID are hidden.

9) Form-A has a text box bound to Tbl-A.A-ID; a combo box (C1) bound to
Tbl-A.f1; a combo box (C2) bound to Tbl-A.f2. The form's default view
is Continuous Forms

10) C1 Bound Column is 1. C1 row source is the following select
statement

SELECT [tbl-B].[B-ID], [tbl-B].[B-f1] FROM [tbl-B] ORDER BY [B-f1]

11) C2 Bound Column is 1. C2 row source is the following select
statement

SELECT [tbl-C].[C-ID], [tbl-C].[C-f1], [tbl-C].[B-ID] FROM [tbl-C]
WHERE ((([tbl-C].[B-ID])=forms![tbl-a]!f1)) ORDER BY [tbl-C].[C-f1]

12) Code:

Private Sub c1_AfterUpdate()
Me.c2 = Null
Me.c2.Requery
Me.c2 = Me.c2.ItemData(0)

End Sub

Private Sub Form_Current()
Me.c2.Requery
End Sub

Private Sub Form_Load()
If IsNull(c1) Then
Me.c1 = Me.c1.ItemData(0)
Call c1_AfterUpdate
End If

End Sub

[deluxeinformation]

Bill wrote:

Problem:

Combo box data disappears from view when a requery is done

See "Background" below for details on tables, forms & controls

On a form, I want to use the setting of bound combo box C1 to limit
what is displayed in bound combo box C2. When I display Tbl-A records
on a continuous form, and navigate away from record 1 to record 2, the
previously entered data displayed in C2 for record 1 disappears from
view on the form***. Once I'm on record 2, if I pick an item from C1
that matches the item picked for C1 on record 1, then the data in C2 on
record 1 reappears. If, on record 2, I instead pick an item from C1
that does not match the item picked for C1 on record 1, then the data
in C2 on record 1 remains invisible.

***Note: Once I shut down the form and examine the table, I can see
that the data is still present in the table itself, it just disappears
from view on the C2 control on the form as described above.

Background:

1) I'm building a plain-vanilla MDB database (i.e., not a project nor
an access page)

2) Table "Tbl-B" has two fields: B-ID, Autonumber (Primary Key); B-f1,
Text

3) Table "Tbl-C" has three fields: C-ID, Autonumber (Primary Key);
C-f1, Text; B-ID, Number

4) Tbl-B.B-ID is joined to Tbl-C.B-ID in a one-to-many relationship,
i.e., each record in Tbl-B will represent a category (i.e., grouping)
for the records in Tbl-C.

5) Table "Tbl-A" has (among others) three fields: A-ID, f1 and f2.

6) A-ID is an Autonumber and is the primary key

7) Tbl-A.f1 is defined as a "lookup". Tbl-B is the lookup table,.
Tbl-B.B-ID and Tbl-B.B-f1 are retrieved, but Tbl-B.B-ID is hidden.

8) Tbl-A.f2 is defined as a "lookup". Tbl-C is the lookup table,.
Tbl-C.C-ID, Tbl-C.f1, and Tbl.C-B-ID are retrieved, but Tbl-C.C-ID and
Tbl-C.B-ID are hidden.

9) Form-A has a text box bound to Tbl-A.A-ID; a combo box (C1) bound to
Tbl-A.f1; a combo box (C2) bound to Tbl-A.f2. The form's default view
is Continuous Forms

10) C1 Bound Column is 1. C1 row source is the following select
statement

SELECT [tbl-B].[B-ID], [tbl-B].[B-f1] FROM [tbl-B] ORDER BY [B-f1]

11) C2 Bound Column is 1. C2 row source is the following select
statement

SELECT [tbl-C].[C-ID], [tbl-C].[C-f1], [tbl-C].[B-ID] FROM [tbl-C]
WHERE ((([tbl-C].[B-ID])=forms![tbl-a]!f1)) ORDER BY [tbl-C].[C-f1]

12) Code:

Private Sub c1_AfterUpdate()
Me.c2 = Null
Me.c2.Requery
Me.c2 = Me.c2.ItemData(0)

End Sub

Private Sub Form_Current()
Me.c2.Requery
End Sub

Private Sub Form_Load()
If IsNull(c1) Then
Me.c1 = Me.c1.ItemData(0)
Call c1_AfterUpdate
End If

End Sub

The article at the following link explains it quite well and describes
a workaround:

http://www.fmsinc.com/free/newtips/a...ccesstip16.asp

HTH,
Bruce