Divide row between two listboxes

[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

 

[Jeff Boyce]

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

 

[KARL DEWEY]

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?

 

[Peter Stone]

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

 

[John W. Vinson]

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.

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.

 

[Peter Stone]

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.

 

[KARL DEWEY]

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.

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.

 

[John W. Vinson]

Obviously John Vinson is also an MVP in Geography.

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?

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.

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.

 

[Peter Hibbs]

Peter,

If you need to display records horizontally rather than vertically
then a Flex Grid Control will probably do the job but you will need to
write a bit of VBA code. Have a look at my Flex Grid Demo program for
some examples including one that does what you want.

http://www.rogersaccesslibrary.com/...?TID=511&SID=fcz9c6a449za6983eb4422893c7a6f24

HTH

Peter Hibbs.

 

[Peter Stone]

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 Stone]

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.

 

[Peter Stone]

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

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.