Different behavior with list box and combo box

[DocBrown]

I have a question about combo boxes and list boxes on continuous forms. Some
of you on this group will recognize this project....

Can someone tell me why I'm seeing different behavior with combo boxes and
List boxes?

Here's the scoop...

The form is based on Allen Browne's Flexable Search form where the filtered
records are displayed in the detail section. No alteration of the data is
allowed in this form.

The combo box has a query that gathers related records in a one-to-many
relationship and includes a lookup table (FocusList) that translates TypeID
to a string:

SELECT VolunteerFocus.VolunteerID, FocusList.Focus
FROM FocusList INNER JOIN VolunteerFocus ON FocusList.TypeID =
VolunteerFocus.TypeID
WHERE (((VolunteerFocus.VolunteerID)=[Forms]![Search Query
Prompts]![VolunteerID_Box]))
ORDER BY FocusList.Focus;

The idea is the user will click on the combobox and see the related record
data. As you see, the records in FocusList are selected based on the
VolunteerID in the main table.

To make the each row display the corresponding records, there's a requery
event in the Form's OnCurrent Event and VolunteerID_Box AfterUpdate event:
Me.Focus_Box.Requery

For the combo box, this all works. But doing exactly the same for a list box
doesn't. The displayed contents of the list boxes for all the records are the
same and are records retrieved for the VolunteerID of the selected (current)
record. I put the equivalent Requery action in the same places, but it still
doesn't work.

Any ideas why it works for Combo box but not List Box? The relevant
properties are identical, I think.

Thanks,
John S.

 

[Graham Mandeno]

Hi again John

The reason they behave differently is that the combo box is a listbox where
the list is invisible until you hit the dropdown arrow. If you could
somehow hit all the dropdown arrow in all your continuous form records at
the same time, then the lists would indeed be the same.

Did you try the VolunteerFocusList function I suggested?

As this is a read-only form, if you want to see several focus areas on each
field at once you could simulate a listbox with a scrollable textbox.
Change the ", " in the function to vbCrLf, so that the focus areas are on
separate lines, not separated by commas. Then add a textbox that can
accommodate multiple lines with a vertical scrollbar, and bind it to your
calculated FocusList field.

 

[DocBrown]

Hi Graham,

Ok, that makes some sense.. :-s It may also explain (maybe) why the field is
left blank on the non-selected records.

Actually, given that I'm able to correctly populate the combo box with just
a query like the one you suggested, I didn't see why I'd need to create that
function to do it.

As you can tell, I'm still experimenting on the approach I want to use. I
think I'll try your suggestion on a scrollable text box and see if i like
that. Which do you think would be more understandable for someone supporting
this in the future?

You've been a great help. I hope I can return the favor in the future.

John

Hi again John

The reason they behave differently is that the combo box is a listbox where
the list is invisible until you hit the dropdown arrow. If you could
somehow hit all the dropdown arrow in all your continuous form records at
the same time, then the lists would indeed be the same.

Did you try the VolunteerFocusList function I suggested?

As this is a read-only form, if you want to see several focus areas on each
field at once you could simulate a listbox with a scrollable textbox.
Change the ", " in the function to vbCrLf, so that the focus areas are on
separate lines, not separated by commas. Then add a textbox that can
accommodate multiple lines with a vertical scrollbar, and bind it to your
calculated FocusList field.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have a question about combo boxes and list boxes on continuous forms.
Some
of you on this group will recognize this project....

Can someone tell me why I'm seeing different behavior with combo boxes and
List boxes?

Here's the scoop...

The form is based on Allen Browne's Flexable Search form where the
filtered
records are displayed in the detail section. No alteration of the data is
allowed in this form.

The combo box has a query that gathers related records in a one-to-many
relationship and includes a lookup table (FocusList) that translates
TypeID
to a string:

SELECT VolunteerFocus.VolunteerID, FocusList.Focus
FROM FocusList INNER JOIN VolunteerFocus ON FocusList.TypeID =
VolunteerFocus.TypeID
WHERE (((VolunteerFocus.VolunteerID)=[Forms]![Search Query
Prompts]![VolunteerID_Box]))
ORDER BY FocusList.Focus;

