Addressing array built in sub cmdSched_Click

[EarlCPhillips]

I am a newbie to VBA, but not coding. I volunteer coding at the local food
bank.
They use a database to schedule volunteers in to sort the food collected.
User wants to forcast how many vols will be here at a given hour or
half-hour on a
given day. DB record contains date, start time, end time and number
scheduled. I
have successfully programmed it using CurrentProject.Connection, SQL code
and ADO to call records,and placed it in a 25 by 60 array. Now I must display
array contents. Main form is frmMain, command button is cmdProjSched which
calls event procedure cmdProjSched_Click. Within sub procedure I have filled
the array and have it ready to print. Cannot seem to get output form named
frmSchedVol to address the array. Using output field properties, I think I
need to populate the Record Source with the address of the array, but I don't
know how to properly address it. Any help would be appreciated. Help feed
the hungry.

 

[John Nurick]

As far as I know you can't bind an Access form to a VBA array. Perhaps
the "database" way to do what you want is to dump the array and use a
temporary table with 25 fields (in which you create 60 records). You can
then bind the form to the table.

Alternatively, you could use a grid control or - for display only -
maybe a listbox (though I'm not sure whether one could handle that much
data).

 

[EarlCPhillips]

I have not tried a grid control or list box. I will try that and see if it
works. Thanks for the suggestion.

 

[EarlCPhillips]

Had same problems with grid control and list box, e.g. addressing. Finally
wrote resultant array to a temporary table and am now trying to get a form to
display this temporary table. I expect to try to delete all temp-table
records when the form closes. However, I am having trouble launching form
with

strDocName = "frmSchedVol"
DoCmd.OpenForm strDocName, acPreview

Can anyone help with why the DoCmd will not open the form?

 

[John Nurick]

Use acNormal if you want to open the form for normal use.

To delete the records in the temp table, use a delete query, e.g.

CurrentDB.Execute "DELETE FROM TempTable;", dbFailOnError

 

[EarlCPhillips]

The acNormal worked the way you said. I had already used the following code:

SQLStmt = "DELETE * FROM tblScheduled WHERE dtmDate > (Now()-10)"
CurrentProject.Connection.Execute SQLStmt

as all records are current-date onward, but added your suggested ",
dbFailOnError".
To recap, I read all activity schedule records from today onward, add up the
scheduled volunteers present for a given time on a given day, accumulate each
value in an array, then generate a table from the array, publish a form from
the table and use the Close method on the form to delete all records from the
table as shown in the code above.

Everything now works as the user wants and I will install it and be off to
the next project. This is a big help in scheduling volunteers. Today is
Volunteer Appreciation Day and I will mention that you helped feed the
hungry. Thanks.

 

[John Nurick]

Hi Earl,

Thank you for the kind words.

If you're working with ADO, don't pass dbFailOnError to
Connection.Execute: it's only relevant to the DAO Database.Execute
method.