Results 1 to 12 of 12
  1. #1
    winterh is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    110

    Please help with VB coding

    Hi All

    Got a problem with my VB code, what I am trying to do is build a login and password screen, but when i run it i keep getting and error "3265 Iteam not found in this collection".

    And I am not seeing the problem as I am not an expect with VB, I have enclosed the VB code below for you to look over.

    Private Sub LoginBut_Click()
    Dim dbs As Database
    Dim rstUserPwd As Recordset
    Dim bFoundMatch As Boolean
    Set dbs = CurrentDb
    Set rstUserPwd = dbs.OpenRecordset("loginQ") (This is the name of the Query)
    bFoundMatch = False
    If rstUserPwd.RecordCount > 0 Then
    rstUserPwd.MoveFirst
    ' Check for matching records
    Do While rstUserPwd.EOF = False
    If rstUserPwd![loginuser] = Form_LoginScreen.cmdlogintext.Value And rstUserPwd![passwordtxt] = Form_LoginScreen.cmdpasswordtxt.Value Then (this is the bit that turns yellow when i run it "loginuser" is the field in the query)


    bFoundMatch = True
    Exit Do
    End If
    rstUserPwd.MoveNext
    Loop

    End If
    If bFoundMatch = True Then
    ' Open the next form here and close this one
    DoCmd.Close acForm, Me.Name
    DoCmd.OpenForm "Main"
    Else
    '
    MsgBox "Incorrect Username or Password"
    End If

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Start by disambiguating your variables:
    Dim dbs As DAO.Database
    Dim rstUserPwd As DAO.Recordset

  3. #3
    winterh is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    110
    Sorry to be a little stupid, do you mean change ever "Dim" or "dbs" to "DAO" or do you mean

    DAo.Database
    DAO.Recordset
    Dim bFoundMatch As Boolean

    As if you do i cant seem to make the think work

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Just add what I put in RED.

  5. #5
    winterh is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    110
    Tried that but I am still getting the yellow line on the below bit of code.

    If rstUserPwd![loginuser] = Form_LoginScreen.cmdlogintext.Value And rstUserPwd![passwordtxt] = Form_LoginScreen.cmdpasswordtxt.Value Then

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Is this code contained in the LoginScreen form?

  7. #7
    winterh is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    110
    Yep dead right, all the form has on it is two text boxes and two buttons one which says "login" and the other says "cancel" and the code is on the "click on" on event on the "login" button.

    The user details are in it own table called "login" and the query which pulls "loginuser" and "password" is called "loginQ"

  8. #8
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You have an "END IF" and "LOOP" out of order. It should be:

    Code:
             bFoundMatch = True
             Exit Do
             rstUserPwd.MoveNext
          Loop
       End If
    You don't need to have ".Value" because the value property is the default property.

    "Form_LoginScreen.cmdlogintext" is not the correct to reference a control on a form. If the code is in the form module, use "Me.ControlName" ie "Me.cmdlogintext"

    Otherwise use "Forms!LoginScreen.cmdlogintext"


    Why don't you open the query already filtered?
    You could use something like"

    Code:
    Private Sub LoginBut_Click()
       Dim dbs As Database
       Dim rstUserPwd As Recordset
       Dim bFoundMatch As Boolean
       Dim sSQL As String
    
       Set dbs = CurrentDb
    
       sSQL = "Select cmdlogintext, cmdpasswordtxt FROM TableName WHERE cmdlogintext = '" & Me.cmdlogintext & "' AND cmdpasswordtxt = '" & Me.cmdpasswordtxt & "';"
       Set rstUserPwd = dbs.OpenRecordset(sSQL)
    
       If rstUserPwd.RecordCount > 0 Then
          ' Open the next form here and close this one
          rstUserPwd.Close
          Set rstUserPwd = Nothing
          Set dbs = Nothing
    
          DoCmd.Close acForm, Me.Name
          DoCmd.OpenForm "Main"
          Exit Sub
       Else
          '
          MsgBox "Incorrect Username or Password"
       End If
    
       rstUserPwd.Close
       Set rstUserPwd = Nothing
       Set dbs = Nothing
    
    End Sub

  9. #9
    winterh is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    110
    Thanks Steve, worked like a dream....one quick question.

    When i input in the password field you can see what is being imputed, how do I turn it into starts for example.

    Cheers

    Hads

  10. #10
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Change the Format property of the control to "Password".

  11. #11
    winterh is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    110
    Thanks, will sort it in the morning.

  12. #12
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Oops, my bad.

    Like RuralGuy said, disambiguate your variables.
    The first two lines should be:
    Code:
    Private Sub LoginBut_Click()
        Dim dbs As DAO.Database
        Dim rstUserPwd As DAO.Recordset
    .
    .
    .

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Need help in VBA coding
    By Kcgp in forum Programming
    Replies: 6
    Last Post: 02-01-2012, 11:22 PM
  2. Email coding
    By arrowmakersuk in forum Access
    Replies: 3
    Last Post: 12-19-2011, 10:06 AM
  3. Date coding
    By shralpy in forum Programming
    Replies: 7
    Last Post: 11-25-2011, 04:29 PM
  4. Access without coding
    By kp123 in forum Access
    Replies: 4
    Last Post: 11-25-2011, 03:50 PM
  5. Bar-Coding
    By texasprincess7 in forum Access
    Replies: 1
    Last Post: 02-12-2009, 10:29 AM

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