[libreoffice-users] Re: Auto Filling Drop Down Boxes

Am 21.09.2011 19:00, stripedtomato wrote:

> I am moving my db from Access to LibreOffice, so I know Access much better,
> but I was never able to figure this out in Access either.  If you happen to
> know the answer in either Access or Base I should be able to figure it out
> from there.

All this is impossible to explain in terms of software "features". 
Having an "official" list of unique ZIP codes and city names and your 
own address list with a ZIP field, you create a list box for the ZIP 
code with source type "SQL" and source

SELECT "ZIP" ||'-'||"PONAME"||'-'||"State" AS "Visible", "ZIP" FROM 
"tblZIPCODES" ORDER BY "Visible"

This fills a list box with visible values:
00210-PORTSMOUTH-New Hampshire
00211-PORTSMOUTH-New Hampshire
00212-PORTSMOUTH-New Hampshire
00213-PORTSMOUTH-New Hampshire
00214-PORTSMOUTH-New Hampshire
00215-PORTSMOUTH-New Hampshire
00501-HOLTSVILLE-New York
00544-HOLTSVILLE-New York
00601-ADJUNTAS-Puerto Rico
00602-AGUADA-Puerto Rico
00603-AGUADILLA-Puerto Rico
00604-AGUADILLA-Puerto Rico
00605-AGUADILLA-Puerto Rico
00606-MARICAO-Puerto Rico
00610-ANASCO-Puerto Rico

and the zip codes in a hidden second field.

Now you type the ZIP code into the focussed list box until you get the 
right city. The second field puts the ZIP code only into the field. 
Since each zip code identifies exactly one location in one state, you 
have a reference pointer to the city names and states in the other 
table. No need to put any more information into the form

A report or mail merge uses a simple cross-table query
SELECT D."this",D."that",D.ZIP,Z."CityName",Z."State"
FROM "Data" AS D, ZIPCODES AS Z
WHERE D.ZIP = Z.ZIP



--
For unsubscribe instructions e-mail to: users+help@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted