Startup/switchboard programming 1

[adamstuff]

Hi,
I'm trying to create a very "mild" security startup form. The form consists only of Username and Password fields, with Submit and Exit command buttons.
What I'm trying to do is have the Username be the first initial & last name of a user, and the password to be the last 6 digits of a user number, both items being entered by a user.
What I would like have happen when the Submit button is clicked, is that the employees tables is searched for the user's last name and concantenated with the first initial of the first name, and the last 6 numbers of the user number are checked and if they exist, to select a form for the user, depending on their position/rank. There are only 3 choices at this point, I never expect to have more than 5.
I know there is the built-in security of Access, but this is what I need now. Can anyone guide me down the yellow brick road?
TIA!

 

[gol4]

you will need to play with the syntax but th8is is a start

me.username= dlookup(left(fname),1 & left(lname),"Usertable",right(Usernum) = " & me.password)

 

[TheAceMan1]

How are ya adamstuff . . . . .

The following example code assumes:
[ol][li]UserName control on form is like [blue]JSmith[/blue].[/li]
[li]PassWord control on form is last 6 digits.[/li][/ol]
Note: [blue]you![/blue] substitute proper names/values where you see them:

[blue]   Dim db As DAO.Database, rst As DAO.Recordset
   Dim Criteria As String, UsrNam As String
   
   Set db = CurrentDb
   Set rst = db.OpenRecordset("EmployeeTableName", dbOpenDynaset)
   UsrNam = Me!UserName
   Criteria = "left([FirstName],1) = '" & Left(UsrNam, 1) & "' AND " & _
              "[LastName] = '" & Right(Me!UsrNam, Len(UsrNam) - 1) & "' AND " & _
              "Right([TablePassWordName],6) = '" & Me!UsrNum & "'"
   rst.FindFirst Criteria
   
   If rst.NoMatch Then
      MsgBox "User Not Found!"
   Else
      Select Case rst!Rank
         Case 1
            DoCmd.OpenForm "Form1"
         Case 2
            DoCmd.OpenForm "Form2"
         Case Else
            DoCmd.OpenForm "Form3"
      End Select
   End If

   Set rst = Nothing
   Set db = Nothing[/blue]

See Ya! . . . . . .

 

[TheAceMan1]

Hit submitt too soon . . .

The code above requires [purple]Microsoft DAO 3.6 Object Library[/purple] to run. To [blue]check/install[/blue] the library, in any code window click [blue]Tools[/blue] - [blue]References...[/blue] In the listing find the library and [blue]make sure its checked.[/blue] Then using the up arrow, [purple]push it up as high in priority as it will go[/purple]. Click OK.

See Ya! . . . . . .

 

[adamstuff]

Theaceman1,

Thank you ever so much. Had a little trouble with what you wrote, but it was exactly what I needed. It does what I need and helped me in learning and understanding(?) VBA. I am very impressed by your Criteria expression. I thought I knew about concantenation, but that's something else. I wrote it down several differnt ways to visually comprehend it. Marvelous! You could post an article on just how it's constructed and represents. That's one of the best programming lessons I ever had.

Thanks again!