Repeat Random String

[GavZ]

Hi - I was just wondering if someone can take a look at the attached.

I want the form to open and repeat adding in records which produces a random string until it gets to 20,000 records without me having to click anything.

Currently it produce the strings ok using the Docmd.GotoRecord, , NewRec on the Got_Focus event but stops every 407 records.

Its in Access97 and only contains 1 textbox.

Hope you can help - thanks in advance

Gav

 

[bigstav]

Repeat Post ?????

Hi Gav, i have provided you with the code to do this in your last post, just add below to the OnOpen() of the form, or the GotFocus() of the Text box ...


Public Sub Insert()

Dim strRandNo As String
Dim Conn As ADODB.Connection
Dim strSQL As String
Dim Check

Set Conn = CurrentProject.Connection

Dim n As Long
n = 0

Do
If n = 20000 Then
MsgBox "Finished"
Exit Sub
End If

TryAgain:
strRandNo = CStr((Int((999 - 100 + 1) * Rnd + 100))) + "-" + CStr((Int((999 - 100 + 1) * Rnd + 100))) + "-" + CStr((Int((999 - 100 + 1) * Rnd + 100)))
Check = DLookup("[FieldName]", "TableName", "[FieldName] = '" & [strRandNo] & "'")
If IsNull(Check) Then 'Created No does NOT already exist.
strSQL = "INSERT INTO [TableName] VALUES ('" & strRandNo & "')"
Conn.Execute strSQL
Else 'Created No DOES exist, so don't try insert.
GoTo TryAgain:
End If

n = n + 1

Loop

End Sub


NB:-

You will need to alter it slightly if you're using DAO in Access 97 :-

Dim db As DAO.Database
Set db = CurrentDb()
db.Execute(strSQL)

 

[GavZ]

Thanks for this - it comes up with -

User-Defined Type Not Defined

And highlights - Dim Conn As ADODB.Connection

Isnt there a problem with ADODB and Access97?

 

[bigstav]

Acc 97 code

Try this ...


Public Sub Insert_Access97()

Dim strRandNo As String
Dim strSQL As String
Dim Check
Dim db As DAO.Database
Set db = CurrentDb()

Dim n As Long
n = 0

Do
If n = 20000 Then
MsgBox "Finished"
Exit Sub
End If

TryAgain:
strRandNo = CStr((Int((999 - 100 + 1) * Rnd + 100))) + "-" + CStr((Int((999 - 100 + 1) * Rnd + 100))) + "-" + CStr((Int((999 - 100 + 1) * Rnd + 100)))
Check = DLookup("[FieldName]", "TableName", "[FieldName] = '" & [strRandNo] & "'")
If IsNull(Check) Then 'Created No does NOT already exist.
strSQL = "INSERT INTO [TableName] VALUES ('" & strRandNo & "')"
db.Execute (strSQL)
Else 'Created No DOES exist, so don't try insert.
GoTo TryAgain:
End If

n = n + 1
Loop

End Sub

 

[Guus2005]

I have a slight modification to your code:

Public Sub Insert(lngNumberOfRecords As Long)

    Dim dbs         As Database
    Dim strRandNo   As String
    Dim strCheck    As String
    Dim lngCounter  As Long
    Dim blnContinue As Boolean
       
    For lngCounter = 1 To lngNumberOfRecords
        Do
            blnContinue = False
            strRandNo = CStr(CInt(Rnd(Now()) * 1000) & "-" & CInt(Rnd(Now()) * 1000) & "-" & CInt(Rnd(Now()) * 1000))
            If Len(Nz(DLookup("[FieldName]", "TableName", "[FieldName] = '" & [strRandNo] & "'"), "")) = 0 Then
                dbs.Execute "INSERT INTO [TableName] VALUES ('" & strRandNo & "')"
                blnContinue = True
            End If
        Loop Until blnContinue
    Next lngCounter
    
End Sub

Don't know why you use the calculation that you use. Doesn't make sense to me. ((999 - 100 + 1) * Rnd + 100)). Don't want to sound disrepectful, just don't understand.

 

[bigstav]

Even better

Hi Guus, i agree your code is tidier.

To be honest i just nicked the Random bit off the web for the sake of speed - i was more concerned with GavZ some ideas of how to Loop and insert values into table whilst checking for duplicates.

 

[Guus2005]

I was triggered by your GoTo in the code. I got the duplicate checking from you.