Results 1 to 14 of 14
  1. #1
    jaykappy is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Mar 2009
    Posts
    59

    Looping query

    I am looking to create a query in SQL vba that queries a table, grabs the unique values in a particular field.

    On each iteration through the query I want to create a new record for each unique value found...wondering how to do this...I am starting with this

    Dim db As Database
    Dim Lrs As DAO.Recordset
    Dim LSQL As String

    'Open connection to current Access database
    Set db = CurrentDb()

    'Create SQL statement to retrieve value from GST table
    LSQL = "SELECT DISTINCT LessonName FROM tbl_V4_Lessons;"

    Set Lrs = db.OpenRecordset(LSQL)



    Do While not EOF
    ' Grab the first unique value
    ' Msgbox that value
    Next

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You're in the ballpark. This needs to be:

    Do While not Lrs.EOF

    "Next" needs to be "Loop". Without

    Lrs.MoveNext

    inside the loop you'll have an endless loop on your hands.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    jaykappy is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Mar 2009
    Posts
    59
    Trying this and getting stuck in the loop

    Dim curdb As Database, currec As Recordset, Prop, TypeID, Tree

    Set curdb = CurrentDb
    Set currec = curdb.OpenRecordset("SELECT LessonName FROM tbl_V4_Lessons;", dbOpenDynaset)

    Do Until currec.EOF
    MsgBox "1"

    HOW TO GET THE VALUE and RETURN TO MSGBOX

    Loop

    currec.Close
    Set currec = Nothing

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I think I've addressed everything but getting the value:

    MsgBox currec!LessonName
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    jaykappy is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Mar 2009
    Posts
    59
    Got this to work...how do I get the value of teh LessonSubject field in the loop

    Dim curdb As Database, currec As Recordset

    Set curdb = CurrentDb
    Set currec = curdb.OpenRecordset("SELECT DISTINCT LessonSubject FROM tbl_V4_Lessons;", dbOpenDynaset)

    Do While Not currec.EOF
    MsgBox "1"
    currec.MoveNext

    Msgbox LessonSubject ????

    Loop

    currec.Close
    Set currec = Nothing

  6. #6
    jaykappy is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Mar 2009
    Posts
    59
    How would I get multiple values from 3 fields...this is not working

    Do While Not currec.EOF

    Dim varID, varLessonID, varLessonSubject, varLessonName
    varID = currec!ID
    varLessonID = currec!LessonSubject
    varLessonSubject = currec!LessonName
    varLessonName = currec!LessonName

    MsgBoxvar ID + varLessonID + varLessonSubject + varLessonName

    currec.MoveNext
    Loop

    Gotcha....THANKS very appreciated....

    Thank you

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You would have to add those fields to the SELECT statement.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    jaykappy is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Mar 2009
    Posts
    59
    This sort of works...but I ONLY want the DISTINCT to reflect the LessonSubject.....not all fields...but I want the fields from those unique LessonSubjects to be written to variables

    Dim curdb As Database, currec As Recordset

    Set curdb = CurrentDb
    Set currec = curdb.OpenRecordset("SELECT DISTINCT ID, LessonID, LessonSubject, LessonName, LessonDescription FROM tbl_V4_Lessons;", dbOpenDynaset)

    Do While Not currec.EOF
    'MsgBox "1"

    Dim varID As String, varLessonID As String, LessonSubject As String
    Dim LessonName As String, LessonDescription As String
    varID = currec!ID
    varLessonID = currec!LessonSubject
    varLessonSubject = currec!LessonName
    varLessonName = currec!LessonName
    varLessonDescription = currec!LessonDescription
    MsgBox varID & varLessonID
    'MsgBox ID + LessonID + LessonSubject + LessonName + LessonDescription

    currec.MoveNext
    Loop

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    What does the data look like, and what does your result look like?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #10
    jaykappy is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Mar 2009
    Posts
    59
    The Distinct seems to be working
    For some reason I get an error after running it on the MsgBox...think the last time through...
    Why is this

  11. #11
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    What's the error exactly, and on what line does it go if you choose debug?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  12. #12
    jaykappy is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Mar 2009
    Posts
    59
    It erros out on the Msgbox but after is loops through each unique value in the table...
    I am going to remove it and see what happens....I think I have what I need though...
    thank you very much for your time, help and patience...it was greatly appreciated.

  13. #13
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    No problem. Post back if you get stuck.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  14. #14
    jaykappy is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Mar 2009
    Posts
    59
    Its all working now...thanks for yoru help.....changed some of the table and field names...but still same issue....ALL GOOD TO GO
    Thanks


    Dim DBstudent As Database, RSstudent As Recordset
    Set DBstudent = CurrentDb
    Set RSstudent = DBstudent.OpenRecordset("SELECT DISTINCT StudentInfo1 FROM tbl_V4_StudentInfo;", dbOpenDynaset)

    Do While Not RSstudent.EOF
    Dim varStudentName As String
    varStudentName = RSstudent!StudentInfo1
    'MsgBox varStudentName

    Dim varLessonSubject As String, varLessonName As String, varLessonDescription As String
    varLessonSubject = Me.cbo_LessonSubject
    varLessonName = Me.txt_LessonName
    varLessonDescription = Me.txt_LessonDescription
    'MsgBox varStudentName + " " + varLessonSubject + " " + varLessonName + " " + varLessonDescription

    ' INSERT INTO Student Info table for each student and the new Subject, Subject Name, Subject Description
    strSQL = "INSERT INTO tbl_V4_Details(StudentName, LessonSubject, LessonName, LessonDescription)" & _
    "VALUES ('" & varStudentName & "', '" & varLessonSubject & "','" & varLessonName & "','" & varLessonDescription & "');"
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True

    RSstudent.MoveNext
    Loop

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Looping Search
    By srmezick in forum Forms
    Replies: 5
    Last Post: 11-04-2011, 11:13 AM
  2. Looping through Records in SQL
    By make me rain in forum Queries
    Replies: 13
    Last Post: 07-17-2011, 08:58 AM
  3. Looping in Access
    By ducthang88 in forum Programming
    Replies: 2
    Last Post: 12-04-2010, 07:43 PM
  4. Looping through a tbl to find a certain value
    By cwf in forum Programming
    Replies: 1
    Last Post: 05-17-2010, 04:02 PM
  5. Looping syntax
    By fadiaccess in forum Access
    Replies: 1
    Last Post: 10-23-2009, 02:57 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums