Listbox Columnwidths causing error

  • Thread starter Thread starter Nicholas Scarpinato
  • Start date Start date
N

Nicholas Scarpinato

I have a listbox that displays daily data for a report. This listbox has one
problem, however. When the data being displayed hits around 50 columns, the
Columnwidths propery errors out saying that the property is too long. I have
a loop that builds a string to set the lengths per column, starting with "2
in;" and appending "1 in;" to the string for each column. Is there a way to
tell the database to make the first column 2 inches and every column
thereafter 1 inch, regardless of how many total columns there are in the data?
 
Nicholas

?You have a listbox with "around 50 columns"?! How can your users read all
that?!

You've described "how" you are trying to do something, but not much about
"why" or "what" that something is.

If you'll describe more about what having "around 50 columns" in a listbox
lets you/your users do, folks here may be able to offer alternate
approaches.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Ok, I guess I could have explained my perdicament a little more in-depth... I
was running short on time when I posted my original question. I'm using a
listbox to display a query, more specifically a crosstab query, which is
basically mimicing a previously existing Excel sheet. The previous reports
were all compiled in a single Excel sheet with 10 tabs, all of which were
pivottables based on the data copied into one of the tabs, which had to be
modified and have four columns added to them to make it all work. What I've
done is take that same import file, import it into Access, allow Access to
make the necessary modifications to the data programmatically so the changes
are done correctly each time, and then display that data in a pivottable-like
form. The reason I'm using listboxes is that with a listbox I have access to
the double-click trigger, which I use to simulate "drilling down" as a user
would in Excel. The problem I'm running into is that this dataset is starting
to cover a rather large timeframe, and for each successive date, I need to
add a new column to the listbox to display it, hence the issue I described
earlier.

If it helps any, I got the basic premise for this idea from this article:
http://www.members.shaw.ca/AlbertKallal/Articles/Grid.htm
 
I suppose I should also mention that this data is A> highly sensitive in
nature (the database I'm building is for the fraud department), B> thousands
of records per day, and C> EXTREMELY slow to process in Excel pivottables. So
in case you're wondering why I'm mimicing the functionality of Excel in an
Access database, that's why. It will be more secure (most of the computers in
our building don't even have MS Access on them, so they're not going to be
poking around in an Access database), more accurate (by using listboxes to
display the data, nobody will be able to change it, plus the imports will be
automated to eliminate human error), and considerably more efficient to
handle the data in Access.
 
Nicholas

If you are trying to mimic Excel functions in Access, it's a little like
trying to drive nails with a chainsaw! You might be able to do it, but...!!

To get the best (and easiest) use of Access' relationally-oriented
features/functions, you can't feed it 'sheet data.

If the terms "normalization" and "relational" are not familiar, plan on
spending some time coming up to speed on them. If you have experience using
Excel, you may have to UNLEARN how you've learned to organize your data in
order to use Access effectively.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
I appreciate the advice Jeff, and believe me, I can't stand it when people
try to transform Access into Excel (or even worse, create an Excel
"database"... ugh...). This is NOT what I'm trying to do. My table structure
is very simple, I have a total of one table, with 12 fields. I run a daily
import process to add data to the table. I use crosstab queries to transform
the data from it's imported form into a counted list, by query criteria, with
each successive day as a new column. Then I sort the crosstab queries in
descending order to check for possible fradulent transactions (i.e., if the
last six digits of a credit card number are 123456, and on 7/25/08 that card
number shows that there were 47 orders placed with that card, it's a good
possibility that someone is using a stolen card). The only time I'm using an
Excel-like approach is in the data display form. Nobody is using this
database to edit any data, it's strictly for research purposes only. The main
reason for the Excel-style look is because it's just plain easier to look at
the data that way because there's so much to go through.

However, I did some research and ended up scrapping the listbox in favor of
a hierarchial flexgrid, which does exactly what I need and works even better
than I had hoped it would. It solved all the problems I was having: I
couldn't freeze columns in the listbox, which I can in the flexgrid; I
couldn't sort the listbox, whereas I have controls to sort the flexgrid on
any field, ascending or descending as needed; The listbox couldn't hold all
the data, the flexgrid can (although the old FlexGrid cannot, it's limited to
around 350,000 cells... the hierachial flexgrid can handle a lot more than
that); And last but not least, the listbox didn't give me any formatting
options, as opposed to a flexgrid where I can basically do whatever I want on
the fly. The flexgrid also gave me the added benefit of being able to run a
query based on a specific cell. Rather than having to run extra queries to
find out the days that had data for a specific search criteria and pick one
of those dates from a listbox to view the transactions for the day, I can now
just double-click a cell in the flexgrid and return the row and column
headers as query criteria and immediately pull exactly the data I want.

Again, thank you both for your help.
 
Back
Top