Page 2 of 2 FirstFirst 12
Results 16 to 21 of 21
  1. #16
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Quote Originally Posted by lefty2cox View Post
    I think you have it. I'm looking to match the toggle name on the form with the "number" field of the recordset. And then I want to set the caption of the same toggle button to the "rank" field value in the same record as the matching number.

    So a loop is the answer, just not sure how to structure it. I want it to do this for all 35 toggle buttons on the form.

    I'm very new... only done a few bits of code with DAO stuff. Simple adding records and so forth.
    Here's my template code for a recordset loop:

    Code:
      Dim strSQL  As String  Dim db      As DAO.Database
      Dim rs      As DAO.Recordset
    
    
      Set db = CurrentDb()
      
      strSQL = "SELECT ..."
      Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
    
    
      Do While Not rs.EOF
        'code to use recordset here
        rs.MoveNext
      Loop
    
    
      set rs = nothing
      set db = nothing

    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  2. #17
    lefty2cox is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    May 2013
    Posts
    163
    Thanks for that code. I'm going to make that my next assignment. I decided to change the names of the toggles and went with the code below. No sure if there is excess. Could I be more streamline?

    Code:
    Private Sub cmdDisplay_Click()
    Dim ctl As Control
    Dim Ball(1 To 5) As Integer
    Dim Count As Integer
    Dim togCount As Integer
    togCount = 0
    Count = 1
    
    
    On Error GoTo tomanyBalls
    
    
    For Each ctl In Me.Controls
        
        If ctl.ControlType = acToggleButton Then
            togCount = togCount + 1
                If ctl.Value = True Then
                    Ball(Count) = togCount
                    Count = Count + 1
           
        End If
        End If
       
    Next
    
    
    'ball1, ball2, etc.. are the names of the text boxes on the form that display the numbers
    
    
    Ball1 = Ball(1)
    Ball2 = Ball(2)
    Ball3 = Ball(3)
    Ball4 = Ball(4)
    Ball5 = Ball(5)
    
    
    txtNumMatches = findMatch()
    If txtNumMatches > 0 Then
        txtNumMatches.Visible = True
        Else
        txtNumMatches.Visible = False
    End If
    If Ball1 = 0 Or Ball2 = 0 Or Ball4 = 0 Or Ball5 = 0 Then
    MsgBox "Select more numbers"
    End If
    Exit Sub
    tomanyBalls: MsgBox "Too many numbers"
    
    
    End Sub

  3. #18
    lefty2cox is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    May 2013
    Posts
    163
    Sorry if this is in the wrong place. I'm not sure if I should be using a new post for this but it's related to the same project. Thanks to pbaldy and everyone for the help. I've made much progress.

    I have a bit of code that is working well thanks to pbaldy but I'm getting an unexpected outcome. The sql statement doesn't appear to property filter the records in the query. curnum is a number to check against the query records. numdays are the user's number of days to go back from today. i thought the sql should filter the records but it just runs through all of them. The code checks out with no errors so I'm obviously missing some logic here. Thanks so much

    Code:
    Function testDue(curnum, numdays)
    
    
    
    
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim ctl As Control
    Dim str As String
    
    
    Dim keyDate As Date
    
    
    testDue = True
    
    
    
    
    keyDate = Date - numdays
    
    
    'The query named qrylast10draws is a name i'm going to change.  it's actually
    'a query of all the records in a table
    
    
    Set db = CurrentDb()
        str = "SELECT * FROM qrylast10draws WHERE drawdate > " & keyDate
        Set rs = db.OpenRecordset(str, dbOpenDynaset)
        
    Do While Not rs.EOF
        If curnum = rs!one Or curnum = rs!Two Or curnum = rs!three Or curnum = rs!four Or curnum = rs!five Then
        testDue = False
        
        End If
        rs.MoveNext
    
    
    Loop
    
    
    End Function

  4. #19
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    If it's a date field, try

    str = "SELECT * FROM qrylast10draws WHERE drawdate > #" & keyDate & "#"
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #20
    lefty2cox is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    May 2013
    Posts
    163
    Perfect. Thanks very much.

  6. #21
    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 problemo.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 04-04-2015, 08:10 AM
  2. Comparing Data From Two Tables
    By dcoley in forum Access
    Replies: 12
    Last Post: 05-01-2014, 12:09 PM
  3. Replies: 14
    Last Post: 01-07-2014, 04:20 PM
  4. Replies: 5
    Last Post: 12-09-2012, 02:29 PM
  5. Replies: 2
    Last Post: 07-30-2012, 03:26 PM

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