Deleting values in an Array

[ECEstudent]

Hi, How do I delete values in an array? I want to reuse that array but first I need to delete its values so I would start with Arr(0) rather than where I stopped off with the last loop at Arr(10) [assuming the last calculation inserted until Arr(9)]

Here is my code:

Set rst = CurrentDb.OpenRecordset( _
"Select * from dbo_ProductStructure where ChildProductNbr Like '*" & txtPartNumber & "*'") 'search associated fields with user input
            
While rst.EOF = False 
 
                ReDim Preserve Arr(i)
                Arr(i) = rst.Fields("ParentProductNbr") 
                i = i + 1
                rst.MoveNext
 
Wend 'end of while loop
x = Arr

 

[ByteMyzer]

You could try:

i = 0
[COLOR="Navy"]ReDim[/COLOR] Arr(i)

 

[ECEstudent]

Where would I put that? What does it do?

 

[ECEstudent]

I should also mention that my code is within a loop. this means that the value 'u' changes each time it goes through the loop but the array stays the same. Thus my need to erase the array before the next 'u' values in the loop. I would appreciate any help. Thank you.

 

[ByteMyzer]

You should put the code before the statement:

[COLOR="Navy"]While[/COLOR] rst.EOF = [COLOR="navy"]False[/COLOR]

As for what the code does, I suggest that you go onto Google and research the ReDim and ReDim Preserve statements more thoroughly to understand how they work.

A good place to start would be here: ReDim Statement (Visual Basic)

 

[ECEstudent]

Wouldn't that statement only delete the first value of the array though?

 

[ByteMyzer]

Hello, ECEstudent,

Please do the research I suggested before posting any more such questions. There is a plethera of information online, searchable through Google, on how ReDim and ReDim Preserve statements work, more than needs to be posted here. Doing this research will give you a much better understanding of these statements.

However, to answer your question, the statement ReDim Arr(i), with i = 0, resizes the array to a single element, and because the Preserve keyword is not used, does not preserve the contents.

Going by your example, if the array previously contained 10 elements (0-9), resizing the array to 1 element (0), would eliminate elements 1-9 automatically.

 

[ECEstudent]

I've already done my research and I did not understand your answer. Thus, I asked another question. I'm still researching and the whole Redim/Redim Preserve thing is not making much sense to me. I already got your private message. Thanks a lot for reposting it here? Anyways, I guess I'll do this on my own.

 

[ByteMyzer]

Have you tried the original solution I proposed to see if it works for you?

[COLOR="Navy"]Set[/COLOR] rst = CurrentDb.OpenRecordset( _
    "Select * from dbo_ProductStructure where ChildProductNbr Like '*" _
    & txtPartNumber & "*'") [COLOR="DarkGreen"]'search associated fields with user input[/COLOR]

[B][I][COLOR="darkgreen"]' Reset the Array[/COLOR]
i = 0
[COLOR="navy"]ReDim[/COLOR] Arr(i)[/I][/B]

[COLOR="navy"]While[/COLOR] rst.EOF = [COLOR="navy"]False
 
                ReDim Preserve[/COLOR] Arr(i)
                Arr(i) = rst.Fields("ParentProductNbr")
                i = i + 1
                rst.MoveNext
 
[COLOR="navy"]Wend[/COLOR] [COLOR="darkgreen"]'end of while loop[/COLOR]
x = Arr

 

[way2bord]

Anyways, I guess I'll do this on my own.

Good thinking. You'll learn a lot faster if you attempt this first.

Also, every basic math course and intro programming course will be using arrays; given your handle, I strongly encourage you to read up on basic array functionality, declaration, and usage (which will be fairly universal across most object oriented programming languages, with minor syntactical differences).