Page 1 of 3 123 LastLast
Results 1 to 15 of 31
  1. #1
    hammer187 is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Aug 2012
    Posts
    66

    Exclamation Trouble With For Loop That Checks An Array For A Number

    I am having problems with my for loop. I need it to check my array for a number that the user inputs in my form. If the number is in the array, it displays a form. If the number is not in the array, it displays another form. Here is my code for my button that the user presses to submit the number so the for loop can check to see if that number is in the array or not.


    I can get my array to work fine if I hard code an if and esleif statement for each number for my array, but that will be tedious. I just cant get my for loop to work properly to count through my array to compare the number that the user puts in to see if it is in the array or not. I've looked at many websites for examples and most of the examples will not work for Access. I have knowledge of C/C++ but not Access Code. I have highlighted the lines that I need to fix, or the lines that I think that I need to fix, but I just can't figure out how to fix it. Any help would be appreciated. Thank you in advance.


    Code:
    Private Sub Command12_Click()
    txtID = Forms![LoginForm1]![txtEmployeeID]
        Dim CoachID As Variant
        CoachID = Array("10269443", "10440457", "01654907", "10269343", "01604737", "01654817", "10391316")
        Dim x As Variant
    For Each x In CoachID
      If x = txtEmployeeID Then
        DoCmd.OpenForm "frmMain", acNormal
         ElseIf Len(txtIDNum < 0) Then
           MsgBox "Please enter a valid Associate ID"
           Else
              DoCmd.OpenForm "HourlyForm", acNormal
             End If
          Exit For
          Next
          DoCmd.OpenForm "frmMain", acNormal
          Exit Sub
           
    End Sub


  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
    Try

    For x = LBound(CoachID) To UBound(CoachID)

    I would be more explicit with my declarations:

    Code:
      Dim CoachID()          As String
      Dim x                  As Long
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    hammer187 is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Aug 2012
    Posts
    66
    I am still getting compile errors. I am getting an error with the LBound and UBound now. With the parentheses by CoachID, I was getting errors with my array.



    Code:
    txtID = Forms![LoginForm1]![txtEmployeeID]
        Dim CoachID As String
        CoachID = Array("10269443", "10440457", "01654907", "10269343", "01604737", "01654817", "10391316")
        Dim x As Long
    For x = LBound(CoachID) To UBound(CoachID)
        
        
        If x = txtEmployeeID Then
         
            
            DoCmd.OpenForm "frmMain", acNormal
        
         
        ElseIf Len(txtIDNum < 0) Then
            MsgBox "Please enter a valid Associate ID"
         
         Else
            
            DoCmd.OpenForm "HourlyForm", acNormal
           
         End If
          Exit For
          Next
          DoCmd.OpenForm "frmMain", acNormal
            Exit Sub
           
    End Sub

  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
    This is probably part of it (my suggestion, your code):


    Dim CoachID() As String
    Dim CoachID As String

    The () declares an array.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    hammer187 is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Aug 2012
    Posts
    66
    Now Im getting errors with my array.

    Code:
    Private Sub Command12_Click()
        txtID = Forms![LoginForm1]![txtEmployeeID]
        Dim CoachID() As String
        CoachID() = ["10269443", "10440457", "01654907", "10269343", "01604737", "01654817", "10391316"]
        Dim x As Long
    For x = LBound(CoachID) To UBound(CoachID)
        
        
        If x = txtEmployeeID Then
         
            
            DoCmd.OpenForm "frmMain", acNormal
        
         
        ElseIf Len(txtIDNum < 0) Then
            MsgBox "Please enter a valid Associate ID"
         
         Else
            
            DoCmd.OpenForm "HourlyForm", acNormal
           
         End If
          Exit For
          Next
          DoCmd.OpenForm "frmMain", acNormal
            Exit Sub
           
    End Sub

  6. #6
    hammer187 is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Aug 2012
    Posts
    66
    I found out that my else statement wasnt even getting executed. I fixed that but I am still having trouble with my array and for loop.

    Code:
    Private Sub Command12_Click()
        txtID = Forms![LoginForm1]![txtEmployeeID]
        Dim CoachID() As String
        CoachID() = ["10269443", "10440457", "01654907", "10269343", "01604737", "01654817", "10391316"]
        Dim x As Long
    For x = LBound(CoachID) To UBound(CoachID)
        
        
        If x = txtEmployeeID Then
         
            
            DoCmd.OpenForm "frmMain", acNormal
        
         
        ElseIf IsNull(txtID) Then
            MsgBox "Please enter a valid Associate ID"
         
         Else
            
            DoCmd.OpenForm "HourlyForm", acNormal
           
         End If
          Exit For
          Next
          DoCmd.OpenForm "frmMain", acNormal
            Exit Sub
           
    End Sub

  7. #7
    ketbdnetbp is offline Competent Performer
    Windows 7 32bit Access 2003
    Join Date
    Mar 2011
    Location
    Midwest
    Posts
    254
    Hammer187 – Not sure if this will help but, you could test…

    Private Sub Command12_Click()

    Dim txtID as Variant
    txtID = Forms![LoginForm1]![txtEmployeeID]

    If Len(txtID) > 0 Then

    Dim CoachID( ) As Variant
    ReDim CoachID(1 to 7) as Variant
    CoachID(1) = “10269443”
    CoachID(2) = “10440457”
    CoachID(3) = “01654907"
    CoachID(4) = "10269343"
    CoachID(5) = "01604737"
    CoachID(6) = "01654817"
    CoachID(7) = "10391316"
    Dim intC as Integer
    intC = UBound(CoachID)
    dim iCounter as Integer
    iCounter = 1

    Do until iCounter = intC
    If CoachID(iCounter) = txtID then
    DoCmd.OpenForm "frmMain", acNormal
    Exit Do
    End If
    Debug.Print iCounter
    iCounter = iCounter +1
    Loop
    DoCmd.OpenForm "HourlyForm", acNormal
    Else
    MsgBox "Please enter a valid Associate ID"

    End if
    ‘Add error handling
    End sub

    If this produces the desired results then, let me know and I can clean up the array element setting part.

    All the best,

    Jim

  8. #8
    hammer187 is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Aug 2012
    Posts
    66
    I am still getting errors, but it is close. The error that I am getting is Loop without Do.

    Code:
    Private Sub Command12_Click()
       
       
       Dim txtID As Variant
    txtID = Forms![LoginForm1]![txtEmployeeID]
    Dim CoachID() As Variant
    ReDim CoachID(1 To 7) As Variant
    CoachID(1) = “10269443”
    CoachID(2) = “10440457”
    CoachID(3) = "01654907"
    CoachID(4) = "10269343"
    CoachID(5) = "01604737"
    CoachID(6) = "01654817"
    CoachID(7) = "10391316"
    Dim intC As Integer
    intC = UBound(CoachID)
    Dim iCounter As Integer
    iCounter = 1
    Do Until iCounter = intC
    If CoachID(iCounter) = txtID Then
    DoCmd.OpenForm "frmMain", acNormal
    Debug.Print iCounter
    iCounter = iCounter + 1
    Loop
    DoCmd.OpenForm "HourlyForm", acNormal
    Exit Do
    Else
    MsgBox "Please enter a valid Associate ID"
    End If
      Exit Sub
           
    End Sub

  9. #9
    hammer187 is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Aug 2012
    Posts
    66
    What i ultimately want is I want the user to input the ID in a form. Then that ID will check the array to see if that ID is in the array or not. If it is, display frmMain, if it isn't display HourlyForm. I also want to use an error checking to check to see if the user accidentally pressed enter and didn't enter anything in the text box for the ID to display the message "Please enter valid ID" I was thinking the error checking part could be an if statement then the HourlyForm part could be the else statement.

  10. #10
    ketbdnetbp is offline Competent Performer
    Windows 7 32bit Access 2003
    Join Date
    Mar 2011
    Location
    Midwest
    Posts
    254
    hammer187 -

    You moved the exit do outside the loop, so when it reaches that point in the code, it errors. Copy and paste the code, run it without making any changes and see what occurs.

    Jim

  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
    Your error is because the Do loop and the If/Then overlap each other. I don't get the purpose of the array anyway. The only time I use them is with the Split function, when the data comes in that format. Rather than hard code the values of the array in code, I'd have them in a table and have your code check there. That makes it more dynamic anyway, as the users can add/delete values as necessary.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  12. #12
    hammer187 is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Aug 2012
    Posts
    66
    This is the code that I use if I wanted to do everything manually.

    Code:
    Private Sub Command12_Click()
        txtID = Forms![LoginForm]![txtEmployeeID]
        Dim CoachID As Variant
        CoachID = Array("10269443", "10440457", "01654907", "10269343", "01604737", "01654817", "10391316")
       
        If CoachID(0) = txtEmployeeID Then
        
            DoCmd.OpenForm "frmMain", acNormal
        
        ElseIf CoachID(1) = txtEmployeeID Then
        
         DoCmd.OpenForm "frmMain", acNormal
         
         ElseIf CoachID(2) = txtEmployeeID Then
        
         DoCmd.OpenForm "frmMain", acNormal
         
         ElseIf CoachID(3) = txtEmployeeID Then
        
         DoCmd.OpenForm "frmMain", acNormal
         
         ElseIf CoachID(4) = txtEmployeeID Then
        
         DoCmd.OpenForm "frmMain", acNormal
         
         ElseIf CoachID(5) = txtEmployeeID Then
        
         DoCmd.OpenForm "frmMain", acNormal
         
         ElseIf CoachID(6) = txtEmployeeID Then
        
         DoCmd.OpenForm "frmMain", acNormal
         
         ElseIf IsNull(txtID) Then
            MsgBox "Please enter a valid Associate ID"
         
         Else
            
            DoCmd.OpenForm "HourlyForm", acNormal
            
            
         End If
        
            Exit Sub
        
        
        
        
    End Sub

  13. #13
    hammer187 is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Aug 2012
    Posts
    66
    The code works, it compiles with no errors, but It doesnt check the array for the ID that the user inputs. It goes straight to the HourlyForm. The error checking works though. It is close.

  14. #14
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    The code works, it compiles with no errors
    ,

    This is a fallacy. The code works when it compiles without error, and produces the results that were expected.
    Lots of code compiles without error, but that's just the start of testing and debugging.

  15. #15
    hammer187 is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Aug 2012
    Posts
    66
    The results were not what they were supposed to be. It seems like it skips the whole array checking.

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Inputing Checks
    By bornstein.a in forum Access
    Replies: 1
    Last Post: 08-27-2012, 04:29 PM
  2. Having Trouble Returning Array from Function
    By NigelS in forum Programming
    Replies: 8
    Last Post: 08-15-2011, 07:12 AM
  3. Disappearing checks in check boxes
    By jimmonator in forum Forms
    Replies: 3
    Last Post: 07-21-2011, 02:57 PM
  4. Having trouble with Next Number
    By WyzrdX in forum Programming
    Replies: 2
    Last Post: 12-17-2010, 12:17 PM
  5. Insert Record checks table
    By pfarnell in forum Forms
    Replies: 13
    Last Post: 09-05-2010, 10:47 AM

Tags for this Thread

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