Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2004
    Posts
    64

    Unanswered: How to add a code to login form to filter records?

    I have a login form where sales reps put in their login name and password which opens a customers form. I want customers form to open with only a particular month's records for that sales rep.
    What I want to do is
    1) Open records for the sales rep who is loging in. (This part works).
    2) Open only the records where RunMonth = #6/1/2004# when the customers form opens. (This is what I'm trying to add to the code).
    (RunMonth is a text field on the form, its control source is RunMonth from the customers table which is the customers form's control source).

    How do I add a filter to the code I have below to make it work?

    Private Sub Command6_Click()
    On Error GoTo Err_Command6_Click

    Dim stDocName As String
    Dim stLinkCriteria As String
    Dim conDB As ADODB.Connection
    Dim rstblReps As ADODB.Recordset
    Dim strPassword As String

    Set conDB = New ADODB.Connection
    conDB.Provider = "Microsoft.Jet.OLEDB.4.0"
    conDB.Open "\\Backup\Departments\Mrkt\Telemarketing_Database\ CustomerCallServiceTracking.mdb", "Admin", ""


    Set rstblReps = New ADODB.Recordset
    rstblReps.Open "select Password from tblReps where [Rep#]=" & Me![Combo4], conDB
    strPassword = Trim(rstblReps!Password & "")

    rstblReps.Close
    Set rstblReps = Nothing
    conDB.Close
    Set conDB = Nothing

    Text5.SetFocus
    If strPassword = Trim(Text5.Value & "") Then

    stDocName = "frmCustomers"

    If Me![Combo4] <> 9 Then '9 = Admin
    stLinkCriteria = "[Rep#]=" & Trim(Me![Combo4].Column(0))
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    Else
    DoCmd.OpenForm stDocName
    DoCmd.Maximize

    End If

    Else
    MsgBox "Wrong Password"
    End If

    Exit_Command6_Click:
    Exit Sub

    Err_Command6_Click:
    MsgBox Err.Description
    Resume Exit_Command6_Click

    End Sub

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    stLinkCriteria = "[Rep#]=" & Trim(Me![Combo4].Column(0)) & " AND month([datefield]) = month(" & me!datefield & ")"
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Jan 2004
    Posts
    64

    Why doesn't it work?

    Thanks for your help. However, I am not able to get this to work.

    The text field in the form I'm trying to open filtered by the month is; RunMonth. And the filter is; RunMonth = 6/1/2004

    What am I doing wrong?

  4. #4
    Join Date
    Jan 2004
    Posts
    64

    It works with a little modification

    stLinkCriteria = "[Rep#]=" & Trim(Me![Combo4].Column(0)) & " AND ([runmonth]) = #6/1/2004#"


    Thanks very much for leading me in the right direction.

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    aha! I was thinking 6/1/2004 was a sample input.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    Jan 2004
    Posts
    64

    Thumbs up

    Thanks God for smart people like you:-)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •