Function Call Itself?

1. Senior Member
Join Date
Dec 1969
Posts
167

## Function Call Itself?

Is it safe for a function (that returns an three-digit integer value) to call itself? What I&#039;m attempting to do is to randomly generate a three digit integer and then check to make sure the number it created isn&#039;t already in a table. If it is, then it needs to repeat the cycle until the number is unique. An autonumber field isn&#039;t an option.<BR><BR>Thanks,<BR><BR>Defiant005

2. Senior Member
Join Date
Dec 1969
Posts
96,118

## Could, but slow...

Recursive functions are slow. Use a FOR loop. Much faster. <BR><BR>You say you want a 3 digit number, so that&#039;s easy:<BR><BR>&#060;%<BR>Dim numbers(1000) &#039; a little overkill, but easier this way<BR><BR>RANDOMIZE<BR><BR>Function ThreeDigit( )<BR> Do While True &#039; loop forever!<BR> num = 100 + INT( 900 * RND ) &#039; gets a number from 100 to 999<BR> If numbers(num) &#060;&#062; num Then &#039; this number not chosen yet<BR> numbers(num) = num &#039; set the flag saying it is now chosen<BR> ThreeDigit = num &#039; this is the right answer<BR> Exit Function &#039; and quit immediately<BR> End If<BR> &#039; if we get here, then the number was already in the table...<BR> &#039; and so it was already chosen...<BR> &#039; so just loop to choose another!<BR> Loop<BR>End Function<BR>%&#062;<BR><BR>Okay?<BR>

3. Senior Member
Join Date
Dec 1969
Posts
167

## Not Quite, But Whatever I Did...

I don&#039;t quite understand. I was actually trying to do it with a loop, but I couldn&#039;t get it to work. Below is the function, in its entirety, I&#039;m using to try to make this work.<BR><BR>Function CreateMemberID()<BR> <BR> Dim intRandomInteger, intCounter, intMemberID, sqlCheckMemberID, rsCheckMemberID<BR> <BR> Randomize<BR> <BR> For intCounter = 1 To 3<BR> <BR> intRandomInteger = Int((9 - 0 + 1) * Rnd + 0)<BR> <BR> intMemberID = intMemberID & intRandomInteger<BR> <BR> Next<BR> <BR> Call OpenDatabaseConnection("mydatabase.mdb","mypasswor d")<BR> <BR> sqlCheckMemberID = "SELECT MemberID FROM Roster WHERE MemberID = " & intMemberID<BR> Set rsCheckMemberID = oConn.Execute(sqlCheckMemberID)<BR> <BR> If Not rsCheckMemberID.eof Then<BR> <BR> Do While Not rsCheckMemberID.eof<BR> <BR> For intCounter = 1 To 3<BR> <BR> intRandomInteger = Int((9 - 0 + 1) * Rnd + 0)<BR> <BR> intMemberID = intMemberID & intRandomInteger<BR> <BR> Next<BR> <BR> sqlCheckMemberID="SELECT MemberID FROM Roster WHERE MemberID = " & intMemberID<BR> <BR> Set rsCheckMemberID = oConn.Execute(sqlCheckMemberID)<BR> <BR> Loop<BR> <BR> rsCheckMemberID.Close<BR> Set rsCheckMemberID = Nothing<BR> <BR> Call CloseDatabaseConnection()<BR> <BR> CreateMemberID = intMemberID<BR> <BR> Else<BR> <BR> rsCheckMemberID.Close<BR> Set rsCheckMemberID = Nothing<BR> <BR> Call CloseDatabaseConnection()<BR> <BR> CreateMemberID = intMemberID<BR> <BR> End If<BR> <BR>End Function<BR><BR>-----------------------------<BR><BR>After playing with it WHILE I was posting this, I figured it out after all. The function SEEMS to work fine (I&#039;ve changed the above code to reflect the changes; look it over if you could and see if any red flags (or even yellow ones) jump out at you). It was my debugging code that was creating problems. Go figure...<BR><BR>But not all was lost: I found several typos (thanks to Option Explicit), I learned something new yet again from you, and the function seems to be doing what it&#039;s supposed to.<BR><BR>Thanks Bill!<BR><BR>Defiant005

4. Senior Member
Join Date
Dec 1969
Posts
96,118

## Well, you could write it a lot shorter...

Randomize &#039; SHOULD ONLY BE USED ONCE PER PAGE!<BR><BR>Function CreateMemberID()<BR><BR> &#039; if you do anything at all with this DB outside this<BR> &#039; function, then this *REALLY* ought to be called outside<BR> &#039; this function...at page global scope, presumably<BR> &#039; please don&#039;t open and close a connection for each db query/usage!<BR> Call OpenDatabaseConnection("mydatabase.mdb","mypasswor d")<BR><BR> Dim intCounter, intMemberID, sqlCheckMemberID, rsCheckMemberID<BR><BR> Do While True &#039; loop forever<BR> intMemberID = 0<BR> For intCounter = 1 To 3<BR> &#039; shame on you...your intMemberID was a *string*!<BR> &#039; so it should have been strMemberID<BR> intMemberID = intMemberID * 10 + Int(10 * RND() )<BR> Next<BR><BR> sqlCheckMemberID = "SELECT MemberID FROM Roster WHERE MemberID = " & intMemberID<BR> Set rsCheckMemberID = oConn.Execute(sqlCheckMemberID)<BR><BR> If rsCheckMemberID.eof Then &#039; if nobody has that number yet<BR> CreateMemberID = intMemberID &#039; then assign it now<BR> Exit Function &#039; and we are done<BR> End If<BR> Loop<BR><BR>End Function<BR>

5. Senior Member
Join Date
Dec 1969
Posts
96,118

## I should point out...

...that this is all bogus.<BR><BR>You execute that code to get a random member id.<BR><BR>And two users (or more) hit your site at the same time.<BR><BR>And by random chance they both manage to get through all that code and they both decide that number 732 is not in use.<BR><BR>So then they both add themselves to the DB using 732.<BR><BR>Can you say "OOPS"?<BR><BR>

6. Senior Member
Join Date
Dec 1969
Posts
167

## RE: I should point out...

This is true, but the probability of that happening out of 1,000 numbers, AND the chances of the database being added to within two seconds of each other (especially for the small size of my organization) would make such a happening less likely than being struck by lightening or winning the lottery. (Or getting a raise??)<BR><BR>I&#039;ll try to clean up my code a bit. Thanks Bill!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•