Problem with populating an Array!

[Niklas Östergren]

Hi!

I have nevere used array´s before and have read a little about them and are
now trying to populate an one dimensional array with a primary key from a
local table. But i keep getting error (Runtime Error 13) even if I dim the
array as a variant (please se my code below).

Anyone knowing what I´m doing wrong here?

TIA!
// Niklas


=============================================
Public Function ArrayExample()

Dim rec As DAO.Recordset
Dim lngarrPersonID() As Long

Set rec = Currentdb().OpenRecordset("tblPerson", dbOpenSnapshot)
rec.MoveLast

ReDim lngarrPersonID(rec.RecordCount)

rec.MoveFirst

Do While Not rec.EOF
lngarrPersonID = rec!PersonID
Debug.Print lngarrPersonID(0)
rec.MoveNext
Loop

Erase lngarrPersonID

rec.Close
Set rec = Nothing

End Function
====================================================

 

[Niklas Östergren]

Sorry I copyed the code from an example when I was testing. Please se below
for the correct code I´m trying to run.

The row, in my code, that gets highlighted as runtime 13 is <
lngarrPersonID = rec!PersonID
TIA!
// Niklas

==========================================
Public Function ArrayExample()

Dim rec As DAO.Recordset
Dim lngarrPersonID() As Long

Set rec = Currentdb().OpenRecordset("tblPerson", dbOpenSnapshot)
rec.MoveLast

ReDim lngarrPersonID(rec.RecordCount)

rec.MoveFirst

Do While Not rec.EOF
lngarrPersonID = rec!PersonID
Debug.Print lngarrPersonID(rec!PersonID)
rec.MoveNext
Loop

Erase lngarrPersonID

rec.Close
Set rec = Nothing

End Function
==========================================================

 

[Steve Huff]

You need to specify the subscript of the array. Array's by the way in VBA
start at 0 unless you use the Option Base 1 at the top of the Module. You
can ignore the 0 position of course, but that's just wasteful.

Try something like this:

For x = 1 to rec.RecordCount
lngarrPersonID(X) = rec!PersonID
Debug.Print lngarrPersonID(x)
rec.MoveNext
Loop

 

[Randy]

Hi!

I have nevere used array´s before and have read a little about them and
are now trying to populate an one dimensional array with a primary key
from a local table. But i keep getting error (Runtime Error 13) even if I
dim the array as a variant (please se my code below).

Anyone knowing what I´m doing wrong here?

TIA!
// Niklas

Niklas, as I can see from your code below, you need to read and practice all
about Arrays. You can look it up on the help menu by typing "ReDim." Please
review the sub-topics "Declaring Arrays", "ReDim Statement" and their
respective examples. I have made changes to your code below so it can work
properly. The modifications and their instructions are inline. Please make
and analyze the changes. It's not too difficult, but please take the time so
you can understand and learn it for future array interaction.

=============================================
Public Function ArrayExample()

Dim rec As DAO.Recordset
Dim lngarrPersonID() As Long

Set rec = Currentdb().OpenRecordset("tblPerson", dbOpenSnapshot)
rec.MoveLast

ReDim lngarrPersonID(rec.RecordCount)

Replace previous line with this:
ReDim lngarrPersonID(rec.RecordCount - 1)

rec.MoveFirst

Add these 2 lines here:
Dim c As Long
c = 0

Do While Not rec.EOF
lngarrPersonID = rec!PersonID

Replace previous line with this:
lngarrPersonID(c) = rec!PersonID

Debug.Print lngarrPersonID(0)

Replace previous line with this:
Debug.Print lngarrPersonID(c)

rec.MoveNext

Add the following line here:
c = c + 1

Loop

Erase lngarrPersonID

rec.Close
Set rec = Nothing

End Function
====================================================

-Randy

 

[Niklas Östergren]

Thank´s a lot Steve!

I just changed "Loop" to "Next X" and it worked!

This will come on handy for my application and I´m going to use it for
saving primary keys for several records that was newly added and which I
cant select in any other way (that I have come to think of) than to store
the primary key´s in a global variable.

I´m working on a wizard where I actually create the records in seversl
tabel´s and I´d like to be able to delete the records if the used step
backwards (cmdPrevious) to the previous form or if they abort the wizard. So
here´s where the global array variable comes in.

Maby ther´s a better way of doing this but this is my first wizard, so...

Thank´s again for you help!

// Niklas

 

[Niklas Östergren]

OK, I´ll read more and practice as well!

I have looked at your code and I have read so much that I understand it when
I see it. But now I need to practice more so I´ll be able to reproduce it
and use it whenever I need to.

One Q:

Isn´t it possible to use "rec.GetRows" to do the same thing? Or do I allways
get the complete row, unless I specifye the recordset to only hold exactly
the field I want, or?

TIA!
// Niklas