473,320 Members | 2,083 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

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

Jun 20 '06 #1
1 4833

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

Jun 20 '06 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: B | last post by:
I know there are several ways to speed up combo boxes and form loading. Most of the solutions leave rowsource of the combo box blank and set the rowsource to a saved query or an SQL with a where...
1
by: D Boyd | last post by:
Im trying to get the name of a bound control on a continuous form in Access. So that I can get the value of any text field. This is what Im looking to do.... The user enters a value for number...
4
by: jcazmail-groups | last post by:
I have a child form that has a combo box whose underlying query needs to be filtered by a value from a combo box on the parent form. I have succeeded in doing this by putting the following SQL in...
3
by: Stewart | last post by:
Hi all! My (relatively small) database holds data on staff members and the projects (services) that they are assigned to. In my form frmStaff, I have a list of staff members - it is a...
20
by: Robert | last post by:
Need some help to stop me going around in circles on this one.... Have a nested subform (subform2) which simulates a continuous form for the record on the parent subform. Subform2 has rows of...
2
by: Pablo | last post by:
I have a Continuous Form based on the following table: sku, description, categoryId, subCategoryId I restrict the initial dataset to include one value for categoryId, but it may include...
2
by: panwala_bhavesh | last post by:
Thanks in advance... I have a form displaying the results of a query in a continuous form style. I want to be able to double-click on the key field of the form (a textbox, in this case a AssetID)...
3
by: wideasleep | last post by:
I have a subform that's a continuous form. In it I have a combo box that fills a date field. It works great except for one thing. It fills in all the other date fields for each record and the combo...
3
by: dmcp | last post by:
hi i have a combo box in continuous form is there a way that i can filter the combo as i write in it?
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.