combo box filters on continuous form

[Pendragon]

Access03/WinXP

On a subform, I am using two combo boxes for data selection. The first
combo box allows the user to select a Primary category; the AfterUpdate
property sets the RowSource for the second combo box where the user will
select a Secondary category. In the AfterUpdate of the second combo box, I
am using Me.Refresh and Me.Requery.

This works without issue. The problem is that on selection of the Primary
category where any previous records in the continuous form do not match the
Primary category of the current record "lose" the data of the Secondary combo
box. I say "lose" because if I exit the main form and re-open, all of the
data on the subform appears appropriately.

I thought I read somewhere that this was a problem with continuous forms and
using VBA to set rowsources of cascading combo boxes - is this true? Any
ideas on how work around this?

 

[Albert D. Kallal]

Access03/WinXP

On a subform, I am using two combo boxes for data selection. The first
combo box allows the user to select a Primary category; the AfterUpdate
property sets the RowSource for the second combo box where the user will
select a Secondary category. In the AfterUpdate of the second combo box,
I
am using Me.Refresh and Me.Requery.

You should not need refresh, and re-query for the forms data if you "set"
the forms
recordsouce in code. (and, I suggest you do this).

This works without issue. The problem is that on selection of the Primary
category where any previous records in the continuous form do not match
the
Primary category of the current record "lose" the data of the Secondary
combo
box.

You don't mention if records are displayed when the form opens at the
start???

say "lose" because if I exit the main form and re-open, all of the
data on the subform appears appropriately.

If you re-open he form, then how does the form remember the settings of the
two combo boxes????? (or, are they bound?)
I guess you have to explain what records display when you open the form the
2nd time, where are these records coming from???

It sounds to me that you should set the forms reocrdsouce in the after
update event OF BOTH combo boxes, or ALWAYS clear the forms reocrdsouce, AND
ALSO the 2nd combo box when you change the 1st combo...

You could also consider to call a common routine in the after update event
of both combo boxes, and that would set the records based on the settings of
both combos at that given point in time.

egL

sql = sql + combo box cndstions
me.RecordSource = strSql

strSql will of couse be the sql string you build in code. Furthermore, when
you set the forms reocrdSoruce, you don't need a me.Requery, or a
me.Refursth (me.Refesh will actaly force disk writes for pending reocrds,
and I don't think that what you looking for). The mere setting of the
recordSouce will do a requery for you.

I thought I read somewhere that this was a problem with continuous forms
and
using VBA to set rowsources of cascading combo boxes - is this true?

Link please, I never heard anything like this....

I see some problems when you try and set the focus to a control on form when
there is NO records returned as a result of the search, that can be really
nasty.

I often used a from for the controls in which the user selects/enters values
(your combo boxes), and then used a sub-form to display the continues
records, and this does avoid any problems when no records are returned.

Anoter way is:

MySql = strTable
MySql = MySql & " where " & myconds
MySql = MySql & myorder
Me.RecordSource = MySql
If Me.RecordsetClone.RecordCount > 0 Then
BuildSearch = True
GotOne = True
'MsgBox GotOne
Else
Me.txtSLastName.SetFocus
Call MyVisiable(False)
End If

If the resulting records don't return any records, I return the focus to the
search field, you DO NOT want to send focus to a BOUND field when no records
are returned...it causes problems.

I also STRONGLY suggest that you do NOT use queries that have "forms"
expressions bound to the sql, as then you don't have very much control over
the parameters....and especially when you want blank values to display all
records....

 

[Pendragon]

Albert, thanks for the reply. I went through some of the things you noted
and tried some revisions, but I think I'm losing focus and what exactly is
going on.

Here's the set up:

There are two static information tables. ST_PrimaryService has PriCatID
(autonumber/primary key) and PriCatDesc (as Text). This serves as the
Primary Category selection. ST_SecondaryService has SecCatID
(autonumber/primary key), SecCatDesc (as Text), and PriCatID (as Number/Long
Integer). Here, PricatID is a combo box on Table/Query and has a row source
from the table ST_PrimaryService with Bound Column 1, Column Count 2.

On frmCompany I have frmCompanyServices as a subform (named
frmCompanyServicesSub). The subform is based on table CompanyServices which
has CompanyID, PriCatID, SecCatID (all as Long Integer). CompanyID is the
Parent/Child relation.

In frmCompanyServicesSub, the detail section has cboPrimaryCatID whose
control source is PriCatID and has a row source of the following SQL
statement: "SELECT ST_PrimaryService.PriCatID, ST_PrimaryService.PriCatDesc
ORDER BY ST_PrimaryService.PriCatDesc". The AfterUpdate property sets a
string variable
strData = "SELECT ST_SecondaryService.SecCatID,
ST_SecondaryService.SecCatDesc FROM ST_SecondaryService WHERE
(ST_SecondaryService.PriCatID) = " & CatID (where CatID is a Long variable
and has been set as Me.cboPriCatID.column(0) ).

The second combo box in the detail section is cboSecCatID and has a row
source set simply to the table ST_SecondaryService (since the first combo box
initializes a filter SQL statement anyway).

I have removed all "Requery" and "Refresh" statements per your suggestion so
that the above description is the bare bones representation.

What is happening is that on a Company record, a user makes a selection in
the subform in the first combo box for the primary category. The second
combo box is then filtered for those selections based on the selection of the
first combo box - no big deal. In subsequent records, however, if the
primary selection is different from the previously selected records, the
second combo box information on the previously selected records goes blank.

Example:

Primary selections: 1 Fruits, 2 Vegetables (numbers are the primary key)
Secondary selections: Apples (1), Oranges (1), Peaches (1), Peas (2),
Carrots (2)

In a company record, I select Fruits and then Apples on record 1, Fruits and
then Oranges on record 2. If on record 3 I select Vegetables, "Apples" and
"Oranges" go blank on the first two records. Record 3 combo box 2 still
appropriately filters my selection to only the vegetables. I select Peas.
If on record 4 I select Fruits, then "Apples" and "Oranges" reappears on the
first two records while "Peas" disappears.

When I have finished, if I close frmCompany and then re-open frmCompany and
go to ABC Company, all selections are visible. So the information is being
stored appropriately and the form is displaying the records appropriately.
It's just that when a user is actively selecting records, previous selections
in combo box 2 are not visible.

I appreciate your suggestions and assistance. If I need to make changes to
table structure or form design, I am certainly open to doing so.

Thanks!!