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 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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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)...
|
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...
|
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?
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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)...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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...
| |