Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2010
    Posts
    32

    Unanswered: Generating a report HELP!

    If anyone can help me get the Employee Report to run properly I would appreciate it. Basically I need to select a Start and End Date and which employee then it will generate the report for those selections. The Report has already been designed but I am unsure how to get it to pull the right data.

    I have a seperate report that will give all employees for the given date and that works great but I want to be able to run a similar report by employee so I can track them over whatever time period specified.

    If you can take a look at this please send me your E-mail address and I will E-mail you the .mdb

    Thank You

  2. #2
    Join Date
    Sep 2010
    Posts
    32
    Instead of E-Mail I am posting the code related to the Report. If it easier to get the mdb over E-mail just let me know

    Report Code:
    Code:
    Option Compare Database
    Option Explicit
    
    Private StartDate As Variant
    Private EndDate As Variant
    Private Name1 As Variant
    
    Private shadeNextRow As Boolean
    Const shadedColor = 15726583
    Const normalColor = 16777215
    
    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    
      On Error GoTo Detail_Format_Error
    
      If shadeNextRow = True Then
        Me.Section(acDetail).BackColor = shadedColor
      Else
        Me.Section(acDetail).BackColor = normalColor
      End If
      
      shadeNextRow = Not shadeNextRow
     
    Detail_Format_Exit:
      Exit Sub
    
    Detail_Format_Error:
      MsgBox "Error " & Err.Number & ": " & Err.Description
      Resume Detail_Format_Exit
    
    End Sub
    
    Private Sub Report_Open(Cancel As Integer)
    
    StartDate = InputDate("Select Start Date")
    EndDate = InputDate("Select End Date")
    Name1 = InputName1("Select Employee")
    
    If IsDate(StartDate) And IsDate(EndDate) Then
        Me.Filter = "Date Between #" & StartDate & "# And #" & EndDate & "# And #" & Name1 & "#"
        Me.FilterOn = True
    End If
    End Sub

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    a suggestion
    create a form which holds the parameters, or modify one which calls theses reports, store on that form whatever controls you need to use as parameters, that can be start / end dates, departments, what ever this form shoudl allways exist, allways be open, but users cannot change anythign directly on here,

    because you can never ever trust the input from a user create another form to capture the values for those parameters (consider using a date control to set the date values)
    push the values from the parameter specification form to the parameter form
    use forms!myparameters.mycontrolname = forms!myparametersteeing form.controlname

    design a query which picks up the values of those parameters
    specify that the satrtdate is, say forms!myparameters!startdate

    run the wuery

    the advantage of this approach is that the user sets the parameters once for each reporting session so they can generate as many reports as they like, but those reports will pick up a consistent parameter

    sounds complex, in practise it isn't
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Sep 2010
    Posts
    32
    I am the only user of this db and I print reports for my bosses. I created a Form with Start End and Employee and a button but I can't get it to work properly. I am really new to this stuff and I do not understand most of it.

  5. #5
    Join Date
    Mar 2009
    Location
    Gatineau, Quebec Canada
    Posts
    147
    Provided Answers: 1

    Variable Report Design

    Hi,

    Assuming you are calling the report from a form, you can set the parameters from the form in the query underlying the report. Following that, just call the report. For example, my report by university class has the following "Criteria" in the query underlying a report:

    [forms]![frmChoice]![cboclass].[value]

    "cboClass" is a combobox on a form

    This should work fine for multiple parameters (if not, check the underlying SQL statement).

    For error checking, you just have to ensure that values are selected prior to the report being called. Then, call the report

    DoCmd.OpenReport "rptByClass", acPreview

    Regards

    John S

Posting Permissions

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