Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Posts
    6

    Unanswered: Question: Updating the Filter on a Report

    Greetings,

    I'm still very much a new player in the Access/SQL ballgame and have been given an existing Access DB to fix some problems on. Here's one of the problems that I'm not sure how to fix though:

    I have a report that pulls in user information. The query behind it pulls the matching records for all users, and the report filters on 'UserID = #', where # has to be entered manually each time to change the output. This requires the person who runs the report to have a master list printed out that shows UserID = Name so he can enter the right ID in the filter. There has to be a better way than this.

    Is it possible to use the OnClick function on the report to bring up an unbound form with a drop down that shows all the actual names, have that unbound form get the UserID from the name, and have it update the report filter based on selection?

    I hope this makes sense... I'm still rather new at this.

    Thanks in advance!

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Easier method is to have a form, most likely either stand alone or launched by another form. Place a 2 column combo box containing customer id, and customer name (with column widths 0";1. Then in the filter, reference the form fields: forms!yourParameterForm!cust_id
    oh yeah... documentation... I have heard of that.

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

  3. #3
    Join Date
    Feb 2004
    Posts
    6
    Teddy, thanks for the suggestion, but I think I'm doing something wrong. I added an unbound form with a combo box that allows me to look through the names and select one, and it shows the appropriate UserID on the form, but when I try to run the report, it pops up with 'Enter Parameter Value' and doesn't filter any of the records at all.

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    The filter should read something like this:

    "UserID = " & forms!yourForm!yourComboBoxwithID

    I'm not sure why those hashes are in there. They probably shouldn't be, assuming your ID field is an integer.
    oh yeah... documentation... I have heard of that.

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

  5. #5
    Join Date
    Feb 2004
    Posts
    6
    I'm still getting Enter Parameter Value.

    I've set my report filter to: "UserID = " & forms!frmPickUser!User_ID

    and in my Unbound Form (frmPickUser), the Row Source on my 2 column combo box reads something like this:

    SELECT dbo_User.User_ID, [NameLast] & ", " & [NameFirst] AS FullName, dbo_User.NameLast FROM dbo_User ORDER BY [NameLast] & ", " & [NameFirst];

    My unbound form works as I'd like it to, but I think that is where the problem lies.

Posting Permissions

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