ACCESS - Report Generation from Query

[CHR1S]

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 -

 

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)

[Pashers]

I think it might be: [cboProducts_2].value or [cboProducts_2]!value?! Edited January 9, 2013 by Pashers

[CHR1S]

Its still not propagating the data in the report.

 

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

 

Thanks

[Pashers]

If you like I can have a quick look through it if you upload it.

[CHR1S]

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));

[Pashers]

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.

[CHR1S]

Think I found my issue, will feed back shortly

[LosOjos]

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.

[CHR1S]

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!

[Pashers]

Well done :-)!