Sales Pipeline

[June7]

You can modify anything you want to in that database. Add fields to tables, include those fields in queries, add new controls to forms and reports that use those fields as ControlSource. You can add more filter and sort criteria to queries. You can build new objects (tables, queries, forms, reports).

[harish]

You can modify anything you want to in that database. Add fields to tables, include those fields in queries, add new controls to forms and reports that use those fields as ControlSource. You can add more filter and sort criteria to queries. You can build new objects (tables, queries, forms, reports).

HI june7,
I got it but I don’t know how to edit the query’s. Can you help me on how to edit the query’s with sample example.
IF it is possible to you please take my database as I mentioned link of BOX.COM. My rest of the work is completed. I struggled from last 15 days about this. You’re my last option. I hope you can do that one.

[June7]

Create a textbox named txtUser on form Open Opportunities List. Set ControlSource to: = [OpenArgs]

Modify code in LoginForm to:

Private Sub Login_Click()
Dim uname As String
Dim sWHERE As String
If Me.UserNameTextBox & "" = "" Then
    MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
    Me.UserNameTextBox.SetFocus
ElseIf Me.PasswordTextbox & "" = "" Then
    MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
    Me.PasswordTextbox.SetFocus
ElseIf Me.PasswordTextbox <> Nz(DLookup("[Password]", "tblMasterusers", "[UserName]='" & Me.UserNameTextBox & "'"), "") _
    Or Me.UserNameTextBox <> Nz(DLookup("[UserName]", "tblMasterusers", "[UserName]='" & Me.UserNameTextBox & "'"), "") Then
    MsgBox "Please Enter Correct username and password"
Else
    uname = Me.UserNameTextBox
    If DLookup("[Usertype]", "tblMasterusers", "[UserName]='" & Me.UserNameTextBox & "'") <> "admin" Then
        sWHERE = "[User Name] ='" & Me.UserNameTextBox & "'"
    End If
    DoCmd.Close
    DoCmd.OpenForm "Open Opportunities List", acNormal, , sWHERE, , , uname
End If
End Sub

[harish]

HI june7,
Thanks a lot for your quick reply.
You say like WHERE CONDITION argument of DoCmd.OpenReport to pass the UserName criteria to report. I don't know how to do that one please give me some example regarding this one.
One more question is what's the wrong with my login code.
If it is possible to you please do that one in my sales pipeline database and send to me.
Next Monday i will submit that one to my superior.

[June7]

The changes I suggest are simple. You can do them.

You can see the WHERE CONDITION argument in use in the code in my post. This argument is the same for .OpenForm and .OpenReport. Search the Help in the VBA editor for more info on using the OpenForm and OpenReport methods.

All you have to do with the the code I offered is to compare yours and mine line-by-line to see what I changed. Test your code. Login with ERP. What happens? Delete your code and copy/paste my code.

Refer to the link at bottom of my post for tutorial on debugging techniques.

[harish]

HI june7,
By using your code,when i entering wrong username and password into login form it shows an compilation error.
Best Regards,
Harish.Y

[harish]

HI june7,

I want Cary the username for all below reports...


All Open Opportunities

closed Opportunities

Customer Address Book

Customer Phone List

Employee AddressBook

Employee Phone List

Forecast By EMployee

Opportunities Over Due

Opportunities Details

[harish]

HI June7,

Can you give the step by step procedure. How to generate reports based on user login.

[June7]

Entering the wrong user name and password should not give compilation error, it should just open the Opportunities List because I did not get the DLookup correct and forgot the check on username. Should be:

ElseIf Me.PasswordTextbox <> Nz(DLookup("[Password]", "tblMasterusers", "[UserName]='" & Me.UserNameTextBox & "'"), "") _
Or Me.UserNameTextBox <> Nz(DLookup("[UserName]", "tblMasterusers", "[UserName]='" & Me.UserNameTextBox & "'"), "") Then

I edited the code in the prior post to reflect this change.

If you make the edits as I described then this should work. You need to learn debug techniques and figure out where you went wrong with editing.

Open report filtered by user the same way the Opportunities List form is open filtered. Use the If Then structure to set WHERE CONDITION of DoCmd.OpenReport.

You can put another textbox on Opportunities List form called txtType and set ControlSource to:
=DLookUp("UserType","tblMasterusers","UserName='" & [OpenArgs] & "'")

Now code to open a report can be like:
If Me.txtType <> "Admin" Then
strWHERE = "[User Name]='" & Me.txtUser & "'"
End If
DoCmd.OpentReport "All Open Opportunities", , , strWHERE

Right now the combobox for selecting a report uses an Embedded macro. I don't use macros, only VBA. You can convert the Embedded macro to VBA and then edit the VBA or learn how to code with macros.

[harish]

Hi june7,
Thanks a lot for each and every reply. Can you help me on my last question regarding this issue.
I think you didn't get my question. Here my problem is when i am selecting report name from ComboReports dropdownlist we need to display the particular login user report.Don't display the other records.

Best Regards,
Harish.Y

[June7]

The field UserName in table Opportunities has a space (User Name), in other tables it doesn't. I suggest you have it the same in all. Advise not to use spaces in any names.

Several ways to approach this.

If you want that same filter criteria for all the reports opened by the ComboReports, should be able to modify the Embedded Macro to set criteria in the WHERE argument. "[UserName]='" & [Forms]![Open Opportunities List]![UserName] & "'"

However, it doesn't seem to be working right and since I don't like nor use macros, not sure how to fix.

Alternative is to use VBA code.
DoCmd.OpenReport Me.ComboReports, , , "UserName='" & Me.UserName & "'"

I do not want to take on the effort to reprogram this database. You have to learn how to do it.

[harish]

Hi June7,

when i write above code what u say. After that i am selecting one report name from combo report it shows one pop up window it contains data like enter username. Here my problem automatically it takes user name instead of asking with pop up window.

Best Regards,
Harish.Y

[June7]

You want a popup window to ask for what? The code is supposed to take the UserName value from the form. Either from the record that has focus or the username textbox I suggested you create.