Page 1 of 2 12 LastLast
Results 1 to 15 of 28
  1. #1
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How to RUN Report? (Access2003)

    I have an Access Form with buttons to run Report.
    Next to it I have a cmd box to choose USERID who to run report for.
    So when USERID is selected by the click of the button Report should ran and come up as a preview.
    However IF USERID is not entered Report should run for everyone.

    What is the cleanest way to go with it?
    Macro? Function?
    I forgot how to command Function to run Report.
    All I had written is:
    Public Function Run_Summary_Report()


    where USER_ID = IIf(Nz(Forms![Reports Switchboard]!cmb_ID_Name, 0) > 0, Forms![Reports Switchboard]!cmb_ID_Name, USER_ID)

    End Function

    Please, push me in a right direction.
    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: How to RUN Report? (Access2003)

    You need code for the On Click event of the command button:

    Private Sub cmdReport_Click()
    Dim strWhere As String

    On Error GoTo ErrHandler

    If Not IsNull(Me.cmb_ID_Name) Then
    strWhere = "USER_ID = " & Me.cmb_ID_Name
    End If
    DoCmd.OpenReport ReportName:="rptSomething", View:=acViewPreview, WhereCondition:=strWhere
    Exit Sub

    ErrHandler:
    If Err = 2501 Then
    ' Report canceled - ignore this
    Else
    MsgBox Err.Description, vbExclamation
    End If
    End Sub

    Substitute the appropriate names.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to RUN Report? (Access2003)

    Hans!
    Brilliant as always.
    One thing Me. didn't work so I had to substitute with Forms![Reports Switchboard]!cmb_ID_Name)
    I forgot what needs to be done for Me. to be excepted by Access? Just curious.

    Thanks so much in a 1000th time!

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: How to RUN Report? (Access2003)

    Me refers to the form (or report) running the code. If the command button is on the same form (Reports Switchboard) as the combo box, you should be able to use Me.cmb_ID_Name. If the command button is on another form, you must use the Forms!... syntax.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to RUN Report? (Access2003)

    Another problem.
    I have 2 columns displayed on my cmb_SelectUserID - ID and NAme.
    strWhere gets value of the Name even ID is first in select statement.
    I took out Name. Left ID.
    Ran Report, came up as for ALL even strWhere was = to ID.


    Thanks,
    I hope it is not too confusing.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: How to RUN Report? (Access2003)

    Make sure that the Bound Column property of the combo box is set to 1.

  7. #7
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to RUN Report? (Access2003)

    I did thanks.
    I had changed code but it is still printing for all:

    Private Sub cmdReport_Click()
    Dim strWhere As String

    On Error GoTo ErrHandler

    strWhere = IIf(Nz(Forms!Recruiting_Reports_Switchboard!cmb_Re cruiter_ID_Name, 0) > 0, Forms!Recruiting_Reports_Switchboard!cmb_Recruiter _ID_Name, strWhere)
    DoCmd.OpenReport ReportName:="Recruitment_Summary_Report", View:=acViewPreview, WhereCondition:=strWhere
    Exit Sub

    ErrHandler:
    If Err = 2501 Then
    ' Report canceled - ignore this
    Else
    MsgBox Err.Description, vbExclamation
    End If
    End Sub

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: How to RUN Report? (Access2003)

    Your strWhere doesn't do anything. It should be of the form "fieldname = value". Please see <post:=671,133>post 671,133</post:> higher up in this thread.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: How to RUN Report? (Access2003)

    You should replace USER_ID with the actual name of the userid field in the table.

  10. #10
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to RUN Report? (Access2003)

    Post deleted by MS_fan

  11. #11
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to RUN Report? (Access2003)

    I've got message that name of the combo box is not recognized by the system. I had copy/pasted from Access combo box properties Name Item in ALL.
    I had to log into Server during run. If I could avoid that, can I? knowing password and user id for the server?
    Thanks

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: How to RUN Report? (Access2003)

    By now, I don't understand what you're doing any more. Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  13. #13
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to RUN Report? (Access2003)

    Before I stripped the database I think I know where my confusion is from.
    Here:
    If Not IsNull(Me.cmb_ID_Name) Then
    strWhere = "USER_ID = " & Me.cmb_ID_Name

    To me cmb_ID_Name is the name of my combo box that is displaying USER_ID that Report has to be filtered by.
    So I see it as If value in combo box not Null then
    strWhere(variable) = to ... and here I get confused...about whole "USER_ID = " & Me.cmb_ID_Name
    What is this? I can't read it. What is USER_ID=Form! combo box...
    Sorry I am feeling brain dead.

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: How to RUN Report? (Access2003)

    The variable strWhere starts out as an empty string.

    If the user hasn't selected an item in the combo box, the value of the combo box is Null and then we leave strWhere unchanged - it's still an empty string.

    But if the user selected an item, we set the value of strWhere to the concatenation of "USER_ID = " and the value of the combo box. For example, if the selected item is 37, the value of strWhere will be

    "USER_ID = 37"

    This string is passed as WhereCondition to the report.

    Note: USER_ID should be the name of the field in the table on which you want to filter the report. I used USER_ID as an example, the name might be different in your table.

  15. #15
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to RUN Report? (Access2003)

    That 'thing' confused brain out of me.
    Why do we need "USER_ID = 37"
    and not just '37' wich will result an action:
    Select Table.USER_ID from Table where USER_ID=37?
    Not
    where USER_ID ='USER_ID=37'

    Well, now when I am almost there I am still getting ALL records.
    I had tried both where USER_ID=37
    and USER_ID ='USER_ID=37'
    and no change.

    When I inserted combo box how was I suppose to populate it?
    I went with Wizard and now I have Unbound combo box with Dta Source SELECT Active_Recruiters.RecruiterID FROM Active_Recruiters ORDER BY [RecruiterID];
    and Row Source Type table/query

    Does it sound legit?
    Thanks so much for hanging here with me.

Page 1 of 2 12 LastLast

Posting Permissions

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