+ Post New Thread
Results 1 to 10 of 10
Office Software Thread, ACCESS - Report Generation from Query in Technical; Im generating a report on all assets in a room. I select the building from a combo box, this propagates ...
  1. #1

    CHR1S's Avatar
    Join Date
    Feb 2006
    Location
    Birmingham
    Posts
    4,505
    Thank Post
    1,585
    Thanked 486 Times in 304 Posts
    Rep Power
    217

    ACCESS - Report Generation from Query

    Im generating a report on all assets in a room.

    I select the building from a combo box, this propagates the next combo box with the rooms relevant to the building.
    I then pass the room selection to the query -

    Code:
    SELECT Assets.AssetID, Assets.AssetDescription, Assets.AssetCategory, Assets.StatusID, Assets.BuildingID, Assets.Building, Assets.Room, Assets.Make, Assets.Model, Assets.ModelNumber, Assets.SerialNumber, Assets.RoomID
    FROM Assets
    WHERE (((Assets.RoomID)=[Forms]![Audit Tools]![RoomAudit].[Form]![cboProducts_2]))
    ORDER BY Assets.RoomID;
    Now my issue is that "cboProducts_2" displays the descriptive name for the room and not the RoomID needed to generate the results. How can I still select the descriptive name from the combo box but pass the RoomID to the query?

    I'm positive I have done this before, but for the life of me cant remember (damn you xmas holiday)

  2. #2

    Join Date
    May 2008
    Location
    Cheshire
    Posts
    292
    Thank Post
    48
    Thanked 27 Times in 24 Posts
    Rep Power
    18
    I think it might be: [cboProducts_2].value or [cboProducts_2]!value?!
    Last edited by Pashers; 9th January 2013 at 03:24 PM.

  3. Thanks to Pashers from:

    CHR1S (9th January 2013)

  4. #3

    CHR1S's Avatar
    Join Date
    Feb 2006
    Location
    Birmingham
    Posts
    4,505
    Thank Post
    1,585
    Thanked 486 Times in 304 Posts
    Rep Power
    217
    Its still not propagating the data in the report.

    Ill re-do my query tomorrow with a fresh head on!

    Thanks

  5. #4

    Join Date
    May 2008
    Location
    Cheshire
    Posts
    292
    Thank Post
    48
    Thanked 27 Times in 24 Posts
    Rep Power
    18
    If you like I can have a quick look through it if you upload it.

  6. #5

    CHR1S's Avatar
    Join Date
    Feb 2006
    Location
    Birmingham
    Posts
    4,505
    Thank Post
    1,585
    Thanked 486 Times in 304 Posts
    Rep Power
    217
    My room selection CBO now lists the two columns - Room Name and Room ID - column 2 (Room ID) is bound.
    I have also added some validation to highlight where I'm going wrong.


    When I select the room and click generate report the query runs and then halts saying there is no data to generate the report.

    SELECT Assets.AssetDescription, Assets.AssetCategory, Assets.Building, Assets.Room, Assets.Make, Assets.Model, Assets.SerialNumber, Assets.BuildingID, Assets.ModelNumber, Assets.RoomID
    FROM Assets
    WHERE (((Assets.RoomID)=[Forms]![Audit Tools]![RoomAudit]![cboProducts_2].Value));

  7. #6

    Join Date
    May 2008
    Location
    Cheshire
    Posts
    292
    Thank Post
    48
    Thanked 27 Times in 24 Posts
    Rep Power
    18
    Quick trouble shooting: replace:
    [Forms]![Audit Tools]![RoomAudit]![cboProducts_2].Value
    with a 'hard value' i.e. type in a value that you expect would work. If this returns the expected results. Then when you press the report button do:
    MsgBox [Forms]![Audit Tools]![RoomAudit]![cboProducts_2].Value
    This show a messagebox displaying what the above code will result to.

  8. Thanks to Pashers from:

    CHR1S (14th January 2013)

  9. #7

    CHR1S's Avatar
    Join Date
    Feb 2006
    Location
    Birmingham
    Posts
    4,505
    Thank Post
    1,585
    Thanked 486 Times in 304 Posts
    Rep Power
    217
    Think I found my issue, will feed back shortly

  10. #8

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,498
    Thank Post
    1,456
    Thanked 1,201 Times in 814 Posts
    Rep Power
    712
    Did you solve it? I seem to remember it being something to do with the "Bound Column" property of the combo box you're using - try increasing/decreasing it by 1 if you still haven't solved it.

  11. #9

    CHR1S's Avatar
    Join Date
    Feb 2006
    Location
    Birmingham
    Posts
    4,505
    Thank Post
    1,585
    Thanked 486 Times in 304 Posts
    Rep Power
    217
    Ha, what a stupid issue!

    In my main asset entry form the [RoomID] value wasn't being auto populated when the drop-down CBO's were selected - this, a Key ID, was missing from all further queries!

    After_Update - RoomID=cboProducts_2.value
    D'Oh!

  12. #10

    Join Date
    May 2008
    Location
    Cheshire
    Posts
    292
    Thank Post
    48
    Thanked 27 Times in 24 Posts
    Rep Power
    18
    Well done :-)!

  13. Thanks to Pashers from:

    CHR1S (14th January 2013)

SHARE:
+ Post New Thread

Similar Threads

  1. why can't I access Moodle properly from home?
    By secretlife in forum Virtual Learning Platforms
    Replies: 10
    Last Post: 14th January 2008, 05:00 PM
  2. Replies: 0
    Last Post: 18th October 2007, 08:55 AM
  3. Governors Access school network from home WHY?
    By thegrassisgreener in forum General Chat
    Replies: 11
    Last Post: 11th October 2007, 07:38 PM
  4. Replies: 8
    Last Post: 28th June 2006, 09:06 AM
  5. Printing Access Reports... or not!
    By Frazer in forum Windows
    Replies: 0
    Last Post: 20th October 2005, 11:03 AM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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