Random records

[knarlyd@hotmail.com]

Please forgive my somewhat "sloppy" coding as I'm not too good at it as you might see.
I've got an issue with retrieving random records which is puzzling me.
When i run the code below, it only pulls 1/2 of the total # in the select statement

SELECT TOP 10 * FROM SM_Import ORDER BY rnd(INT(NOW*id)-NOW*id)

So, it only pulls up 5 (emails) to create, not 10. If I change it from 10 to 20, it pulls 10.
Any ideas why and how to fix?

And on to the next issue:

Below is the entire code
The code always pulls the exact same records each time but I'm not sure why.
Can someone check this and let me know how i can correct these two issues?

Private Sub Command1_Click()
On Error GoTo ErrorCheck

Dim ExitDB As Integer
Dim NewURL As String
Dim rs As DAO.Recordset, strSql As String
Dim Lotal As String

NewURL = DLookup("URL", "Hyperlink")

Randomize

Ltotal = DCount("ID", "SM_Import")
MsgBox Ltotal & " Records"

Dim x As Integer

strSql = "SELECT TOP 10 * FROM SM_Import ORDER BY rnd(INT(NOW*id)-NOW*id)"

Set rs = CurrentDb.OpenRecordset(strSql)
Do While Not rs.EOF

Dim ol As Object
Dim CaSubject As String
Dim rst As Recordset
Dim db As Database
Dim Full_Name As String
Dim myVarFirstName As String
Dim myVarLastName As String
Dim myVarEmail As String
Dim myvarSubject As String
Dim myvarCreatedBy As String
Dim rst1 As Recordset
Dim myVar1 As String

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT Email FROM SM_Import")

Dim MyVarFLName As String

myVarFirstName = rs.Fields(1)
myVarLastName = rs.Fields(2)
myvarSubject = rs.Fields(6)
myVarEmail = rs.Fields(7)
MyVarFLName = myVarFirstName & " " & myVarLastName
Full_Name = MyVarFLName
myvarCreatedBy = rs.Fields(8)
With rst
.MoveNext

End With

Set rst1 = db.OpenRecordset("SELECT Subject FROM SM_Import")

With rst1
.MoveNext
Subject = .Fields(0)
End With

Set db = CurrentDb()

Set ol = CreateObject("Outlook.Application")
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)

With MailOutLook

.To = Full_Name
.Subject = "Test"
.SentOnBehalfOfName = "test@schn.com"
.HTMLBody = "Thank you"
.Display

rs.MoveNext
End With
rs.MoveNext
Loop

rs.Close
Set rs = Nothing

Exit Sub

Forms!Employees!Switchboard.SetFocus

ErrorCheck:
MsgBox "Error No.: " & Err.Number & vbNewLine & vbNewLine & _
"Description: " & Err.Description

 

[AccessBlaster]

Give this a quick read. http://www.techrepublic.com/blog/ho...-a-random-set-of-records-in-microsoft-access/

 

[Cronk]

Because you have the statement

rs.MoveNext

twice in the loop, you will only get every second record.

 

[knarlyd@hotmail.com]

Give this a quick read. http://www.techrepublic.com/blog/ho...-a-random-set-of-records-in-microsoft-access/

Thanks for your reply, very appreciated!

 

[knarlyd@hotmail.com]

Because you have the statement

rs.MoveNext

twice in the loop, you will only get every second record.

That did the trick, thanks so much!