Form query subform

[Blondie96]

I know I have been at this too long... What seemed simple is now confusing me.

I have a form Which lists a type of room
cboroomtype (lg, med, tiny) This works

I want a subform (datasheet style) that will list all the room names of the type chosen in the cboRoomType
The files look like:
Room Type:
roomTypeKey (autonumber)
RoomTypeName (Text)

Room Name:
RmKey (autonumber)
RmTypeID (links to RoomTypeKey of Room Type table)
RoomName

I have obviously set the form/subform up wrong, because no matter what I select for the room type, the subform displays all the rooms in roomtype(1)

Any help will be appreciated.
Thanks,
Tamra

 

[PHV]

Say that cboRoomType is a ComboBox with the following properties:
RowSource: SELECT roomTypeKey,RoomTypeName FROM [Room Type]
2 columns (1st bound and hidden ie width 0
The RecordSource of your subform should be something like this:
SELECT RoomName FROM [Room Name] WHERE RmTypeID=[Forms]![your main form]![cboRoomType]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Blondie96]

My Main form is RoomType, it has a cboRoomGroup it's row source is "Select [Room Type].RoomTypeID, [Room Type].RoomType FROM [Room Type];
Column count =2 col widths 0";1" bound column 1 (this cbo works fine)

Subform is RoomName Subform, default view: datasheet, it has one field on it
name: Roomname(unbound) (no control source)
subform record source is SELECT RoomName.RoomName
FROM RoomName
WHERE (((RoomName.RoomType)=[Forms]![Room Type]));

Now I get no data in the subform at all

 

[PHV]

I'm confused as the field names in your last post are quite different than those in the OP...
What about changing this:
WHERE (((RoomName.RoomType)=[Forms]![Room Type]));
By this ?
WHERE RoomType=[Forms]![Room Type]![cboRoomGroup]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Blondie96]

it should have been cboRoomgroup... I changed it to:

SELECT RoomName.RoomName
FROM RoomName
WHERE (((RoomName.RoomType)=[Forms]![cboRoomGroup]));

but I still get nothing in the subform

 

[PHV]

Have you even read my last post ?

 

[Blondie96]

I was copying from the query builder, the recordsource has this:

SELECT RoomName.RoomName FROM RoomName WHERE RoomName.RoomType=Forms!cboRoomGroup;

(access keeps dropping the [] from forms & cbo...

 

[PHV]

Have you even tried this ?
WHERE RoomType=[Forms]![Room Type]![cboRoomGroup]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Blondie96]

Tried:

SELECT roomName.roomName FROM RoomName WHERE RoomType=Forms![Room Type]!cboRoomGroup;

it still dropped [] from forms & cbo...

still no data in subform

 

[Blondie96]

I even went to a stored sql that creates a table with this exact data, replaced the '=[Forms]![Room Type]![cboRoomGroup]' AND Tried it. It still did not work...

I am at a loss...

 

[Blondie96]

Does this by chance need anything in an onload() ?? or somewhere else?