Divide row between two listboxes

  • Thread starter Thread starter Peter Stone
  • Start date Start date
P

Peter Stone

XP, Access 2003

I have a list box to display data, but it's rather long. How can I divide
the query so that the first record appears in listbox1, the second in
listbox2, etc.?

The records are sorted alphabetically.

Thanks

Peter
 
Peter

Won't two listboxes take up more screen space than one wide one? (I assume
you mean 'wide', not 'tall').

Won't having to check in two places make for more work for the users?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
You say you have a rather long listbox. Then why do you need first record in
listbox and then 2nd record in the next? Post example of data.

The records are sorted alphabetically. Is there any thing else in the
records to determine 1st and 2nd?
 
It's purely for visual reasons.

The list box shows Mainland sovereign states by Continent.

The form is used for planning and information purposes and has various list
boxes that display various types of divisions of the geographical data. For
some continents the list box drops of the bottom of the screen which means
either a scrolling list box or a scrolling form.

The information is easier to analyze if users can see all rows at once. This
can be achieved by displaying alternate rows in side-by-side list boxes.

Here is the query:
SELECT tblDest.DestID, tblNm.Nm, tjnDestPtf.PtfDestID
FROM ((tblDest INNER JOIN (tblNm INNER JOIN tjnDestNm ON tblNm.NmID =
tjnDestNm.NmID) ON tblDest.DestID = tjnDestNm.DestID) INNER JOIN tjnDestGeo
ON tblDest.DestID = tjnDestGeo.DestID) INNER JOIN tjnDestPtf ON
tblDest.DestID = tjnDestPtf.DestID
WHERE (((tjnDestGeo.CatID) In (500,506)))
GROUP BY tblDest.DestID, tblNm.Nm, tjnDestPtf.PtfDestID, tblNm.NmCatID
HAVING (((tjnDestPtf.PtfDestID)=[Forms]![frmContDiv]![cboCont]) AND
((tblNm.NmCatID)=1) AND ((Count(*))=2))
ORDER BY tblNm.Nm;

Thanks
 
The information is easier to analyze if users can see all rows at once. This
can be achieved by displaying alternate rows in side-by-side list boxes.
Click to expand...

I'm not sure I understand. How is it useful to have two interleaved lists,

Antigua Argentina
Bahamas Barbados
Belize Bermuda
Bolivia Brazil

rather than (if you really hate scrolling) two lists, Antigua through Jamaica
and Mexico through Venezuela?

It makes the queries much more complex.
 
Obviously John Vinson is also an MVP in Geography.

Two lists is acceptable if they are even lengths. How do I split them please?

FYI:the form doesn't look like a conventional form. The various list boxes
have the same background as the form and are locked so they can't be clicked.
My goal is to make the information attractive and readable.

P.S. I'm in Antigua but a different one than you're thinking of.
 
Was john's depiction of data display what you are wanting?

You did not answer -- Is there any thing else in the records to determine
1st and 2nd?

You can put the table in the query design grid twice (Access adds a sufix to
the 2nd instance of '_1' to distinguish them.

If there any thing else in the records to determine 1st and 2nd then you can
pull 1st record from first table and 2nd record from the second table.

It takes a little jocking so as not to have Cartesian effect.

--
Build a little, test a little.


Peter Stone said:
Obviously John Vinson is also an MVP in Geography.

Two lists is acceptable if they are even lengths. How do I split them please?

FYI:the form doesn't look like a conventional form. The various list boxes
have the same background as the form and are locked so they can't be clicked.
My goal is to make the information attractive and readable.

P.S. I'm in Antigua but a different one than you're thinking of.
Click to expand...
 
Obviously John Vinson is also an MVP in Geography.
Click to expand...

nah, just looked at the International section of the phonebook to refresh my
memory.
Two lists is acceptable if they are even lengths. How do I split them please?
Click to expand...

You could base each Listbox on a query. Since the number of countries per
continent doesn't change all that often, I'd just hardcode the number of
countries in each to give you the right number: e.g.

SELECT TOP 11 CountryName
FROM Countries
WHERE Continent="South America"
ORDER BY Countryname;

For the second set, use a subquery to eliminiate the ones you've seen already:

SELECT CountryName
FROM Countries
WHERE Continent = "South America"
WHERE CountryName NOT IN
(SELECT TOP 11 CountryName
FROM Countries
WHERE Continent="South America"
ORDER BY Countryname)
ORDER BY Countryname;
FYI:the form doesn't look like a conventional form. The various list boxes
have the same background as the form and are locked so they can't be clicked.
My goal is to make the information attractive and readable.
Click to expand...

INteresting. I wonder if a Report might be preferable - it lets you do snaking
lists, whereas a Form doesn't; and if you're not using this form for editing
(it seems) a report in Preview mode might be easier.
 
Thank you John.

You've hit the nail on the head. The number of countries changes rarely.
I've just tried a variation on this and it works.

You've also opened my eyes to using reports. Tunnel vision on my part. I've
never used a report. I've done so much work on this project using forms
(which are for creating things) that I never thought of reports (which are
for displaying things).
 
Peter
I'm currently playing with the world of options on the Flex Grid Control.
Fascinating--I never knew this existed. It solves various problems for me. I
currently have more than sixty forms and many switchboards, I've been trying
to find a way to colour some rows, etc., etc.

Thank you.

Peter S.
 
Thanks Karl
Sorry I didn't answer your question. The order is solely alphabetical and
there's not anything else to determine the order. I went with John's
solution, but for self education, I'm currently playing with a query that
pulls the fields twice as per your idea. Different ways of looking at the
same problem.

Peter

KARL DEWEY said:
Was john's depiction of data display what you are wanting?

You did not answer -- Is there any thing else in the records to determine
1st and 2nd?

You can put the table in the query design grid twice (Access adds a sufix to
the 2nd instance of '_1' to distinguish them.

If there any thing else in the records to determine 1st and 2nd then you can
pull 1st record from first table and 2nd record from the second table.

It takes a little jocking so as not to have Cartesian effect.
Click to expand...
 
Back
Top