Populate Form - wondering about a method

[David44Coder]

I want to display some data in an array to an unbound datasheet form.
Usually the data would be in a table and the Form bound to it.
BUT can this be achieved without a table - i.e write to the Form directly as it it were a spreadsheet?
How could you move to each new record or row?

 

[The_Doc_Man]

OK, to the limited extent that I understand the mechanism of a datasheet, it cannot be unbound because it isn't capable of taking data from something that is not based on a recordset. Has to do with the way it is generated. Query is OK, table is OK. Memory array... not so much.

You can manually build a form that maybe would LOOK like a datasheet but would NOT be a datasheet. You could populate it manually, but if you wanted to scroll it, you would have to program the effect of rippling rows up or down, which might look a little ragged.

However, there is always the option to use a temporary table, which you would erase and then fill in, after which you could display it as a legit datasheet form. That temporary table could be scrolled very easily as part of a datasheet.

Stated another way - don't re-invent a perfectly good wheel called the Access datasheet. Turn the array into a recordset and use the scrolling method that already exists. The datasheet pseudo-form requires a table but it doesn't have to be some astounding data structure. It just has to look like a table. Load up the array and be done with it.

 

[June7]

Why do this? Where does the data populating array come from?

 

[David44Coder]

Thanks for the replies and advice taken on board. It was as developing this I suddenly thought why populate a table if I can put the data straight into a form. It was/is just a temporary display.
But couldn't see how to.. then got hung up on is that possible or not.
I've gone with the temp table idea and all working nicely.

 

[Pat Hartman]

So, you are writing code that generates data into an array that will never be saved but which you want to display on a form?

 

[David44Coder]

Yes, if requested that data is shown to the user.

 

[CJ_London]

I want to display some data in an array to an unbound datasheet form.
Usually the data would be in a table and the Form bound to it.
BUT can this be achieved without a table - i.e write to the Form directly as it it were a spreadsheet?

could really do with a clearer explanation of what you want to do

- where does this data come from? created in code? download from web? something else?
- what does it look like - an example would help
- is the data consistent for each column?
- does the data need to be navigable (sort/filter)
- does the data need to be editable?

for a form you could consider using a ADO disconnected recordset instead of an array

Or perhaps use a listbox populated with a value list instead of a form

 

[Pat Hartman]

A Listbox can be loaded using VBA. It doesn't need to be bound. I don't have any example code but you should be able to find some and figure out how to substitute reading an array for reading a table to load the ListBox's RowSource. You can use the Listbox as a substitute form for display.

 

[The_Doc_Man]

There is a limit to loading the listbox with literal string data where each element is separated by commas and each row is separated by semi-colons. There appears to be a limit of 32K characters for the string that defines the content of the listbox. How many characters you can put in a field depends on how you break up the rows and fields.

Size Limitation of Listbox Item-VBForums

Hi, Does anyone know what is the size limitation for each item of the listbox?? OB

www.vbforums.com

I have also found references that suggest that no "standard" listbox row can exceed 255 bytes in width, so you have limits on individual rows.

 

[CJ_London]

which is why we need some idea of the actual requirement.

 

[Uncle Gizmo]

BUT can this be achieved without a table -

How about a disconnected record set?

Disconnecting and Reconnecting the Recordset - ActiveX Data Objects (ADO)

Disconnecting and Reconnecting the Recordset

docs.microsoft.com