Listbox from array

[GK in the UK]

If I populate a listbox with an array, does Access save the rows to disk ?

I want to populate a listbox with a small static table, and add two columns to it which are calculated on the fly.
I can create my array from the static table (say 12 rows x 6 columns)
I can create the two columns by adding two fields in the static table which are permanently empty, and populate them in the listbox by calculation ( I think)

I'm just trying to establish if Access will hold the listbox contents in memory, because this listbox will be refreshed constantly and I want to avoid database bloat.

 

[theDBguy]

Hi. Where would the data come from? When you say "static table," you're not referring to an actual table in Access, right? If you're not using an actual TableDef object in Access, then there should be nothing saved on disk, which also means the data disappears when the database closes.

 

[GK in the UK]

The 'static table' is tblVatRates. It rarely changes. So yes it's an actual table (a small one)


When I create and save an invoice, I want to create a 'result matrix' and display it on frmInvoice.

It will look like this (on the form), all nicely presented: (sorry tabs don't work in here)

Vat Rate ID Vat Rate Type Vat Rate Percent Net Value VatValue

1 EC1 0.00 100.00 0.00
2 EC2 0.00 100.00 0.00
E Exempt 0.00 100.00 0.00
R Reduced Rate 5.00 100.00 5.00
S Standard Rate 20.00 100.00 20.00
Z Zero Rate 0.00 100.00 0.00


The first 3 columns are straight from tblVatRates.
Columns 4 and 5 are calculated on the fly.
I want to show them on the form, with the totals.
I would prefer, I think, to just populate an array with tblVatRates, then, calculate columns 4 and 5, then display the result in a listbox. There's nothing to be edited, it's just display.


I don't need to save the results anywhere

I've seen various threads about temp tables and database bloat, this listbox will be refreshed constantly so I don't want it to be writing to the disk all the time.

 

[theDBguy]

Okay, then, I think all that info, or at least the calculated ones, will only be stored in memory. So, I think you're safe.

 

[GK in the UK]

Thank you, I'll code it on that basis, I'm fairly sure I can achieve what I want, I just wanted to be sure about the listbox being held in memory.

 

[theDBguy]

Thank you, I'll code it on that basis, I'm fairly sure I can achieve what I want, I just wanted to be sure about the listbox being held in memory.

Good luck and let us know what you find out. Cheers!

 

[Pat Hartman]

Access caches data so a small table;query that is read multiple times will likely be held in memory. I would bind the listbox to a query and do the calculations in the query.

 

[MajP]

I've seen various threads about temp tables and database bloat, this listbox will be refreshed constantly so I don't want it to be writing to the disk all the time

That is completely apples and oranges and not relevant to what you are doing.

 

[GK in the UK]

OK the array was a red herring I think. I know what my question is in my head but getting the answer in Access speak is more difficult.

So far: dropped any thought of using an array. Figured it out by doing the calculation in the query.

Figured out the SQL with the Select, Sum on two columns, the Inner Join on two tables, the Group By, and the Order By.

Quick question: In the Sum (two fields) I first calculate the Net value.
The other Sum field is the Vat Value which is based on the Net Value.
But, Access complained and said I couldn't have the reference to Net Value in the SQL.
So I had to code another function for Vat Value which calculates the Net Value AGAIN before it returns the Vat Value, and drops that into the SQL.
Is this just the way it is in Access ? Doing stuff twice ?

Anyway I have a working SQL statement that fetches the records I want, groups them and sums them.

Now the listbox. Figured out how to name the listbox column headers (AS [sensible name] in the SQL).
Turns out I can't format the listbox columns into nicely formatted currency columns.


https://photos.app.goo.gl/vy7d5EmYCeDqgKmZ6

So it looks like I'll be embedding a datasheet in my form to show the summary results all nicely formatted.

 

[theDBguy]

Hi. It's not just Access. Although different database engines may do it differently, it all boils down to execution priorities. It's hard to explain, but yes, sometimes, you'll have to repeat the calculation, if you need to refer to it in another calculation. In your case, you might not really need a separate function, but glad to hear you got it working now. Good luck with your project.