Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    saylindara is offline Novice
    Windows Vista Access 2003
    Join Date
    Jul 2009
    Posts
    16

    Report parameters

    I posted this question on the microsoft office online and Al Campagna was very kindly helping me with it. I have been unable to post a reply for several days as I keep getting 'service temporarily unavailable' messages. Or I manage to post a reply but it tells me it didn't accept it. If Al looks at this forum I apologise.



    What I am trying to do is to filter a report via an unbound form (ParamForm) which has a combobox with the bound field EmployeeID and a command button with the following in the OnClick Event.

    Private Sub Command2_Click()
    Refresh
    If IsNull(cboFindName) Then
    DoCmd.OpenReport "r_EventsAttendedByEmployee", acViewPreview
    Else
    DoCmdOpenReport "r_EventsAttendedByEmployee",,EmployeeID= "& Me.cboFindName)
    End If
    End Sub

    This opens the report if the combobox is blank showing all employees perfectly but if there is a name in the combobox I get a perfectly formatted report but no details on it.

    What am I doing wrong?

  2. #2
    Join Date
    Jun 2009
    Location
    Vietnam
    Posts
    4
    Try code:
    DoCmdOpenReport "r_EventsAttendedByEmployee",,"[EmployeeID]=" & Me!cboFindName

    Click here for more information.

  3. #3
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    The WhereCondition argument is the 4th argument and you have it as the 3rd. Just add one more comma. There should also be a period "." after "DoCmd." at the beginning but that might just be a typo along with the trailing paren.
    DoCmdOpenReport "r_EventsAttendedByEmployee",,EmployeeID= "& Me.cboFindName)
    ...the whole line should read:
    DoCmd.OpenReport "r_EventsAttendedByEmployee",,,"EmployeeID = " & Me.cboFindName

  4. #4
    saylindara is offline Novice
    Windows Vista Access 2003
    Join Date
    Jul 2009
    Posts
    16
    Sorry Hien that didn't work but thank you for taking the trouble to reply.
    Thank you Rural Guy. Im looking forward to trying your solution today.

  5. #5
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Let us know how you make out.

  6. #6
    saylindara is offline Novice
    Windows Vista Access 2003
    Join Date
    Jul 2009
    Posts
    16
    Dear RG

    This is exactly what I have put in the OnClick event of the command button on the unbound form. The acViewPreview was missing from the details I last posted so I've added that. This time, when there is a name in the combobox (employeeID the bound field), the whole report opens the same as it does when the box is blank. I would really appreciate your help.

    Private Sub Command4_Click()
    Refresh
    If IsNull(cboFindName) Then
    DoCmd.OpenReport "r_EventsAttendedByEmployee", acViewPreview
    Else
    DoCmd.OpenReport "r_EventsAttendedByEmployee", acViewPreview, , , EmployeeID = "& Me.cboFindName"
    End If
    End Sub

  7. #7
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Too many arguments this time and what is with the trailing quote?
    DoCmd.OpenReport "r_EventsAttendedByEmployee", acViewPreview, , "EmployeeID = " & Me.cboFindName

  8. #8
    saylindara is offline Novice
    Windows Vista Access 2003
    Join Date
    Jul 2009
    Posts
    16

    Smile

    At last! RG you're a genious. I looked for your reply and tried it out before I'd even had my morning cup of tea and now I can have that and my breakfast a happy bunny. Thank you so much for your help.

  9. #9
    saylindara is offline Novice
    Windows Vista Access 2003
    Join Date
    Jul 2009
    Posts
    16
    One other thing - is it possible to add further filters, e.g. date?

  10. #10
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Quote Originally Posted by saylindara View Post
    One other thing - is it possible to add further filters, e.g. date?
    YES. Just AND them together.

  11. #11
    saylindara is offline Novice
    Windows Vista Access 2003
    Join Date
    Jul 2009
    Posts
    16
    Dear RG

    I feel I am taking up too much of your time but I hope you can help me further. I did think that once I saw how to filter by name it would point me to do the rest on my own but sadly not.

    I've tried putting And in where I thought Ands should go but I'm obviously not understanding what you mean as I can't get it to work.

    I have added two text boxes to the unbound form txtFromDate and txtToDate (in addition to cboFindName). Ideally I would like the user to be able to print the entire report if the txtFromDate is empty, select all events from a particular date to now (txtToDate =date() as default?) or dates within a range (txtFromDate and txtToDate). The corresponding field on the report is EventDate.

    I've turned to the Access books I have for help but it's obvious I will need to study VBA in depth before I can make sense of it.

  12. #12
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    DoCmd.OpenReport "r_EventsAttendedByEmployee", _
    acViewPreview, , _
    "EmployeeID = " & Me.cboFindName & " AND " & _
    "[YourDateField] >= #" & Me.txtFromDate & "# AND " & _
    "[YourDateField] <= #" & Me.txtToDate & "#"

  13. #13
    saylindara is offline Novice
    Windows Vista Access 2003
    Join Date
    Jul 2009
    Posts
    16
    Thank you for replying

    When I select dates it only picks up the year, e.g. if I select dates from 1/6/09 (English format) I'll get all the events from the beginning of the year. I've tried putting #/#/# in the code but it rejected that. Is there anything else I can do? Actually now I've written that I'm wondering if the English format is the problem. If so, is there anything I can do about it.

    If the date boxes are blank on the form I get a message:

    Syntax error in date query expression 'EmployeeID = 1AND EventDate >= ## And EventDate ,=##)'

    I suppose I can get round that by having default values in the date boxes but is there another way?

  14. #14
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Your date *must* be in US format to work correctly. As for empty dates, just test for that and use different WhereCondition clauses.

  15. #15
    saylindara is offline Novice
    Windows Vista Access 2003
    Join Date
    Jul 2009
    Posts
    16
    Ah yes, well I encountered this problem when setting up my date calculations on the queries and I did manage to solve the problems via Allen Browne's advice and help on the Microsoft forum. I think I am going to need further help with this coding but I have taken up too much of your time already so I'll post another question to the site after I've studied this a bit more. I am new to Access and there's a lot to think about.

    Thank you very much for your help, it's been much appreciated. The fact that users can now select a name and print out a report is already a great advantage.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Too few parameters. Expected 2.
    By PPCMIS2009 in forum Access
    Replies: 0
    Last Post: 01-28-2009, 01:02 PM
  2. Replies: 0
    Last Post: 02-15-2007, 03:07 PM
  3. Passing parameters to a report
    By elmousa68 in forum Access
    Replies: 0
    Last Post: 12-07-2006, 01:38 AM
  4. DISPLAY INPUT PARAMETERS ON THE REPORT
    By fadone in forum Forms
    Replies: 1
    Last Post: 12-09-2005, 10:26 PM
  5. Input parameters
    By GloriaLuz in forum Reports
    Replies: 0
    Last Post: 11-14-2005, 09:38 PM

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