Re: [MS_AccessPros] randomly chosen groups

 

Ha ha! Thanks for fixing my air code, John :-) Glad you got it working, Russ.

Best, Graham
--
Sent from my phone. Please excuse my brvty and tpyos.

On 5 March 2017 07:37:58 GMT+13:00, "Russ dyspoz2@cox.net [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

 

John and Graham,

That did it!  I really cannot thank you enough for your help in this.  Clearly without that help I would be lost in that code.  I just hope that those "in charge" don't want the groups separated into different quantity some day.  If so, maybe I can just change the code to read:

"Const cNumGroups = 'new number of groups' "

Russ

On 3/4/2017 11:31 AM, John Viescas JohnV@msn.com [MS_Access_Professionals] wrote:

 

Well, he did say it is "air code". 😃

Try this:

Private Sub Command10_Click()
Const cNumGroups = 4
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim iCount As Long
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT [Group Number] FROM [Family Address] " _
        & "ORDER BY Rnd([Household #]);", dbOpenDynaset)
With rs
    Do Until .EOF

        .Edit
        ![Group Number] = (iCount Mod cNumGroups) + 1

        .Update
        .MoveNext
        iCount = iCount + 1
    Loop
    .Close
End With
Set rs = Nothing
Set db = Nothing

John Viescas, Author

Effective SQL

SQL Queries for Mere Mortals 

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications 

http://www.viescas.com/ 

(Paris, France)

On Mar 4, 2017, at 5:39 PM, Russ dyspoz2@cox.net [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,

I looked it over a bit and noticed the incorrect table name was there.  I corrected that, and now this one comes up.  

Error 3020:

Update or CancelUpdate without AddNew or Edit. 

With this text highlighted:  ![Group Number] = (iCount Mod cNumGroups) + 1

This is the current code:

Private Sub Command10_Click()
Const cNumGroups = 4
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim iCount As Long
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT [Group Number] FROM [Family Address] " _
        & "ORDER BY Rnd([Household #]);", dbOpenDynaset)
With rs
    Do Until .EOF
        ![Group Number] = (iCount Mod cNumGroups) + 1
        .MoveNext
        iCount = iCount + 1
    Loop
    .Close
End With
Set rs = Nothing
Set db = Nothing

On 3/4/2017 9:48 AM, John Viescas JohnV@msn.com [MS_Access_Professionals] wrote:

And the library you need is Microsoft Office 14.0 Access database engine Object Library.

John Viescas, Author

Effective SQL

SQL Queries for Mere Mortals 

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications 

http://www.viescas.com/ 

(Paris, France)

On Mar 4, 2017, at 4:46 PM, John Viescas <johnv@msn.com> wrote:

Sorry!  It's on the Tools menu.   Tools / References.

John Viescas, Author

Effective SQL

SQL Queries for Mere Mortals 

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications 

http://www.viescas.com/ 

(Paris, France)

On Mar 4, 2017, at 4:11 PM, Russ dyspoz2@cox.net [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

__._,_.___

---

Posted by: Graham Mandeno <graham@mandeno.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (29)

---

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

---

Visit Your Group

• New Members 2

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___