The idea is the user will click on the combobox and see the related record
data. As you see, the records in FocusList are selected based on the
VolunteerID in the main table.

To make the each row display the corresponding records, there's a requery
event in the Form's OnCurrent Event and VolunteerID_Box AfterUpdate event:
Me.Focus_Box.Requery

For the combo box, this all works. But doing exactly the same for a list
box
doesn't. The displayed contents of the list boxes for all the records are
the
same and are records retrieved for the VolunteerID of the selected
(current)
record. I put the equivalent Requery action in the same places, but it
still
doesn't work.

Any ideas why it works for Combo box but not List Box? The relevant
properties are identical, I think.

Thanks,
John S.

 

[Graham Mandeno]

Hi John

You *could* populate the list/combo box with a query but, as you have
already observed, the list would display the same members for each record on
your continuous form.

Even if you could persuade the combo to show just the first focus field in
the volunteer's list, I feel that would create a misleading and confusing
user interface, because there is no visual indication whether or not that is
the *only* member in the list.

The method I have suggested would show the focus fields, either as a
scrollable list (the multi-line textbox approach) or as a comma separated
list which expands to a multi-line list on the click of a dropdown (the
textbox plus combo approach). The preferred approach really depends upon
your screen real estate and your aesthetic preference, but both require a
function to aggregate the multiple focus area names into a single string.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

Ok, that makes some sense.. :-s It may also explain (maybe) why the field
is
left blank on the non-selected records.

Actually, given that I'm able to correctly populate the combo box with
just
a query like the one you suggested, I didn't see why I'd need to create
that
function to do it.

As you can tell, I'm still experimenting on the approach I want to use. I
think I'll try your suggestion on a scrollable text box and see if i like
that. Which do you think would be more understandable for someone
supporting
this in the future?

You've been a great help. I hope I can return the favor in the future.

John

Hi again John

The reason they behave differently is that the combo box is a listbox
where
the list is invisible until you hit the dropdown arrow. If you could
somehow hit all the dropdown arrow in all your continuous form records at
the same time, then the lists would indeed be the same.

Did you try the VolunteerFocusList function I suggested?

As this is a read-only form, if you want to see several focus areas on
each
field at once you could simulate a listbox with a scrollable textbox.
Change the ", " in the function to vbCrLf, so that the focus areas are on
separate lines, not separated by commas. Then add a textbox that can
accommodate multiple lines with a vertical scrollbar, and bind it to your
calculated FocusList field.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have a question about combo boxes and list boxes on continuous forms.
Some
of you on this group will recognize this project....

Can someone tell me why I'm seeing different behavior with combo boxes
and
List boxes?

Here's the scoop...

The form is based on Allen Browne's Flexable Search form where the
filtered
records are displayed in the detail section. No alteration of the data
is
allowed in this form.

The combo box has a query that gathers related records in a one-to-many
relationship and includes a lookup table (FocusList) that translates
TypeID
to a string:

SELECT VolunteerFocus.VolunteerID, FocusList.Focus
FROM FocusList INNER JOIN VolunteerFocus ON FocusList.TypeID =
VolunteerFocus.TypeID
WHERE (((VolunteerFocus.VolunteerID)=[Forms]![Search Query
Prompts]![VolunteerID_Box]))
ORDER BY FocusList.Focus;

The idea is the user will click on the combobox and see the related
record
data. As you see, the records in FocusList are selected based on the
VolunteerID in the main table.

To make the each row display the corresponding records, there's a
requery
event in the Form's OnCurrent Event and VolunteerID_Box AfterUpdate
event:
Me.Focus_Box.Requery

For the combo box, this all works. But doing exactly the same for a
list
box
doesn't. The displayed contents of the list boxes for all the records
are
the
same and are records retrieved for the VolunteerID of the selected
(current)
record. I put the equivalent Requery action in the same places, but it
still
doesn't work.

Any ideas why it works for Combo box but not List Box? The relevant
properties are identical, I think.

Thanks,
John S.