Array = [table]![Column] - How?

[Jemmo]

I have been reading through the MS MSDN pages on arrays, but it doesnt tell me how to set the contents of an existing table to be an array.

Can anyone help?

 

[namliam]

Why would you want that table to be an array?? That just plain doesnt make sence??!!

That is what you have tables for to not have to use bloody arrays.

 

[Jemmo]

I want to use a For Each...Next statement, but these can only be used on Arrays or Collections.

Everything I have tried so far is not looping as I am expecting, or filtering down and returning the correct results. So I am trying a different approach.

 

[chergh]

If you really want to use a for each next statement then create a recordset of the table and then use the getrows method to create your array.

set rs = currentdb.openrecordset("select * from table")
array = rs.getrows

 

[DJkarl]

I want to use a For Each...Next statement, but these can only be used on Arrays or Collections.

Everything I have tried so far is not looping as I am expecting, or filtering down and returning the correct results. So I am trying a different approach.

Says who? I use For Each Next statements with recordsets based on tables.

Can you paste some of your code and explain what you want it to do and what it is actually doing?

 

[namliam]

What is wrong with
Do while

Loop

and loop thru a table?? instead of an Array?

 

[Jemmo]

Says who?

Microsoft Access VBA Orogramming Second Edition (for the absolute beginner)

Page 293

'For Each Loops
VBA provides a looping structure specifically designed for iterating through members in a collection or an array.'

If you are saying this is not the case then I will try it on my recordset and see if I can get that to work.

With regard to what I am doing (or rather trying to do), I need to look at a list of SupplierId's.
Get the 1st SuipplierId and use this to filter data from another recordset (table).
Once the relevant data is obtained, it is written to a spreadsheet using the SupplierId and date as the spreadsheet name so that each one is unique.
Once the spreadsheet has been writen, it looks at the next SupplierId and repaets the process of filtering out data and creating the spreadsheet. And so on.

 

[Jemmo]

What is wrong with
Do while

Loop

and loop thru a table??

I can't get the damned thing to work.
It will loop but not step on the the next SupplierId.
(And I cant getthe filtering working either)

 

[namliam]

Dim rs as dao.recordset
set rs = currentdb.openrecordset ( "Select * from yourtable") '< Add where and order by if needed.
Do while not rs.eof

' ... more code here.

    rs.movenext
loop

 

[Jemmo]

I have;

DimCupidrst as DAO.Recordset
Dim strSql1 as String
Dim strCupid as Variant

strSql1 = "SELECT DISTINCT Cupid FROM table etc"

Set Cupidrst = CurrentDb.OpenRecordset(strSql1, dbOpensnapshopt, dbReadOnly)

strCupid = [Cupidrst]![Cupid]

For Each strCupid in Cupidrst

more code

and when I run the code I get 'Operation is not supported for this tytpe of object'.

Debug highlights the 'For Each strCupid in Cupidrst' line of code.

 

[namliam]

Like I said above:

Dim Cupidrst as DAO.Recordset
Dim strSql1 as String
Dim strCupid as Variant

strSql1 = "SELECT DISTINCT Cupid FROM table etc" 

Set Cupidrst = CurrentDb.OpenRecordset(strSql1, dbOpensnapshopt, dbReadOnly)

do while not cupidrst.eof
    strCupid = [Cupidrst]![Cupid]
' more code
    cupidrst.movenext
loop

 

[Jemmo]

Namliam, thankyou, thankyou, thankyou, thankyou, thankyou, thankyou, thankyou, thankyou, thankyou, thankyou, thankyou, thankyou, thankyou.

That is looping and creating the individual spreadsheets.

Thankyou.

Now I just need to resolve the (SQL) filtering.

 

[namliam]

Namliam, thankyou, thankyou, thankyou, thankyou, thankyou, thankyou, thankyou, thankyou, thankyou, thankyou, thankyou, thankyou, thankyou.

LOL

Happy to help even if only once

Now I just need to resolve the (SQL) filtering.

What is your problem with your filtering?

 

[Jemmo]

As the code is looping through each member in the [Cupidrst]![strCupid] recordset, I want to return values from strSQL2 HAVING [tblData]![Cupid] = [Cupidrst]![strCupid] (the looping variable value).

(The SQL statement is grouped by a number of field groupings so I am using the HAVING clause)

 

[namliam]

Cupidrst is the recordset, not [Cupidrst]![strCupid], that is the column.

You can use a where over a having if you are not using any grouping function, like Max, Sum or Count.
Where is faster than the Having....

You can do something like
strSQL = ""
strSQL = strSQL & " Select bla ..."
strSQL = strSQL & " From bla ..."
strSQL = strSQL & " where [tblData]![Cupid] = " & [Cupidrst]![strCupid]
strSQL = strSQL & " group by etc "
etc.

To build your sql, this will replace the ID into the SQL.

Note:
Above is assuming your strCupID is actually a number field.
If it is a text use:
strSQL = strSQL & " where [tblData]![Cupid] = '" & [Cupidrst]![strCupid] & "'"
If a date use:
strSQL = strSQL & " where [tblData]![Cupid] = #" & [Cupidrst]![strCupid] & "#"

Note2:
With a date field make sure your date is either in ISO format (YYYY/MM/DD) or US format (MM/DD/YYYY) any other format will cause you problems!

 

[Jemmo]

Aha - that's another thing that's not explicitly explained on the MSDN site (not that I have seen - when mixing SQL with VBA Variables close the SQL statement with quotes BEFORE addiing the VBA variable.
I had this ages ago - but with the quote after the VBA variable.

Thankyou again.
So much.

 

[namliam]

Aha - that's another thing that's not explicitly explained on the MSDN site

What is not?? LOL

Microsoft is not exactly renouned for their great informative help and information.