Bill wrote:[color=blue]
> 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[/color]
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
