Do Until

[Ankarn]

I have a Do Until which i use for adding elements with primary key from number a to number b in a table.

A problem is that if some of the numbers allready excist in the table, it wont add them. There is no error with it, but it just dont do anything with the numbers that are allready there, and that is ok, but i want the user to be noticed when that is the case so he know theres something wrong here.

For every "Do" i could run a test of whether the number allready excist in the database. Is that the only way? and lets say 10 of the numbers are allready there. Would the message with "The number x is allready in your database" 10 times?

someone have any idea on how to do this?

 

[Banana]

To be honest, I'm not totally sure if I understand your question.

You're asking for ways of not annoying your users with message that a number wasn't added 10 times?

In this case, something like this will display one messagebox:

Here's pseudocode:

Dim iExists() As Integer
Dim strMessage As String

Do Until Something
     If x InTheTable Then
        Redim Preserve iExists(Ubound(iExists)+1)
        iExists(Ubound(iExists))=x
     Else
       ....
     End If
     ....
Loop

If Ubound(iExist) > 0 Then
     For i= 1 to Ubound(iExist)
        strMessage=iExist(i) & strMessage
     Next i
     Msgbox strmessage
End If

 

[Ankarn]

i havent tried your code yet, and i'm not sure if i understand it. But just so we are clear about what i want. I primarily want the user to be notified if he is adding a number that allready excist. But since this is a Do Until where i add all numbers from a userdefined number a to a userdefined number b, there could be a lot of these cases in one click. Lets say i allready have records with primary keys between 10 and 40. Lets say the user try to add 1-100 in the primary key field. There would be 30 cases where the number allready excist.

What i want is to notify the user that this happened, But not 30 times. I dont know if that is the case, if the message will display 30 times, but i want the user to know, maybe at the first number.

Is InTheTable a poperty of vba? I honestly dont know how this code of yours work. But it seems like you are building up a string and adding all numbers that allready is there. That would be a great way to do it.

Redim Preserve iExists(Ubound(iExists)+1)
iExists(Ubound(iExists))=x
Are you kind of building up an integer here? it seems like an advanced way of doing it.

How would the "IsInTable" work when there are more fields in the table, and it could maybe match some number in one of the other fields.

i believe i need to specify which field.

 

[Banana]

Ankarn, please note that I prefaced my sample as "pseudocode", so stuff like "Something" or "IsInTable" are not actual code, but just a shorthand because I assumed you already know how to test for this yourself.

What that code did was create an array of integers and add a new element for each number that had duplicates and when we were done, display a message *once* with all numbers that was duplicated.

To understand what it does, use Access help to read up on keyword; some general pointer:

You declare an array by adding () to any variables so this is an array:

Dim iExists() As Integer

This isn't an array:

Dim iExists As Integer

In this case, we're using dynamic arrays but we still have to tell how many elements it can contain, so hence:

Redim iExists(X)

Will redimension the array with X many elements. If there were any elements saved prior to Redim, they will be lost.

But as you were using Do Until, not For...Next, which means that loops may not be always same in count (one time it takes one loop, another it takes 10 loops, and so forth), I made it so it would add one new element each time a number was found, *and* preserving all elements.

UBound=Upper bound of the array, so this line:

Redim Preserve iExists(Ubound(iExists)+1)

basically says to add one more elements on top of the existing elements.

Note that in general arrays are zero based, but in this case, we started with 1 because of Ubound() being used.

Finally, I generally avoid Redim Preserve because it is very resource intensive. If you find this too slow, you may just define a static array, e.g.:

Dim iExists(99) As Integer

and when you reach 100th elements, stop adding any more to the string so the messagebox only display first 100 (and a extra sentence indicating there were more but not displayed, perhaps).

I hope that helped.

 

[Ankarn]

This is interesting. I was kind of wondering when in my process arrays would come up. I have written 1700 lines of code on my database now, never had the use for array i guess. But i remember from java that arrays was the big thing.

When i know that its an array it makes sense.

I dont think i need a dynamic array. I can just mention the first one and have a count for how many of them there are.

Thanks.

 

[Banana]

Whatever works. Glad to be of help.