Random Generate Number 1

[brendasql]

I have a LostFocus sub routine that will generate a random ID number for adding a new record. When the user clicks the command button Save New it gives the user an error if this number already exist in the table. I did not use the auto random numner in ACCESS because users wanted a unique number that contained letters and number and the length could only be 7 characters long.

I need to figure out how to validate this random ID number at the time it is generated and so the user never receives this message and so they do not have to re-enter all the data they have just entered. I also need to go back into the sub routine and regenerate this number if it has been been duplicated.

 

[PHV]

You may consider the DLookUp function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[brendasql]

Thanks for your reply, I have attempted to use this function, but so far I have not been able to get it to work. I'm not real sure if I'm using it correctly. I normally program in VB6 and Access2002 is throwing me some bad curves.

 

[SeeThru]

Another way would be open a recordset (or recordsetclone) of the table with "WHERE [RefNo]=" & me!refno and then check if recordset.recordcount>0 - if it is, re-generate the random number.....

Would sequential not be easier?

 

[brendasql]

Yes it would be easier, but management requested that the ID number not be sequential.

It will not save this ID number to my table if it has been duplicated, but it gives the user a generic error message about primary keys and no duplicates when they press the SAVE button (only if the number is a duplicate).

Also, I have tried to Dim myDB as Database and (Database does not appear as an option, I see DataAccessPage), because I was going to try to open a connection to the database and execute a query to see if this number existed as a record in my table.

 

[TheAceMan1]

How are ya brendasql . . . . .

[blue]PHV[/blue] is on target with [blue]DLookUp[/blue]. The following example code sets the ID when the user navigates to a new record. In this manner the ID is preset before user enters data (when user goes to new record ID is already there). All you have to do is simply save!. The following steps install the code:
[ol][li]Rem out or remove the code in your [blue]LostFocus[/blue] event.[/li]
[li]In the OnCurrent event of the form, copy/paste the following line . . .

[blue]   If Me.NewRecord Then Call SetNewID[/blue]

[/li]
[li]In the code module for the form, copy/paste the following routine ([blue]You![/blue] substitute proper names in [purple]purple[/purple]) . . .

[blue]Public Sub SetNewID()
   Dim ID As String, Flg As Boolean
   
   Do
      [green]'Your Random ID Code Here[/green]
      ID = "[purple]Your Random ID Number from code above[/purple]"
      
      If IsNull(DLookup("[[purple][b]RandomFieldName[/b][/purple]]", "[purple][b]TableName[/b][/purple]")) Then
         Flg = True
      End If
   Loop Until Flg [green]'Loop if ID exists![/green]
   
   Me![purple][b]RandomTextboxName[/b][/purple].DefaultValue = ID
   
End Sub[/blue]

[/li]
[li]Set code in your [blue]Save New Button[/blue] to simply save.[/li][/ol]
[purple]Thats it . . . give it a whirl and let us know . . .[/purple]

See Ya! . . . . . .

 

[BobStubbs]

You could change your random number routine so it checks the number immediately, and chooses another number if it is already stored in the table:

Dim intCounter as Integer
Dim strNewID as String

Do
    strNewID = (your random number routine)
    intCounter = DCount("[IDFieldName]", "tblTableName", "[IDFieldName]='" & strNewID & "'")
Loop Until intCounter = 0

'Set the ID field in your new record equal to 
'strNewID here, e.g.
RecordID = strNewID

IDFieldName is the name of the field in your table, in which the unique ID is stored
tblTableName is the name of your table

First, set strNewID using your existing routine to create a new random number.
The DCount function counts the number of instances of this value which are already stored in the table.
If this is not zero - i.e. the number has already been used - the process will loop and another number will be chosen.
Finally, set the RecordID field in the new record equal to the value in the string variable.

I hope this will be helpful.

Bob Stubbs

 

[brendasql]

Many many Thanks to both TheAceMan1 and Bob. I couldn't get the kinks worked with the DLookup, but the Dcount worked great. Thank you both again!!!