I have a form with a listbox that is based on a query. The query would have 2 columns for every month. I was thinking of only showing those columns for the months that have occurred so far which would mean i would need to have code that would adjust the number of columns and the column width when the form loads based on the month. Would it be better to ask the user how many months they want to see?

Ideas ??

Hi Bob

I'd ask your customer what they want, I had a similar situation and they wanted a 6 month rolling window ... So it depends on what the system is for and what your client expects from it.

hth

let's say for sake of ease they want to see thru the current month and not the months that havent happened yet.

Hi Bob

Not sure what you mean?
To clarify; my client wanted all transactions in the last 6 months, hence for today (23 April 2012) the list box would show transactions for 24 Oct 2011 - 23 Apr 2012 (inclusive).

my listbox shows these from left to right. between the m4d and total would come the rest of the year. I could show the m5a, m5d and so on all the time. OR, I can just show the months that havent happened yet. Why show m8a if it isnt august.


vendor
device type
prior year
m1a
m1d
m2a
m2d
m3a
m3d
m4a
m4d
total

Hi Bob

As I said earlier - I'd ask the customer, the customer is always right, .

I can't pre-guess your customer and they may want something you had not anticipated, which I was trying to illustrate - one of my clients initially wanted just the current month, then a 6 month rolling window (the preceding 6 months) then for the current (financial) year (12 months), then they decided they wanted the flexibilty to choose

hth

Bernie,

I can appreciate what you are saying, however, in this case, I really am looking at how to do it regardless of whether it will be used in the end.


Bob

If I understand correctly, you want a VARIABLE number of columns in the list box, depending on how many months need to be included. Correct?

If so, you'll have to rewrite the SQL for the list box before populating it, depending on which month it is.
Then, you'll have to redefined the columns in the list box, adding columns as the number of months grows.

It's fairly complex VBA, but it can be done.

Aaah, I understand

I personally try to void changing properties like column, or control, widths as I always feel it is confusing when the format of a form changes; I would (probably) maintaining the maxium number of columns that will ever be needed with the column widths remaining static - I don't see any advantage in changing the column numbers and widths, but leaving them there gives a sort of indication of the period of time remaining, the values returned could be null or if the query returns '0's then you could change the list box row source to exclude these non valid columns.

But this is just one person's (subjective) opinion.

Hope that helps.

Bernie, George

Thanks for the input. I was kind of leaning to the "it might not be worth the effort" but wanted other opinions.


Thanks again.

bob

I have done it exactly once. IMO, it's NOT worth the effort.