For loop

[javajom]

Hi,
I'm new to vb and to make a function that will literate through an array. The array will have a list of names in it and when the function opens it will pen a input box asking afor a name, then it will literate through the array and return true if it's there or false if not. Please help.

 

[ajetrumpet]

Where are you getting the names from? It doesn't really sound like an array is needed for this. Can't you look-up the value somehow (wherever it comes from), and return the boolean based on the finding??

 

[pbaldy]

What's the question? What have you got so far?

 

[javajom]

Hi,

What's the question? What have you got so far?

Private Sub Auto_Open2()
Dim myArray(10) As String
Dim count As Integer
Dim pWord As String
Dim ans As Boolean
ans = False
count = 0
myArray(1) = "Jon"
myArray(2) = "Mandy"
myArray(3) = "Jack"
myArray(4) = "Jamie"
pWord = InputBox("Please enter password")
Do While (count <= 4)
If pWord = (myArray(count)) Then
ans = True
End If
count = count + 1
Loop
If ans = True Then
MsgBox ("Hello")
Else: MsgBox ("Bye")
Application.Quit
End If
End Sub

Which gives access providing your names is in the array and shuts down if you're are not. The next step is to allocate different security locks to the names, so Jon is the administator so access to all, Mandy is stores so can only have access to stores tables and forms then Jack and Jamie will have access only to production tables and forms.

So on start up it would ask your password and depending on what it is, would only give you access to your security level. Any ideas?????

 

[pbaldy]

Well, my first thought was also that an array was probably not the best solution here. I'd have a table that had user names, passwords and security levels. The user inputs their name and password, and you validate it against the table. If they pass, you also grab their security level and do whatever you want with it.

 

[javajom]

sounds good!

Hi,
As I said I'm new to vba programming but sounds a great idea, but I'm new to this and have no idea how to do it. Have a hidden table with all information, how do you write a function/sub to do this?

 

[pbaldy]

Something along these lines, off the top of my head

  Dim strSQL  As String
  Dim db      As DAO.Database
  Dim rs      As DAO.Recordset

  Set db = CurrentDb()
  
  strSQL = "SELECT * FROM TableName WHERE UserName = '" & Me.txtUserName & "' AND Password = '" & Me.txtPassword & "'"
  Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

  If rs.EOF Then
    'no record found, something failed
  Else
    'user & password matched, do what you want
    Me.txtSecurityLevel = rs!SecurityLevel
  End If
  set rs = nothing
  set db = nothing

 

[javajom]

Many thanks I will try to work something out.