Trouble With For Loop That Checks An Array For A Number

[ketbdnetbp]

hammer187 - If its the code I provided

Try adding:
DeBug.Print CoachID(iCounter)
'before the line
'If CoachID(iCounter) = txtID then

Jim

[hammer187]

No change Jim.

[hammer187]

Here is the code that I am using

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) = "10269343"
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
Debug.Print CoachID(iCounter)
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
End Sub

[ketbdnetbp]

hammer187 -

Try this...
Dim txtID As Variant
txtID = Forms![LoginForm1]![txtEmployeeID]
'Add
MsgBox txtID
'See what is returned

Jim

[hammer187]

It displays the correct number but it doesnt check the array for that number I dont think.

[hammer187]

I am going to leave to go home from work. Keep giving me ideas or snippets. I will test them when I get home, which will be an hour. Thank you.

[ketbdnetbp]

In which case...

I would check the value of the Debug.Print CoachID(iCounter)
dim x as Variant
x = Debug.Print CoachID(iCounter)
MsgBox x

and, lastly I would check to see if the number returned from the previous MsgBox was in the array values stipulated.

Jim

[pbaldy]

I would love to hear why you want to do this the hard way. I would point out that as written, the HourlyForm will open no matter what. It would be easier for Jim to debug if you could post the db here.

[ketbdnetbp]

Paul - Thank you for point out the hourly form oversight. Your help is much appreciated. Jim

[pbaldy]

No problem Jim.

<Soapbox>
I just don't like using an array for this. It's basically putting business logic in code that belongs in a table. You shouldn't have to change the design of the application when you get a new employee, which this will require. Arrays are wonderful tools, just not the correct tool for this job in my opinion.
</Soapbox>

[orange]

I tested your code.
You have some illegal quotes in these

CoachID(1) = “10269443” 
CoachID(2) = “10440457”

I changed the quotes and commented a few lines and the code ran and executed the "open Form " line.
There may be more, but that is what I found.


You need to use the proper delimiter "

[ketbdnetbp]

Paul/Orange - Please look at the "not so elegant" revised code below...

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) = "10269343"
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
Dim Switch as Integer
Switch = 1
Do Until iCounter = intC
Debug.Print CoachID(iCounter)
If CoachID(iCounter) = txtID Then
DoCmd.OpenForm "frmMain", acNormal
Switch = 2
Exit Do
End If
Debug.Print iCounter
iCounter = iCounter + 1
Loop
If Switch = 1 then
DoCmd.OpenForm "HourlyForm", acNormal
End if
Else
MsgBox "Please enter a valid Associate ID"
End If
End Sub


At this point, I'm just trying to get it to work.


Jim

PS - The quotes were a result of copying/pasting.

[hammer187]

That last code worked great. I would love to do it the easy, dynamic way with a table. But like I said in my first post, I am not good with access. That is why I had this forum to help me with this array. Thank you Jim

[ketbdnetbp]

hammer187 -

Very glad it is working. However, as Paul suggested, when a change occurs, records added/deleted, etc. in the employee table, the code would have to be updated. If you can provide details of the employee table, I/we might be able to help you with a dynamic solution.

As always, thanks to Paul and Orange for their assistance.

Jim

[hammer187]

I am totally fine with updating the code everytime a change occurs. I was going to copy and paste my esleif statements anyway with the code that I had written. This just helped me scratch out a step.